There’s nothing to worry about if you have no idea about these data analysis functions of Excel. Here we are to help you get familiar with those helpful and must-know functions of excel. Read this post till the end to know everything and perform data analysis in Excel like a Pro.
Here in this tutorial post, we will share the top 11 most useful excel functions that won’t only handle most of your tasks but will also make them much easier than ever. So, without doing any further ado, let’s just start exploring the 11 most useful and handy functions of excel for data analysis.
We define the most useful functions of Excel as those that make the tasks seem much more manageable. The good news is most people have a tool kit of functions which they find helpful in completing most of their tasks. Let’s jump and start exploring the 11 must-know excel functions for data analysts one by one.
#1 The “IF” Function
It is one of Excel's most useful functions, and one can’t overlook it when discussing or analyzing data in Excel. This function can help you automate the decision making tasks quickly in the spreadsheets.
Using the IF function, we can perform different calculations in the spreadsheet based on the logical test result (which we can refer to as a decision). The IF function works by asking you to perform the logical test, what action to be performed in case if the result of the test comes true and what alternative action to be performed if the output comes false.
=IF(logical test, value if true, value if false)
In the example given below, We have shown “Yes” in Column E for all the delivery dates in column C that are seven days later than the order date represented in Column B. If not, “No” is displayed.
This one is another function among the most useful ones that shows the sum of all the values meeting specified criteria. However, there’s one more similar function in Excel named “SUMIF,” which performs the same task, but rather than testing many, it tests only one condition. So, if you want, you can ignore the “SUMIF” function and use the superior function “SUMIFS”).
This function needs the range of value input from you, and then each range to test and the criteria declaration.
=SUMIFS(sum range, criteria range 1, criteria 1, ….)
The next fantastic function Excel has got for you is COUNTIFS. This function is similar to SUMIFS in some ways. There are several other similar functions, such as MAXIFS, AVERAGEIFS, MINIIFS etc., that we haven’t mentioned in this top 11 listing.
Counting the number of values meeting some defined criteria is the primary task this function performs. Therefore, this function doesn’t need any input like sung range, as required in the SUMIFS function.
=COUNTIFS(criteria range 1, criteria 1, ….)
In the example given below, we performed the task of counting the total number of sales from a particular region (E3) having value="<200.
Also, please note that when using COUNTIFS or SUMIFS, you need to give the criteria input in the form of text or cell as reference. The example shown above uses both techniques in a singular formula.
It’s one of the brilliant features of Excel, which I found very helpful in eliminating all the cell spaces, except single spaces between the words. You can use this function to perform the task of removing all the trailing spaces. When copying and pasting content from one other place, this unwanted space issue occurs. So, to get rid of such problems, simply use this function.
The COUNTIF Excel function was not operational in the example shown below because space was added accidentally at cell B6’s end.
These spaces can not be seen by the user's eyes, which means you can not determine its presence unless something goes wrong.
In this example, we have used the TRIM function separately in a column to clean the data from the region column.
If you want to combine multiple cell values into one, this function is for you. You will find this function very helpful when piecing multiple sets of data together; the data could be a group of names, IDs, serial numbers, addresses etc.
This function will prompt you to use different values available.
=CONCATENATE(text1, text2, text3)
In the example below, we used this function to combine the first and second names into a complete name. Also, please note that space is inserted between text1 and text2 arguments.
This function performs the opposite task as of CONCATENATE function. It extracts a particular set of characters from the beginning and end of the text. If you want to extract some parts of a URL, address, name or any other data, you will find this function very useful.
Both the right and left functions of Excel demand identical information. They ask you to define where the text is located and what number of characters you wish to extract from the text.
=LEFT(text, num chars)
=RIGHT(text, num, chars)
In the example available below, we have given column A, which contains client ID data, a transaction ID, and the respective region code. To extract the client ID, we have used this LEFT excel function.
Similarly, to extract the Column A cell’s last character, we used the RIGHT excel function.
It’s the most commonly used Excel function for data analysis. It searches for the commanded value in a particular table and gives the result or output from the other column that is related to the commanded value.
If you wish to combine data from multiple lists into one, search for missing data, or compare the data, you will find this function brilliant. When using this function, you need to provide the following information:
The value you want to search for?
The table that you want to target for this search?
Define the column having the information you are searching for?
Type of lookup you want to perform?
=VLOOKUP(lookup value, table array, column index number, range lookup)
Most of the time, we copy and paste data into excel for analysis from another platform or system. Not always, but sometimes this leads to wrong data formation in excel sheets. As you should be knowing, you can’t perform some excel functions, such as SUM, if excel fails to recognize the pasted data as a number. So, the “Value” function comes into action for the rescue.
The Value function is capable of converting text format numbers to actual number representation/format (SIX to 6). You can use the following:
In the example screenshot attached below, we converted text sales value from Column C to number.
If you are using the Microsoft 365 version, you can use this “Unique” function. It’s not available for previous versions. This function requires the following input from you:
Check unique values by row or column.?
Range to return the unique list from?
Do you need a unique list output or a separate list?
=UNIQUE(array, by col, exactly once) OR =UNIQUE (array, by row, exactly once)
Range to sort?
Column to sort range by?
Order to sort the range (descend. or ascend.)?
Sort row or sort column?
=SORT(array, sort index, sort order, by col)
This one is one of the fantastic functions that Excel has. You can also use this function along with the “Unique” function to sort everything more appropriately. For this, you just need to provide the range to sort.
You need to give the following three inputs:
Range to filter?
Criteria for returning the result?
Actions based on no result?
=FILTER(array, include, if empty)
Getting started is easy
Setup your first data flow within minutes.
No credit card required.