14 Essential Microsoft Excel Functions for Data Analysis

Key Takeaways 

• You use the CONCATENATE function to seamlessly merge text, numbers, and dates into a single cell for better organization.
• Excel lets you use VLOOKUP, INDEX/MATCH, and AVERAGEIFS to pinpoint specific data, calculate averages based on criteria, and gain valuable insights from your datasets.
• You can clean and organize spreadsheets using TEXT and IFERROR to format your data for clarity.

1. CONCATENATE

=CONCATENATE is one of the most crucial functions for data analysis as it allows you to combine text, numbers, dates, etc., from multiple cells into one. To use this function, follow the syntax below:

=CONCATENATE(Cell 1, Cell 2)

Cell 1 and Cell 2 refer to the cells with values you want to merge. It is also handy for creating the tracking parameters for marketing campaigns, building API queries, adding text to a number format, and several other things.

2. LEN

=LEN is another handy function for data analysis that essentially outputs the number of characters in any given cell. The function is predominantly usable while creating title tags or descriptions with a character limit. The syntax is below:

=LEN(Cell)

Cell in the syntax above may have a value such as A1, B2, etc. The function can also be useful when discovering the differences between different unique identifiers, which are often lengthy and not in the correct order.

3. VLOOKUP

=VLOOKUP can match data from a table with an input value. The function offers two matching modes — Exact and Approximate — controlled by the lookup range. If you set the range to FALSE, it’ll look for an exact match, but if you set it to TRUE, it’ll look for an approximate match. Below is the syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The elements of this function are broken down as shown:

• Lookup_value: It is the element you need to search for.
• Table_array: Range of cells containing the data you want to search.
• col_index_num: Column number in the table_array containing the value you want to return.
• [range_lookup]: Optional argument specifying how the function finds the lookup_value. TRUE for approximate matches, FALSE for exact matches.

4. INDEX/MATCH

Like the VLOOKUP function, the INDEX and MATCH functions are handy for searching specific data based on an input value.

When used together, the INDEX and MATCH can overcome the VLOOKUP’s limitations in delivering the wrong results (if you are not careful). So, when you combine these two functions, they can pinpoint the data reference and search for a value in a single-dimension array. That returns the coordinates of the data as a number. Below is the syntax:

