Excel is considered as basic data analysis tool. It gives the power to users to do any sort of analysis from a data set. Knowing appropriate excel function and its application make the job simple and quick. Many a times, people are doing the excel work manually which is time consuming and there is a chance of error, instead of applying a function.
There are around 500 functions in Microsoft excel. Among these larger number of functions, it is important to apply appropriate functions which make the job easy and simple. There are 20 frequently used functions which are useful for data analysis. In this article, we are going to explain those 20 most useful excel functions for data analysis.
Table of Contents
20 Most Useful Excel Functions for Data Analysis
VLOOKUP/ XLOOKUP
When to use VLOOKUP: When you would like to pick up a data from different table or work sheet based on a common data, these functions should be used. For an example, product wise sale is in table A and same product wise profit is in Table B. Here objective is to bring product wise profit in table based on product id or name. So, VLOOKUP should be used in table A to pick up data from table B.
Function Arguments VLOOKUP: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]
When to use XLOOKUP: XLOOKUP is advanced version of VLOOKUP which is available with MS office 365 for the time being. VLOOKUP only can be used when the column is on the right side from where data needs to be picked-up. Here XLOOKUP comes into the picture which can read data from left side or right side.
Function Arguments XLOOKUP: XLOOKUP(lookup_value,lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Difference between VLOOKUP & HLOOKUP:
- VLOOKUP can read data only from left to right where XLOOKUP can read data from left to right or right to left.
- For VLOOKUP, need to insert column index number but it is not required for XLOOKUP.
- In XLOOKUP, if error condition is in built and IFERROR function is not required. VLOOKUP does not have such option.
- In VLOOKUP, data can be read from top to bottom. In case of XLOOKUP, you can define whether data should be read from top to bottom or bottom to top.
IF + AND/ OR
When to use IF + AND/OR: IF function is a powerful function which enable you to apply logic and get an output based on the logic. The function works like, if the logic holds true, it will give some output as per requirement and if its false, it will give something else. In IF function, only one simple logic can be applied. To apply multiple logic IF function should be merged with AND function. On the other hand, to test the logics and give output as per requirement, IF should be merge with OR function.
Function Argument IF + AND: IF(AND(logical1, [logical2], …), [value_if_true], [value_if_flase])
Function Argument IF + OR: IF(OR(logical1, [logical2], …), [value_if_true], [value_if_flase])
SUMIF/ SUMIFS
When to use SUMIF/SUMIFS: It is a combination of SUM and IF function. When summation is required based on logic. Only one logic is permissible for SUMIF and in SUMIFS, multiple logics can be applied.
Function Argument SUMIF: SUMIF(range, criteri, [sum_range])
Function Argument SUMIFS: SUMIFS(sum_range, criteri_range1, criteria1, [criteria_rnge2, criteria2],…)
Difference between SUMIF and SUMIFS: As mentioned earlier that in SUMIF, one logic can be applied and in SUMIFS, multiple logic. If you investigate the arguments for both the functions, you’ll find the difference. In case of SUMIF, sum range needs to mention after applying the logic. But in case of SUMIFS, sum range needs to be mentioned at the beginning and then all the required logics need to be applied.
COUNTIF/ COUNTIFS
When to use COUNTIF/ COUNTIFS: COUNTIF or COUNTIFS are combination of count and IF function. Suppose you want to count the entries in a column with specific logic, these functions are useful. In COUNTIF, one logic can be applied where in COUNTIFS, multiple logic.
Function Argument COUNTIF: COUNTIF(range, criteria)
Function Argument COUNTIFS: COUNTIFs(criteria_rnge1, criteria1, [criteri_range2, criteria2]…)
AVERAGEIF/ AVERGAGEIFS
When to use AVERAGEIF/ AVERAGEIFS: It is the combination of average function, to get average and IF function, to apply logic. Let’s say, we would like to calculate average from a column of a data set and logic should be, if the numerical value is more than zero than only average should be calculated. If logic will be more than one, AVERAGEIFS function should be used.
Function Argument AVERAGEIF: AVERAGEIF(range, criteria, [average_range])
Function Argument AVERAGEIFS: AVERAGEIFS(average_range, criteria_range1, criteria1, …)
INDEX + MATCH
When to use INDEX + MATCH: MATCH and INDEX are two separate functions. MATCH function is used to identify the position of a value or number within an array. On the other hand, INDEX function is used to get value based on the row and column number or specific location. INDEX and MATCH function can be used as replacement of VLOOKUP function. But there are few advantages with INDEX + MATCH function. Those are like no restriction of array, can read data from right to left unlike VLOOKUP and make the column reference dynamic.
Function Argument MATCH: MATCH(lookup_value, lookup_array, [match_type])
Function Argument INDEX: INDEX(array, row_num, [column_num])
CONCATENATE
When to use CONCATENATE: Many a times, it is required to create a composite key by merging two ids in data analytics. Another example is to merge first name and last name which are in separate column. Here, CONCATENATE function comes into the picture. The value of two columns can be merged by using “&” also but in case of multiple columns, it is time consuming and there is chance of error.
Function Argument CONCATENATE: CONCATENATE(text1, [text2], …)
LEFT/ RIGHT/ MID
LEFT, RIGHT and MID functions are type of text functions which are being used to crop to extract the text.
When to use LEFT: If you want to extract the text from left side of a string, use LEFT function.
When to use RIGHT: If you want to extract the text from right side of a string, use RIGHT function.
When to use MID: If you want to extract the text from the middle of a string, use MID function.
Function Argument LEFT: LEFT(text, [num_chars])
Function Argument RIGHT: RIGHT(text, [num_chars])
Function Argument MID: MID(text, start_num, num_chars)
IFERROR
When to use IFERROR: Many a times, we are getting error as output of a function. Errors are like, #NAME?, #VALUE!, #DIV/0!, #NULL!, #REF!. It is not looking good if your excel file contain such error. To avoid or handle that, IFERROR function is being used. Here, instead of error you can insert blank space or required text or number to show instead of error.
Function Argument IFERROR: IFERROR(value, value_if_error)
UNIQUE
When to use UNIQUE: Whenever, you would like to extract unique values from a data range, use UNIQUE function. As an example, you have a column with repeated product names and the objective is to get unique product name, UNIQUE function needs to be used.
Function Argument IFERROR: IFERROR(value, value_if_error)
SORT
When to use SORT: SORT function is being used to sorting the data by ascending or descending order. It is not only sort and show one column but also it is picking up the entire table based on the selection. As shown below that entire table is being picked up and sorted by the function. As you can see from the function argument that sort index and by sort order and by column or row can be inserted as input.
Function Argument IFERROR: SORT(array, [sort_index], [sort_order], [by_col])
FILTER
When to use FILTER: Filter function is among the new functions in excel. It is used to filter the data based on the condition. Filter function is most useful to create a dashboard in excel. As you can see from the function argument that it needs a input as “include” where the filter logic has to be applied.
Function Argument FILTER: FILTER(array, include, [if_empty])
TEXT
When to use TEXT: TEXT function is a dynamic function to convert the number into a text. It is most useful when you have a date, and you would like to extract month name or day of the week. The function is simple to apply but only you need to know the input as format_text in the function.
Function Argument TEXT: TEXT(value, format_text)
MAXIFS/ MINIFS
When to use MAXIFS/ MINIFS: MAX and MIN function is to identify the maximum and minimum value in an array. To do that, only need to insert the function and select the array or numbers. MAXIFS or MINIFS function is used to get maximum or minimum value based on a logic. Let’s say, the objective is to find out the maximum value and to find that, it will consider only value more than 100. So, ‘>10’ logic needs to be applied here. For both the functions, multiple logics can be applied.
Function Argument MAXIFS: MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Function Argument MINIFS: MINIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
LARGE/ SMALL
When to use LARGE/ SMALL: Like MAX and MIN function, LARGE and SMALL function is to find out the largest and smallest value in a array. But there is more within it as compared to MAX and MIN. MAX and MIN only can show you the largest and smallest value. So, if you would like to extract 2nd or 3rd largest or smallest value, it is possible with LARGE and SMALL function. In both LARGE and SMALL function, first need to select the array. Then needs to define which value you would like to extract weather its is largest or 2nd largest, smallest or 2nd smallest and so on in the position of ‘k’ in argument.
Function Argument LARGE: LARGE(array,k)
Function Argument SMALL: SMALL(array,k)
RANK
When to use RANK: Let’s say, we have a list of products and sales for each of products. We would like to rank the products based on sales, so RANK function will be used to get rank for each product.
Function Argument RANK: RANK(number, ref, [order])
SUMPRODUCT
When to use SUMPRODUCT: The main objective to use SUMPRODUCT is to get sum value after multiplication. Multiple columns can be selected to apply the function to multiply and sum. Also, logic can be applied to perform the multiply and sum.
Function Argument SUMPRODUCT: SUMPRODUCT(array1, [array2], [array3], …)
SUBTOTAL
When to use SUBTOTAL: With SUBTOTAL function, you can perform 11 types of calculations. Let’s talk for an instance, you have applied SUM function after apply filter to a column. The summation will show for the entire column not for filtered data which are visible in the screen. Here, SUBTOTAL function is helpful, and calculation will be applicable on the filtered data only.
Function Argument SUBTOTAL: SUBTOTAL(function_num, ref1, [ref2], …)
Function_num input:
- AVERAGE
- COUNT
- COUNTA
- MAX
- MIN
- PRODUCT
- STDEV.S
- STDEV.P
- SUM
- VAR.S
- VAR.P
OFFSET
When to use OFFSET: Suppose you have a table and you would like to pick up few data points from a specific table, OFFSET function will be the function of choice in that case. The inputs required for OFFSET function are row, column, height, and width.
Function Argument OFFSET: OFFSET(reference, rows, cols, [height], [width])
INDIRECT
When to use INDIRECT: Use INDIRECT function when you would like to pick up a data based on the text reference. Moreover, when you change the text reference dynamically, use INDIRECT function. INDIRECT function is bit complex to understand in the first go. But it is more useful and you will be familiar with it by practice.
Function Argument INDIRECT: INDIRECT(ref_text, [a1])
Basic Excel Formulas
Basic functions are not ignorable and useful to perform basic tasks like sum, count, average etc. Basic excel functions are mentioned below.
- SUM- To do the summation
- COUNT- To count the numbers in an array
- AVERAGE- To get average from an array
- COUNTA- To count any type of entries in an array
- NOW- Now function will generate present date and time from the system
- TRIM- To trim unnecessary space from a string
- MIN & MAX- To get maximum and minimum value from an array
- COLUMN- To get column number
- ROW- To get row number
- LEN- To count number of characters in a specific string
Final Words
So, these are 20 most important advanced functions in excel for data analysis or playing with data. As a data analyst, I can say that if you know these 20 functions and it’s use, most of the work will be done in terms use of excel functions. If you are new to excel, first start with 10 basic functions and then move to 20 advanced functions. Another useful and dynamic tool in excel is pivot table for data analysis. Apart from these functions, also learn pivot table to make the analysis work simple and error free.
