10 Excel Data Analysis Functions
Excel has hundreds of functions and trying to match the proper formula with the right kind of data analysis can be overwhelming. The most valuable functions don't need to be difficult. You’ll wonder how you ever lived without ten easy functions that will increase your ability to interpret data. There are plenty of Excel formulas and functions depending on what kind of operation you want to perform on the dataset. We will look into the formulas and functions on mathematical operations, character-text functions, date, sumif-countif, and a few lookup functions.
When conducting data analysis, the formula =CONCATENATE is one of the simplest to understand but most potent. Text, numbers, dates, and other data from numerous cells can be combined into a single cell.
SYNTAX = CONCATENATE (text1, text2, [text3], …)
Concatenating the Values of Several Cells
The simple formula to CONCATENATE the values of two cells A2 and B2 are as follows:
The values will be combined without any delimiters.
To separate the values with space, use “ “.
=CONCATENATE(A3, “ “, B3)
Concatenating a Text String and Formula Calculated Value
You can also concatenate a text string and a formula-calculated value.
For example, you can use the following formula to return the current date:
=CONCATENATE("Today is ",TEXT(TODAY(), "dd-mmm-yy"))
You should follow some rules to ensure that the CONCATENATE function always delivers the correct result.
There should be at least one “text” argument in the CONCATENATE function for it to work.
If the CONCATENATE function's arguments are invalid, the formula returns a #VALUE! Error.
The result of the CONCATENATE function is always a text string, even when all the source values are numbers.
In data analysis, LEN is used to show the number of characters in each cell. It’s frequently utilized when working with text that has a character limit or when attempting to distinguish between product numbers.
SYNTAX = LEN (text)
The number of calendar days between two dates is calculated using this function = DAYS.
SYNTAX =DAYS (end_date, start_date)
The number of weekends is automatically excluded when using the function. It’s classified as a Date/Time Function in Excel. The net workday’s function is used in finance and accounting for determining employee benefits based on days worked, the number of working days available throughout a project, or the number of business days required to resolve a customer problem, among other things.
SYNTAX = NETWORKDAYS (start_date, end_date, [holidays])
One of the “must-know” formulas for a data analyst is =SUMIFS. =SUM is a familiar formula, but what if you need to sum data based on numerous criteria? It’s SUMIFS.
SYNTAX = SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], …)
AVERAGEIFS, like SUMIFS, lets you take an average based on one or more parameters.
SYNTAX = AVERAGEIFS (avg_rng, range1, criteria1, [range2], [criteria2], …)
The COUNTIFS function is yet another powerful Excel data analysis tool. It’s a lot like the SUMIFS function. The COUNTIFS function counts the number of values that satisfy a set of conditions. As a result, it doesn’t need a sum range like SUMIFS.
SYNTAX = COUNTIFS (range, criteria)
COUNTA determines whether a cell is empty or not. You’ll come across incomplete data sets daily as a data analyst. Without needing to restructure the data, COUNTA will allow you to examine any gaps in the dataset.
SYNTAX = COUNTA (value1, [value2], …)
The acronym VLOOKUP stands for the vertical lookup that is responsible for looking for a particular value in the leftmost column of a table. It then returns a value in the same row from a column you specify.
SYNTAX = VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])
Below are the arguments for the VLOOKUP function:
lookup_value - This is the value that you have to look for in the first column of a table.
table - This indicates the table from which the value is retrieved.
col_index - The column in the table from the value is to be retrieved.
range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.
We will use the below table to learn how the VLOOKUP function works.
Here, A11 cell has the lookup value, A2: E7 is the table array, 3 is the column index number with information about departments, and 0 is the range lookup.
If you hit enter, it will return “Marketing”, indicating that Stuart is from the marketing department.
“Horizontal” is represented by the letter H in HLOOKUP. It looks for a value in the top row of a table or an array of values, then returns a value from a row you specify in the table or array in the same column. When your comparison values are in a row across the top of a data table and you wish to look down a specific number of rows, use HLOOKUP. When your comparison values are in a column to the left of the data you wish to find, use VLOOKUP. It gives the value in the same column from a row you specify.
SYNTAX = HLOOKUP (lookup_value, table_array, row_index, [range_lookup])
Below are the arguments for the HLOOKUP function:
lookup_value - This indicates the value to lookup.
table - This is the table from which you have to retrieve data.
row_index - This is the row number from which to retrieve data.
range_lookup - [optional] This is a boolean to indicate an exact match or approximate match. The default value is TRUE, meaning an approximate match.
Given the below table, let’s see how you can find the city of Jenson using HLOOKUP.
Here, H23 has the lookup value, i.e., Jenson, G1:M5 is the table array, 4 is the row index number, 0 is for an approximate match.
Once you hit enter, it will return “New York”.
Excel is a really powerful spreadsheet application for data analysis and reporting. After reading this article, you would have learned the important Excel formulas and functions that will help you perform your tasks better and faster. Thank you for following up with me to the end. Please feel free to distribute it to your peers.