Excel for beginners

Excel is limited to 1,048,576 rows by 16384 columns in a single worksheet.
A 32-bit Excel environment is subjected to 2GB of virtual address space shared by Excel, the workbook, and add-ins that run in the same process.
64-bit Excel -share an address space that might run up to 500-700MB.
Google sheet -Google spreadsheets are limited to 5M cells with a maximum of 256 columns per sheet.

The main difference between Excel and Google Spreadsheet is Excel is used as a desktop app while Google Spreadsheets is mainly used as a cloud application.

pros of excel

• More robust UI and more right-click options.
• Troubleshooting is available in Excel.
• Opening multiple local files is faster.

Cons of Excel.

• Take local disk space.
• Saving and opening files take longer.
• Like any other desktop it can crash and lose work.
• Files are always stored in local storage.

Google spreadsheets pros.

More collaboration and easier to share work.
less right click and much less likely to crash
Requires fewer desktop resources ie storage.
Calculation and query execution are faster since it is executed on cloud servers.

Unstructured data- exactly as it sounds, data with no structure.No columns,no headings it barely fits in a spreadsheet.

When cleaning data in Excel - (formatting and standardizing )- currency, (spaces use the Trim formulae) capital letters, dates wrong format,
looking for duplicates-
Standardizing-(uppercase, lowercase, proper)
**filter a column and edit the columns or data with errors.

1. for instance if there are letters or acronyms in a worksheet (ctrl+H) replace the letters with their full meaning.**

In Excel, the IF function is a powerful and commonly used logical function. It allows you to perform different actions based on whether a specified condition evaluates to true or false. The basic syntax of the IF function is as follows:

excel
Adavantages of excel -

1. spreadsheets can be really useful in the right context.

2. you can see the data cleanly laid out in a table.

3. its easy to determin what is and the format.

Disadvantages of excel.

1. Its hard to reproduce state.
2. theres no way to tell what steps have been taken on a dataset.
3. Because of the plethora of functions it's nearly impossible to know them all.
4. You can find yourself in analysis paralysis.
5. spend a lot of time and effort trying to figure one thing out.
6. it is very difficult to handle extremely large datasets.
7. Spreadsheets have less flexibility for complicated analysis and presentation.

Copy code
=IF(logical_test, value_if_true, value_if_false)
logical_test: This is the condition you want to check. If this condition is true, the function returns the value specified in value_if_true; otherwise, it returns the value specified in value_if_false.
value_if_true: This is the value that the function returns if the logical_test is true.
value_if_false: This is the value that the function returns if the logical_test is false.
pivot tables -

this is how to create pivot tables
Adding charts to Excel from a PivotTable can be a useful way to visualize and analyze your data. Here are the steps to add a chart based on a PivotTable in Excel:

Create a PivotTable:

1. Select the data range that you want to analyze.
2. Go to the "Insert" tab on the ribbon.
3. Click on "PivotTable" and choose the location where you want the PivotTable to be placed (e.g., a new worksheet).
4. In the PivotTable Fields pane, drag and drop the fields into the Rows and Values areas to arrange and summarize your data.
5. Create the PivotChart:

under view in excel u get- Workbookviews,show,zoom,window and marcos. under windows u get freeze panes - u can freeze panes,freeze top row ,freeze first column.
CTRL + F6 is used to switch windows.
edit a cell u press F2,

Click anywhere within the PivotTable

1. Go to the "Insert" tab on the ribbon.
2. Click on "PivotChart."
3. Select the chart type you want to use (e.g., Column, Bar, Line, etc.) and click "OK."
4. Customize the Chart:

Once the PivotChart is inserted, you can customize it further.
You can change the :

• chart title
• axis labels
• legend and other elements by clicking on them and modifying the text.
• To change the chart type, right-click on the chart and choose "Change Chart Type."

Update the PivotChart:

As you interact with the PivotTable (e.g., by changing filters or updating data), the PivotChart will automatically update to reflect those changes.
Remember that the specific steps might vary slightly depending on the version of Excel you're using, but the general process is similar. Creating a PivotChart linked to a Pivot Table is a powerful way to visualize and understand complex data sets in Excel.
The term pandas came from wesmickneey from joining two words panel and data. A data frame a feature available in the library and is defined as a two-dimensional,size mutable potentially heterogeneous tabular data structure with labeled axes (rows and column)
A data frame is a two-dimensional data structure that is data is aligned in a tabular fashion in rows and columns.

VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])
=VLOOKUP(C3,J10:K13,2)
=VLOOKUP(MATCH(C11,G9:G13,0),G9:H13
VLOOKUP(Enter the number you want to look for, Enter the range of the whole data u are looking for , enter the column number u are searching for, Then type FALSE to know the exact match.)

XLOOKUP

HLOOKUP

Linking two workbooks in excel.

Removing BLANKS IN EXCEL
Press ctrl + G to open a dialog box , select special and go to blanks and remove the cells.

Sorting the data in excel -
Grouping data with outlines.

=power(A2,2) -Power by 2 & =raise a power (Cell^2) used to do the same.
=TEXT(A2,"HH:MM:SS AM/PM")
=DATEDIF(CELL1,CELL2,"Y") OR =DATEDIF(A1,B1,"Y") to get years between dates.

IF you a have a column with both date and time u can use the INT formula or

1. Selecting the column containing your datetime values (e.g., column C).
2. Go to the Data tab on the Excel ribbon.
3. Click on Text to Columns.
4. Choose Delimited and click Next.
5. If your datetime values have a specific format (e.g., yyyy-mm-dd hh:mm:ss),
6. select Date and choose the appropriate format. Click Finish

or writing this on new column =TEXT(C1,"dd/mm/yyyy")

OR doing the same =TEXT(C2,"HH:MM:SS")

Or opening the VBA editor and typing this code in the VBA on the new column to separate =AI.EXTRACT($C2,$B2)

Function AI_EXTRACT(text As String, pattern As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = pattern
    regex.IgnoreCase = True
    regex.Global = False

    If regex.Test(text) Then
        AI_EXTRACT = regex.Execute(text)(0).Value
    Else
        AI_EXTRACT = ""
    End If
End Function

Home tab-Format as Table-

Filtering data and sorting data, sort & filter u choose filter.
you can filter date according to date equals.

Connecting data from web and cleaning it.
link textbox to excel.
Embed feature in excel.

shortcuts to open
CRTL+ 1 to open number formats ALT
ALT+F11 to open VBA.
CTRL+F5

Dont stretch columns like a Noob-
Right click on the sheets on the lower side and select view code, change general to worksheets on the drop down. Type this code

Cells.EntireColumn.Autofit