My Top Excel Functions for Data Analysis (with examples!)

Published in

Learning Data

8 min readMay 14, 2024

Dog is learning Excel! Photo by Kyle Hanson on Unsplash

Are you an aspiring data analyst? If so, you should learn Excel, like the dog in the picture above!

As a data analyst, I use Excel for many tasks: adding numbers, calculating medians and averages, finding minimums and maximums, and more. The more Excel functions I learn, the more efficient my analysis becomes.

However, I keep returning to the same functions. If you want to get ahead of the curve in Excel analysis, read on and learn my most frequently used functions!

As a reminder, type the = sign before starting the functions below. “=” signals to Excel that you are about to start typing a function.

Also, after applying any function, check the output to ensure its accuracy!

My Top Excel Functions (with examples)

MAX()

MAX() calculates the maximum value in a range.

To calculate the maximum value in column A, I type:

=MAX(A2:A6)

“MAX” finds the maximum value.
“A2:A6” is the range of cells I want to examine.

This is useful for quickly finding the highest value in a large dataset.

MIN()

MIN() calculates the minimum value.

To calculate the minimum value in column A, I type:

=MIN(A2:A6)

“MIN” finds the minimum value.
“A2:A6” is the range of cells I want to examine.

SUMIF()

SUMIF() adds values based on a single or multiple criteria.

In the example below, I have a list of sales transactions with two columns: “product (column A)” and “sales amount (column B).” I want to calculate the total sales for each product: t-shirts, jeans, and blazers.

To calculate the total sales for t-shirts, I type:

=SUMIF(A2:A6, “T-Shirt”, B2:B6)

“SUMIF” adds values based on certain criteria.
“A2:A6” is the range that contains the criteria.
“T-Shirt” is the criteria I want to match. I’m only interested in summing sales for T-shirts.
“B2:B6” is the range with the values I want to add (the sales amounts.)

I use the SUMIF() formula to calculate the total sales for t-shirts.

Next, I want to use SUMIF() to add up the sales amounts for jeans. To do so, I include “Jeans” as the criteria:

=SUMIF(A2:A6, “Jeans”, B2:B6)

“SUMIF” adds values based on certain criteria.
“A2:A6” is the range that contains the criteria.
“Jeans” is the criteria I want to match. I’m only interested in summing sales for jeans.
“B2:B6” is the range with the values I want to add (the sales amounts.)

I use SUMIF() to calculate the sales amounts for jeans.

AVERAGE()

AVERAGE() calculates the average of a set of numbers.

In the example below, I calculated the average for the cell range A2 through A126:

=AVERAGE(A2:A126)

“AVERAGE” finds the average.
“A2:A126” is the location of the numbers.

MEDIAN()

MEDIAN() finds the median of a set of numbers.

In the example below, I calculated the median for the cell range A101 through A113:

=MEDIAN(A101:A113)

“MEDIAN” says I want to find the median.
“A101:A113” is the location of the numbers.

CONCAT() to combine names

CONCAT() combines text from multiple cells into one cell.

I use the CONCAT() function all the time, most often for combining first and last names in a “full name” column. This is especially useful if you’re analyzing messy survey data.

To combine the first and last name columns below, I type:

=CONCAT(A2, “ “, B2)

“CONCAT” combines text from different cells.
“A2” is the first text that shows (first name.)
I added quotation marks with a space so there is a space between the first and last names. If I didn’t do this, the full name would appear as “MarySmith”.
“B2” is the last text that shows (last name.)

Next, I drag the formula down the column and check the output for accuracy. I see that “Mary” and “Smith” combined correctly into “Mary Smith,” “John” and “Reed” combined to “John Reed,” and so on.

CONCAT() to create email addresses

You can use CONCAT to make email addresses if you know the email ID and hostname. For example, if you have a list of email IDs and know the hostname is “@texas.edu”.

Below, I use this formula to combine the email ID with the hostname to form the full email address:

=CONCAT(A2, “@texas.edu”)

“CONCAT” combines text from different cells.
“A2” is the first cell I want Excel to look at. In this case, it’s the email ID mnh.
“@texas.edu” adds this hostname to the END of the text in A2.

The final result is mnh@texas.edu, which is correct!

I used CONCAT to make email addresses end in @texas.edu

Drag down this formula to apply to the rest of the column. As always, double-check the output for accuracy.

COUNT()

