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.