In our latest 3 tip Friday newsletter read about Excel's Flash Fill feature, shortcut which adds borders to the selected area and INDEX MATCH function, one of Excel's most powerful features.
Excel’s Flash Fill feature
Excel Flash Fill feature will autofill a column based on previous entries. Excel will detect the pattern from the previous entries and autofill the rest of the cells in a column.
In the example below, enter the initials in the first cell of column C:
Then go to Data on the toolbar and click on Flash Fill in the Data Tools section.
You will see the rest of the range in the column C will be filled with the initials from column A and B.
Shortcut to add borders to a selected range
Adding borders to a range can be done using a quick shortcut:
Select the range you want to add the borders to
The following window will popup:
Click A to add borders to all the cells in the range or type one of the other letters to choose a different border type.
Index Match function
The Index and Match functions in Excel are very often used together to retrieve a specific value from a range. The Index function itself can retrieve a value from a range but you will need to know the exact position of the value you want to retrieve. There are three arguments to the Index formula:
array: this is the range where we will be looking for our value
row_num: this specifies the row where our value is (optional if the range selected is just one row)
column_num: this specifies the column where our value is (optional if the range selected is only one column)
In the example below if we want to know what country is ranked 4th, we would enter the following formula in cell E2:
The row and column numbers are relative to our range: although the 4th position is in row 5, within range A2:C11, it’s in the 4th row from the top of the range.
The column_num is 2 since we are retrieving the country name, which is in column two in our range.
Now let’s try and get the turnover for Germany. Our example is pretty small and we can quickly see that Germany is in position 8 in our range, but for a larger dataset, this is almost impossible. To achieve this first we will find in which row in our range is Germany. We can do this by using the Match function.
This function will return the position of a value within a range and it has the following elements:
lookup_value- this is the value for which the position we want to retrieve (in our example, this is “Germany”
lookup_array- this is the array where we are looking for this value (in our example this is B2:B11)
match_type- this is an optional argument, you will mostly use 0 as an argument as this will give you the position of the value that is exactly the same as the lookup_value
Enter the following formula in cell E5, this will return the value 8, which is the position of Germany:
Now we will join these two functions to retrieve the turnover of Germany by replacing the row_num in the Index formula with our match function as follows: