Over the years, Excel has evolved to become one of the best data analysis tools. It is powered by 450+ Excel functions that can do a lot of heavy weight calculations.
While there are so many Excel functions, you would only need to master a handful of these to get most of your work done.
In this article, I will share my top 7 Excel functions that I have seen most of the data analysts use on a daily basis.
EXCEL VLOOKUP Function
VLOOKUP is a powerful function that allows you to scan a list and when a match is found, it returns the corresponding value from the specified column.
In layman terms, suppose you go to a restaurant and you’re scanning their menu. When you find an item you would like to order, you scan to the right to see the price for that item.
In Excel, VLOOKUP function can do this for you. It will scan the list vertically (V for vertical) from top to bottom, and when it finds match, it will return the value from the column number you specify.
Below is the syntax of VLOOKUP function:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Here is an example, where you can fetch the price for a dish using VLOOKUP:
In the above example, the formula looks for the value “coffee” in the list in A2:A8, and fetches the price.
COMBINATION OF INDEX/MATCH Functions
While VLOOKUP is great when doing lookups, it has certain limitations. It cannot look to the left of the lookup value.
The combination of INDEX and MATCH functions overcomes many of the limitations of VLOOKUP and in most cases is a better choice when doing lookups (although VLOOKUP is highly popular and is used a lot more than the INDEX function).
MATCH function looks for a value in a column and returns the position number when a match is found.
INDEX function takes these positions (for row and column) and returns the value in the specified row and column number.
In the above example, Match function gives us the position of the lookup value – coffee – and the Index function gives us it’s price from the second column.
EXCEL IFERROR Function
Errors if not handled can create havoc in data models and calculations. Analysts fear these as much as they fear spiders.
IFERROR function allows us to replace the error with an acceptable value. For example, if you’re getting an error because of a formula, you can simply replace it with a blank or 0 or any other text.
In the example below, if you try and look for a value which is not there is the list, it will return an error.
In this case, you can use the IFERROR function to show some meaningful value/text instead of the ugly error.
EXCEL SUMIF Function
As the name suggests, SUMIF function would add the values when a given criterion is met.
For example, suppose you have a list of grocery items and you want to know the total cost of fruits, then you can use fruit as the criterion to sum all the prices (as shown below):
There are other similar functions such as:
- SUMIFS: Adds the value when all the specified criteria are met.
- COUNTIF: Counts the number of instances when the specified criterion is met.
- COUNTIFS: Counts the number of instances when all the specified criteria are met.
TEXT Functions in Excel
Apart from data, analysts also have to deal with a lot of text data as well.
For example, imagine you get hundreds of data points such as (USD 100, USD 200, USD 300, and so on).
You can’t use USD 100 in a calculation as it isn’t a number. However, if you could somehow extract the numerical part, then you would be able to perform calculations on it.
It’s situation like these when Text functions come in handy.
There are several text functions in Excel that enable you to slice and dice the text data.
Here are some useful Text functions available in Excel:
- LEN function: this counts the number of characters in a text string
- TRIM: this remove leading, trailing, and double spaces in text strings.
- RIGHT: This allows you to extract the specified number of characters from the right of a text string.
- LEFT: This allows you to extract the specified number of characters from the left of a text string.
- MID: This allows you to extract the specified number of characters from the middle of a text string. You can specify the starting position and the number of characters you want to extract.
Below is an example of extracting the numerical part from the text string using the MID function.
While this is a very basic example, I am sure it gives you an idea on how you can combine and use these text function in more complex situations.
Do you have your own list of useful Excel functions that you can’t live without?
Do share it with me in the comments section.