Excel Functions, Tips, and Shortcuts for Everyone
OVERVIEW
Learn how to use the VLOOKUP, INDEX & MATCH, CLEAN & TRIM, Text to Columns functions, and explore the Pivot Table including its numerous data reporting functions. Also, tips on error detection, cell references, and the use of wildcard were discussed. Finally, I shared some shortcuts for your optimal use of Microsoft Excel.
VLOOKUP
The VLOOKUP and HLOOKUP are both from the same family of lookup functions, these functions can be used in categorizing and extracting data from a table based on a particular value. The VLOOKUP and HLOOKUP have similar syntax, the only difference is V represents vertical and can be used to categorize the data only vertically, while H represents horizontal and can only be used to categorize the data horizontally. The VLOOKUP syntax and parameters are explained below:
=VLOOKUP( lookup_value, lookup_table, col_index, [range_lookup] )
lookup_value: The value that you want to search for.
lookup_table: The array/table of data that is to be searched for the lookup_value. Note: The lookup value should always be in the first column in the lookup_tabbe and sorted alphabetically or numerically for VLOOKUP to work correctly.
col_index: The column number in lookup_table from which the matching value must be returned. The first column is 1.
range_lookup (Optional): Enter FALSE for an Exact Match Lookup. Enter TRUE for a Range Lookup/approximate match. If this parameter is omitted, TRUE is the default.
The lookup function can be used for selecting the closest value to the lookup value which is called the Range Lookup or in selecting the exact match of the lookup value called Exact Match Lookup.
For example, we can categorize African football players based on their height (short, average, and tall) using the range lookup as shown below.
=VLOOKUP(C2,$K$6:$L$9,2,TRUE)
The value 2 (third argument) tells the VLOOKUP function to return the value in the same row from the second column(Remark) of the yellow table.
The exact lookup could be used to find the exact match of a lookup value, in this case, we would look up the name of the player with unique ID: 550.
=VLOOKUP(550,A1:F1236,2,FALSE)
The value 2 (third argument) tells the VLOOKUP function to return the value in the same row from the second column(Name) of the table.
INDEX & MATCH
The INDEX function returns the value of a cell in a table based on the column and row number while the MATCH function returns the row or column number of a cell in a table. when the INDEX and MATCH functions are combined they can look up a value on a table based on horizontal and vertical conditions. Simply put, they act like the VLOOKUP and HLOOKUP combined.
The basic INDEX syntax is as follows:
=INDEX(array, row_num, [col_num])
array: This is usually a range of cells or table.
row_num: The row number of the value. This becomes optional if the array contains one row.
col_num: The column number of the value. This becomes optional if the array contains one column.
For example, we can determine the name of the player in row 6.
=INDEX(A1:A22,6)
The MATCH syntax is as follows:
=MATCH(lookup_value,lookup_range,[match_type])
lookup_value: The value to be looked up in the lookup array.
lookup_range: The range of cells/table being searched.
Match type: 0 is an exact match, 1 is the less than match type, -1 is the greater than match type. This can be omitted, the default option is the less than match type.
An example of the match function is to locate the row position of “Wilfried Zaha” on the table, In this case, the lookup_value is “Wilfried Zaha”.
In summary, INDEX wants numeric positions to lookup the value on the table, MATCH helps find those positions.
The combined use of INDEX and MATCH can be used to find the nationality of a player.
=INDEX(A1:D22,MATCH(G5,A1:A22,0),MATCH(F6,A1:D1,0))
The first MATCH formula returns 6 to INDEX as the row number, the second MATCH formula returns 4 to INDEX as the column number. Hence, the INDEX returns “morocco” as the nationality of “Hakim Ziyech”.
CLEAN + TRIM
The Trim and clean functions are interesting cleaning functions in Excel, especially when combined.
The Clean function removes line breaks and other non-printable characters. The application of CLEAN() is shown below:
While the Trim function removes double/extra spaces between words but won’t remove single spaces. The application of TRIM() is shown below:
The TRIM and CLEAN function can be combined as shown below:
=TRIM(CLEAN(A2))
TEXT TO COLUMNS
This function is used to split text in one cell to several columns based on its delimiters, it is also resourceful in cleaning data as most of the datasets we tend to encounter come in messy states.
For example, we would use this function to separate footballer’s names into first name & last name.
Step 1: Highlight the column you want to split/parse
Step 2: Click the “Data” tab in the ribbon, then look in the “Data Tools” group and click “Text to Columns”. Then the “Convert Text to Columns Wizard” would pop up.
Step 3: For the first step of the wizard, choose “Delimited” >> Click [Next].
Step 4: A delimiter is a symbol or space which separates the data you wish to split. For example, if your cell reads “Adebayo, Rahman” you would select “Comma” as your delimiter. In this example, the delimiter is “Space”. Select the delimiter within your data.
Step 5: Check the box next to “Treat consecutive delimiters as one”. >> Click [Next].
Step 6: Under “Column data format,” choose “General.”
Step 7: Click the arrow/spreadsheet icon at the far right of the “Destination” text box. Select where you want to paste the split data, then press [Enter].
Step 8: Click [Finish]
PIVOT TABLE
I believe pivot tables are the core of analytics in Excel and one of the most resourceful tools for swift analytics on Excel, it’s in-depth analytics and flexibility is simply amazing. Also, my go-to reporting and summarizing tool, Would not be able to explain all of its functions here(Maybe another post) so, I’d simply explain how to create a pivot table, and you could explore its interesting functions.
“Pivot Tables are the core of analytics in Excel”
Step 1: Select anywhere on the table
Step 2: Create the Pivot Table
The PivotTable is located on the excel ribbon. From Insert >> PivotTable.
Next, we create our pivot table. In the Create Pivot Table window, you’ll notice that the entire range of the table is automatically selected.
There is also the flexibility to form the pivot table in the existing worksheet or a new worksheet. In this case, we formed it in a new worksheet:
Step 3: Explore the PivotTable fields
Analysis A: To explore the age distribution of African football players.
Drag and drop the “age” feature into the ROWS and VALUES section then edit the summarization to “count” through the “Value Field Setting” as shown below.
Analysis B: Group African players based on their current football clubs and filter by their preferred foot.
Drag the “club” and “Name” features into the Rows section
Drag the “preferred_foot” feature to the Filters section
Toggle between preferred foot as shown below.
TIPS
The table below shows the interpretation of the most popular errors in Microsoft excel. Another error not included is the “N/A” error, which is usually common when executing lookup functions and it means that the lookup is either incorrect or could not be found.
Toggling through cell reference
F4: Immediately after typing a cell reference in the formula bar, use F4 to change the type of cell reference. For example, relative reference B4, if you press F4 once you will get the absolute reference $B$4, press it again to get B$4 (row absolute, column relative), again to get $B4 (column absolute, row relative), and once more will get you back to the initial relative reference B4.
USING Wildcards
There are 3 popular wildcards in Excel but just 2 are popularly used. It is important to note that wild cards only work with text. The two wildcards are Asterisk (*) for zero or more characters and Question mark (?) for any single character. For example, “P?t” will match Pit, Pot, Put, and Pet (as well as incorrect words like Pnt) but will not match Pity. The asterisk (*) will match any number of letters. For example, we could use =COUNTIFS(Players_name, “B*”) and that will count players whose names start with “B” like Benjamin Tetteh and Bernard Kyere.
Shortcuts
Ctrl+T: Create Table
Tab: Accept a function that Excel has suggested at the prompt.
F2: Displays the formula in editing mode and shows the color-coded precedents for all arguments in a formula.
F4: Toggle between relative and absolute references.
F9: Recalculates formulas in all open workbooks.
Shift+F9: Recalculates formulas in the active worksheet only.
F5: Displays the Go To dialog box
Shift + CTRL + ! (Number) or + $ (Currency) or + % (Percentage): Change Cell format
Shift + F2: Insert/edit comment
CTRL + B (CMD + B): Change selection to Bold Text
CTRL+Z | CMD + Z: Undo
CTRL +Y | CMD+Y: Redo
CTRL + ; | CMD + ; — Today’s date
Ctrl+A (CMD+A): Select all
Ctrl+. : Move between the corners of a table
Shift + Space: Select entire row
CTRL + Space: Select entire column
CTRL + Tab | CMD + ~: Go to next workbook
CTRL + A | CMD + A: Select the entire worksheet (or contiguous dataset)
CTRL+N | CMD+N: To create a new workbook or new file
CTRL+O | CMD+O: To open the dialogue box and select an existing file (workbook)
CTRL+W | CMD+W: To close your current Workbook
CTRL+S | CMD+S: To quickly save your file
CTRL + ~ (CTRL + `): Show formulas in the worksheet
CTRL + PgUp (CMD + PgUp): Go to the previous sheet.
CTRL + PgDn (CMD + PgDn): Go to the next sheet.
CTRL + Arrow keys (CMD+Arrow keys): Go to first/last cell in a row, column.
CTRL + SHIFT + Arrow keys: Increase the selection to the last cell with data
CTRL+Home (CMD+Home): Select the first non-blank cell.
CTRL + End (CMD+End): Go to the last non-blank cell.
CTRL + SHIFT + L (CMD + SHIFT + F): Add or remove a filter
CTRL + X | CMD + X: Cut selection
CTRL + C | CMD + C: Copy selection
CTRL + V | CMD + V: Paste data (from previous cut/copy action)
CTRL + F2: Open Print Preview window
CTRL + F3 | CMD + F3: Name a cell or cell range
Ctrl+Shift+F3 | Cmd+Shift+F3: Create Names from Selection. If you want to create several named ranges at once you can use the Create Names from Selection tool. First, select the range, including headers, then activate the tool. You can choose where the names come from, usually the Top row or Left column, but you can also use the Bottom row or Right column.
Hope you learned something new, If you have any questions, please feel free to contact me on LinkedIn.
Thank You!