=INDEX(column of the data you want to return, MATCH(common data point you're trying to match, column of the other data source that has the common data point, 0))

In the example above, I wanted to look up the number of views in January. For that, I used the formula:

 =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3)

Here,

• A2:C13 is the column of data I want the formula to return.
• Jan is the value I want to match.
• A2:A13 is the column in which the formula will find Jan.
• 0 signifies that I want the formula to find an exact match for the value.

If you want to find an approximate match, you must substitute the 0 with 1 or -1. So, 1 will find the largest value less than or equal to the lookup value, and -1 will find the smallest value less than or equal to the lookup value. Note that if you don’t use 0, 1, or -1, the formula will use 1 by default.

5. MINIFS/MAXIFS

=MINIFS and =MAXIFS are similar to the =MIN and =MAX functions, except they allow you to take the minimum/maximum set of values and match them on particular criteria. So, essentially, the function looks for the minimum/maximum values and matches them with input criteria. Here is the syntax:

=MINIFS(min_range, criteria_range1, criteria1,…)

=MAXIFS(max_range, criteria_range1, criteria1,…)

In the example above, I wanted to find the minimum scores based on the student’s gender. For that, I used the formula:

=MINIFS (C2:C10, B2:B10, "M") 

I got the result 27. Here,

• C2:C10 is the column in which the formula will look for the scores.
• B2:B10 is a column in which the formula will look for the criteria (the gender).
• M is the criteria.

Similarly, I used the formula below for maximum scores and got the result 100.

=MAXIFS(C2:C10, B2:B10, "M")

6. AVERAGEIFS

The =AVERAGEIFS function allows you to find an average for a particular data set based on one or more criteria. While using this function, you should remember that each criterion and average range can be different.

However, in the =AVERAGEIF function, the criteria and sum range must have the same size range. Below is the syntax:

=AVERAGEIFS(average_range, criteria_range1, criteria1,…)

In the example above, I wanted to find the average score based on the student’s gender. For that, I used the formula below and got 56.8. Here,

• C2:C10 is the range in which the formula will look for the average.
• B2:B10 is the criteria range.
• M is the criteria.

=AVERAGEIFS(C2:C10, B2:B10, "M") 

7. COUNTIFS

If you want to count the instances where a data set meets specific criteria, you must use the =COUNTIFS function. This function allows you to add limitless criteria to your query, making it the easiest way to find the count based on the input criteria. Here is the syntax:

=COUNTIFS(criteria_range1, criteria1,…)

In this example, I wanted to find the number of male or female students who got passing marks (i.e., >=40). For that, I used the formula below:

=COUNTIFS(B2:B10, "M", C2:C10, ">=40")

Here,

• B2:B10 is the range in which the formula will look for the first criteria (gender).
• M is the first criterion.
• C2:C10 is the range in which the formula will look for the second criterion (marks).
• >=40 is the second criterion.

8. SUMPRODUCT

The =SUMPRODUCT function helps you multiply ranges or arrays together and then returns the sum of the products. It’s a versatile function and can be used to count and sum arrays like COUNTIFS or SUMIFS, but with added flexibility. You can also use other functions within SUMPRODUCT to extend its functionality further. Here is the syntax:

=SUMPRODUCT(array1, [array2], [array3],…)

In this example, I wanted to find the total of all the products sold. I used the formula:

=SUMPRODUCT(B2:B8, C2:C8) 

Here,

• B2:B8 is the first array (the quantity of products sold).
• C2:C8 is the second array (the price of each product). The formula then multiplies the quantity of each product sold by its price and adds all of it up to deliver the total sales.

9. TRIM

The =TRIM function is particularly useful when working with a data set with several spaces or unwanted characters. The function allows you to easily remove these spaces or characters from your data and get accurate results while using other functions. Here is the syntax:

=TRIM(text)

In this example, I wanted to remove all the extra spaces between the words Mouse and pad in A7. For that, I used the formula:

 =TRIM(A7)

10. FIND/SEARCH

Rounding things off are the FIND/SEARCH functions, which will help you isolate specific text within a data set. Both the functions are similar in what they do, except for one major difference — the =FIND function only returns case-sensitive matches. Meanwhile, the =SEARCH function has no such limitations. Here is the syntax for Find and Search, respectively:

=FIND(find_text, within_text, [start_num])

=SEARCH(find_text, within_text, [start_num])

In this example, I wanted to find the number of times Gui appeared within Guiding Tech, so I used the formula below, which delivered the result 1.

=FIND(A2, B2)

If I wanted to find the number of times gui appeared within Guiding Tech instead, I’d have to use the =SEARCH formula because it isn’t case-sensitive. These functions are particularly useful when looking for anomalies or unique identifiers.

11. TEXT

The =TEXT function transforms numbers into text. This makes it possible to display them in a specific format. You may use this function to ensure clarity and consistency. Here’s the syntax:

=TEXT(value, "format_text")

In the example above, I use the formula below to convert the value in G1 to words.

=TEXT(G1, "mmmm d, yyyy")

12. IFERROR

The =IFERROR function is an error handler that allows you to specify what you want the Excel program to show when there is an error. It helps avoid a lot of # error signs on your sheets. Here’s the syntax:

=IFERROR(value, value_if_error)

In the example above, we use the formula below to show a message when there is an error.

=IFERROR(A1/B1, "Division by zero error")

13. FILTER

The =FILTER function extracts specific data sets based on one or more criteria. You can dynamically filter results without making changes to the data. Hence, you can explore and analyze different subsets of your data. Here’s the syntax:

=FILTER(array, include, [if_empty])

We can use the example below to show column B values equal to Sales.

=FILTER(A1:C10, B1:B10 = "Sales")

14. SORT

The =SORT function organizes data in ascending or descending order. Here’s the syntax:

=SORT(array, sort_col1, sort_order1, [sort_col2, sort_order2], ...)

In the example below, we sort data in the A1:C10 range, 3 refers to the third column in the range, and we use -1 to sort in descending order.

=SORT(A1:C10, 3, -1)

You may also find some Excel shortcuts handy while using these functions.