COUNT() counts cells that contain numeric values (including dates and times, which are stored as numeric values.) It ignores cells that contain text, empty cells, logical values (TRUE/FALSE), and error values (e.g. #VALUE!, #DIV/0!, etc.)

In the example below, I count the number of cells in column B that contain numeric values (scores.)

I use this formula:

=COUNT(B2:B11)

“COUNT” counts cells with numeric values.
“B2:B11” specifies the range of cells to examine.

This counts the numeric values in column B and gives me the result 7, which is correct.

I use the COUNT() function to count cells that contain numbers in column B.

COUNTA()

COUNTA() counts ALL non-empty cells, regardless of their data type.

How does this compare with COUNT()?

COUNT() only counts cells that contain numeric values, whereas COUNTA() counts all non-empty cells, regardless of type.

If a cell contains the value “INCOMPLETE”, the COUNTA() function WOULD include this in its count, but the COUNT() function would NOT.

Below, I have the same list of students and scores as in the example for COUNT(). The COUNT() function gave me a result of 7 because it did NOT count the “INCOMPLETE” cells.

The COUNTA() function gives me a result of 10 because it DOES include the “INCOMPLETE” cells.

Let’s look at the COUNTA formula in more depth.

Below, I use the formula =COUNTA(B2:B11)

“COUNTA” says I want to count all non-empty cells, regardless of data type.
“B2:B11” specifies the cells I want to look at: the numbers in column B.

COUNTIF()

COUNTIF() counts cells based on a single or multiple criteria.

For example, I have a list of names and countries. I want to see how many participants attended my event from each country.

To do so, I make a separate table and list out the countries: United States, Spain, Belgium, and Australia.

I use COUNTIF() to count the number of participants from the United States:

=COUNTIF(B2:B9, “United States”)

“COUNTIF” counts cells based on certain criteria.
“B2:B9" is the range of cells I want to examine.
“United States” is the criteria I want to look for.

This gives me the result 3, which is correct. 3 participants came from the United States.

I use the formula =COUNTIF(B2:B9, “United States”) to count the number of participants from the US.

Then, I re-type the formula using the other criteria I want to look for.

For Spain, I type =COUNTIF(B2:B9, “Spain”)

For Belgium, I type =COUNTIF(B2:B9, “Belgium”)

For Australia, I type =COUNTIF(B2:B9, “Australia”)

IF()

IF() lets you use conditional logic. The IF function checks if a condition is met, and returns one value if true and another if false.

In the example below, I have a list of students’ scores in a class, and I want to assign a grade based on each student’s score. I use an IF formula to automate this process.

I use the IF() formula to calculate grades for students based on their scores.

Column A has student names and column B has their scores.

In column C, I assign grades based on the following criteria:

90+ = “A”
80–89: “B”
70–79: “C”
Below 70: “D”

To assign these grades, do the following:

Set up a column C where the grades will go.
In cell C2 (if C2 is where you want the grade to display for the first student), use this formula: =IF(B2 >= 90, “A”, IF(B2 >= 80, “B”, IF(B2 >= 70, “C”, “D”)))
Then, drag this formula down along your list of student scores to apply the grading logic to all students.
Make sure to double-check how the formula is applied and if the results are accurate.

Let’s break down this formula: =IF(B2 >= 90, “A”, IF(B2 >= 80, “B”, IF(B2 >= 70, “C”, “D”)))

IF(B2 >= 90, “A”,…) checks if the score in cell B2 is greater than or equal to 90. If true, it assigns the grade “A.”
IF(B2 > =80, “B”,…) If the score is not 90 or above, it checks if it’s greater than or equal to 80. If true, it assigns the grade “B”.
IF(B2 >= 70, “C”, …) If the score is not 80 or above, it checks if it’s greater than or equal to 70. If true, it assigns the grade“C”.
If none of the above conditions are met (meaning the score is less than 70), it assigns the grade “D”.

As always, check that the output is what you expect and that you are referencing the correct cells!

Final Thoughts

Most likely, if you’re a data analyst, you’ll use Excel in your day-to-day work. I often use the functions above, particularly AVERAGE(), MEDIAN(), SUM(), and CONCAT(). These functions can make your life so much easier, even if you aren’t a data analyst!

If you want to level up your Excel game, check out:

Maven Analytics Excel immersive
Maven Analytics Excel Specialist learning path
My Medium article, Top 5 Ways I Use ChatGPT to be an Excel Ninja.
My Medium article, Top 5 Excel Data Cleaning Skills I Use Everyday.

The contents of external submissions are not necessarily reflective of the opinions or work of Maven Analytics or any of its team members.

We believe in fostering lifelong learning and our intent is to provide a platform for the data community to share their work and seek feedback from the Maven Analytics data fam.

Submit your own writing here if you’d like to become a contributor.

Happy learning!

-Team Maven