How to Calculate Average in Excel (With Formula Examples)

What is average? It is simply the arithmetic mean of a given set of numbers.

Averages are super easy to calculate – no doubt about that 🎉

But if your dataset gets long, it might be that easy to calculate averages manually. And who would want to spend hours on something worth a few clicks only?

Excel offers a multitude of ways to calculate averages in Excel. And we will look into them all.

So what are we waiting for? Let’s jump right into the guide below.

Also, download our free sample workbook here to tag along with the guide as you continue reading 📩

First of all other methods, Excel offers an in-built AVERAGE function to calculate averages.

Want to see how the AVERAGE function works in Excel? Let’s go 🚗

The image below shows the marks of a student in different subjects.

Marks of Nica

The total marks for all the subjects are 100. So how much did Nica score overall (on average)? To calculate that.

  1. Activate a cell and start writing the AVERAGE function as follows:


average function syntax

When you average cells in Excel, know that the AVERAGE function will ignore logical values and empty cells but include any cells containing zero values 0️⃣

Kasper Langmann, Microsoft Office Specialist
  1. Specify the data range that is to be averaged.


Cell reference argument

Pro Tip!

You can specify the data range as cell references like = AVERAGE (B2, B3, B4, B5, B6, B7).

Or you can write this argument as a reference to a whole cell range like = AVERAGE (B2:B7).

And if you don’t have the figures populated in cells, you can write the values inside the function yourself as = AVERAGE (10, 12).

  1. Press Enter, and you’re good to go.
Average function calculates the average

Nica scored 82 marks on average. That’s a good score by the way 🥇

Calculate the average with a formula

We’ve tried using the AVERAGE function of Excel to calculate averages. It’s now time for something more interesting.

Pro Tip!

Do you know what is meant by average? Average is the sum of a given set of numbers divided by the count of those numbers 👩‍🏫

For example, what is the average of 2,4, and 6?

Count the numbers. There are 3 of them.

Divide the sum by the count (12/3 = 4).

Can we write our formula to calculate the average in Excel? Certainly yes. Let’s again calculate the average marks for Nica with a formula.

  1. Add up all the marks by writing the following formula.

= (B2 + B3 + B4 + B5 + B6 + B7)

Adding up the subject marks
  1. Divide the formula above by the number of values.

We have 6 subject marks here, so we will divide the formula above by 6 6️⃣

= (B2 + B3 + B4 + B5 + B6 + B7) / 6

Dividing the formula with 6
  1. Press Enter to get the average marks as below.
Excel calculates the average marks

82 – same as above! This tells that the AVERAGE function does nothing different than what we did above 👆

Calculate the average with the SUM and COUNT formula

The ways for calculating averages in Excel have yet not ended. You can calculate averages by combining other functions of Excel.

For example, the functions SUM and COUNT. How? Let’s see here 👀

We are again calculating the average marks for Nica (yes, once again), but this time with a different method. So let’s begin.

  1. Write the SUM function as follows:

= SUM (B2:B7)

Summing up the frequently occurring number

The SUM function will sum up all the marks ➕

  1. Divide the above formula by the COUNT function as follows:

= SUM (B2:B7) / COUNT (B2:B7)

Dividing by the COUNT function

The COUNT function counts the number of values in the referred cell range.

The COUNT function counts all the cells in the defined cell range. As we have defined the cell range B2:B7, it will count the cells between B2 to B7.

Kasper Langmann, Microsoft Office Specialist
  1. Press Enter to get the following results.
The SUM and the COUNT function

And there it is! Excel computes the AVERAGE of the given numbers.

Easy, no 😍

That’s it – Now what?

And that’s how you can calculate averages in Excel. It is super simple – and now you know that too. There are multiple ways how you can calculate averages in Excel.

Using a formula, the SUM and COUNT formula, and obviously, through the AVERAGE function in Excel.

However, just like calculating averages, there’s so much more that you can do in Excel using other functions. Some of my go-to Excel functions include the VLOOKUP, SUMIF, and IF functions of Excel.

These functions would make your life a lot easier. I suggest you learn them right away  ✈

But How? My 30-minute free email course will help you learn them (and many more) Excel functions in no time.

Other resources

There’s not only one function dedicated to calculating averages in Excel – but much more. Like the AVERAGEA function, AVERAGEIF function, and AVERAGEIFS function. Learn them here.

You’d also be surprised to know that Excel doesn’t offer an in-built function to calculate weighted average. But you can still calculate it in Excel using other functions. How? Learn here.

Frequently asked questions

To calculate the average percentage in Excel:

  1. The source data must be in percentages.
  2. Use the AVERAGE function to calculate the average of the given percentages.

Apply the “Percentage” format to the cell containing the Average from the Home Tab > Number > Percentage.