How To Use the AVERAGE Function in Excel: A Step-by-Step Guide

Alan Murray, contributor to the CareerFoundry blog

The AVERAGE function in Excel is one of the most commonly used functions.

In this tutorial, you will learn how to use the AVERAGE function. We’ll also show you how to use it with multiple number ranges.

Here’s what we’ll cover:

  1. What is the AVERAGE function and what is it used for?
  2. How to use the AVERAGE function in Excel (step-by-step)
  3. Things to consider when using the AVERAGE function
  4. Using multiple number ranges

1. What is the AVERAGE function and why use it?

The AVERAGE function returns the average from a given range of numbers.

This is a useful statistical function and can be used for many reasons. For example, to return the average sales total, the average exam score of students, or maybe the average monthly expense on food.

The average returned is the arithmetic mean.

This is calculated by adding a range of numbers and then dividing by the count of those numbers. However, you do not need to know this, because the AVERAGE function does it for you.

The AVERAGE function can handle multiple number ranges, if required. This is its syntax;

=AVERAGE(number1, number2, …)

2. How to use the AVERAGE function

There are two main ways to use the AVERAGE function, and both are covered in this tutorial. Download this workbook to follow along.

We have the following list of exam scores by students and we need to return the average exam score into cell D3.

A simple Excel spreadsheet containing data for student names and test scores

1. Click in cell D3, as that is where we want the result returned.

2. Click Home, click the arrow next to the AutoSum button and then click Average.

An arrow pointing to the "autosum" dropdown menu in Excel, and another arrow pointing to the "average" item within the dropdown menu

3. The AVERAGE function attempts to find the range of numbers to use. In this example, it asks if we want to use B3:C3, which is incorrect.

This range is highlighted gray, as it is ready for us to select the correct range to use.

A simple Excel spreadsheet containing data for student names and test scores. In this example, the AVERAGE function has been entered and has guessed, incorrectly, which cells to use.

4. Select range B2:B8. As you do so, this replaces the initial range.

A simple Excel spreadsheet containing data for student names and test scores. In this example, the AVERAGE function has been entered and the relevant cells selected.

5. Press Enter.

The average result is returned. This result has four decimal places.

A simple Excel spreadsheet containing data for student names and test scores. The AVERAGE function has been used to calculate the average of all the test scores.

Let’s remove the decimals as they are not required in this example.

1. Select cell D3.

2. Click Home and then click the Decrease Decimal button four times (once for each decimal place).

There are two decimal buttons on the Ribbon. The first increases the number of decimals and the second one decreases them.

A simple spreadsheet showing the average of exam scores shown to four decimal places. The two decimal buttons in the Excel toolbar ribbon are highlighted with a red arrow.

The result is now shown to 0 decimal places.

A simple Excel spreadsheet containing data for student names and test scores. The AVERAGE function has been used to calculate the average of all the test scores

Instead of using the button on the Ribbon, you can write the formula into the cell. This is the method many regular Excel users would take.

1. Click in cell D3.

2. Type = and then average. As you type, a list of functions appears.

A simple Excel spreadsheet containing data for student names and test scores. The user has typed "equals" and then "av" into a cell, and a dropdown has appeared with various average functions.

3. You can double click the AVERAGE function in this list to complete it, or keep typing. If you type it, remember to type an open parenthesis “(“ after the function name.

A simple Excel spreadsheet containing data for student names and test scores. In this instance, the user has typed out the average function manually in the relevant cell

4. Select range B2:B8. Type a closing parenthesis “)” and press Enter.

A simple Excel spreadsheet containing data for student names and test scores. In this instance, the user has typed out the average function manually in the toolbar

3. Things to consider when using the AVERAGE function

There are a few things to consider when using the AVERAGE function:

  • The range of numbers can be provided as a range, defined name, or a table reference.
  • The AVERAGE function will ignore text and blank cells in the range.
  • Cells that contain 0 are included in the average result. To exclude them, the AVERAGEIF function can be used.

4. Using multiple number ranges

The AVERAGE function can handle multiple ranges of numbers, if required.

In this example, we have two lists of student exam scores. We want to return the average into cell G3, for all student exam scores from both regions.

A simple Excel spreadsheet containing data for student names and test scores. There are two separate lists of students and test scores

1. Click in cell G3.

2. Type “=AVERAGE(“ and select range B3:B9.

3. Type a comma “,”. This is used to separate the two number ranges (this is shown in the tooltip provided).

In an Excel worksheet, the average function has been typed into a cell with a comma to denote two separate ranges

4. Select range E3:E11. Type a closing parenthesis “)” and press Enter.

The average is returned for all student exam scores.

A simple Excel spreadsheet containing data for two sets of student names and test scores. The average function has been used to calculate the average for the test scores.

When calculating the average from multiple ranges, you can also hold the Ctrl key and select another range instead of typing the comma separator.

The AVERAGE function can handle up to 255 ranges. More than you will ever need!

If you’re getting to grips with Excel in your quest to become a data analyst, you can try a free data analytics short course here. Keen to learn more Excel functions? Check out the following:

What You Should Do Now

  1. Get a hands-on introduction to data analytics with a free, 5-day data analytics short course.

  2. Take part in one of our live online data analytics events with industry experts.

  3. Talk to a program advisor to discuss career change and find out if data analytics is right for you.

  4. Discover how to become a qualified data analyst in just 4-7 months—complete with a job guarantee.