How to Use the AVERAGE Function in Microsoft Excel

Finding the arithmetic mean (the average) of a dataset is one of the most common tasks performed in Microsoft Excel. Whether you are calculating the average daily temperature, the average test score for a class, or the average monthly expenditure for a business, doing it manually with a calculator defeats the purpose of using a spreadsheet.

The AVERAGE function handles this instantly. It automatically adds up all the numbers in a range and divides by the total count, ignoring blank cells and text along the way.

How to Use the AVERAGE Function

The basic syntax for the function is =AVERAGE(number1, [number2], ...). You can feed it individual cells, raw numbers, or large ranges of data.

Example: Averaging a Column of Data

Imagine you have a list of student test scores in column B, from cell B2 down to B25. You want to find the class average.

1. Click on the empty cell where you want the final average to appear (e.g., cell B27).
2. Type the formula: =AVERAGE(B2:B25)
3. Press Enter.

Excel will instantly calculate and display the average score.

Using AutoSum for a Quick Average

If your data is in a neat, unbroken column or row, you don’t even have to type the formula manually. You can use the ribbon.

1. Click the empty cell directly below a column of numbers (or directly to the right of a row of numbers).
2. Go to the Home tab on the ribbon.
3. Look for the Editing group on the far right.
4. Click the small down arrow next to the AutoSum button (the button with the Greek Sigma symbol Σ).
5. Select Average from the drop-down list.
6. Excel will automatically insert the formula and highlight the numbers it thinks you want to average. If the highlighted box is correct, simply press Enter.

How the AVERAGE Function Handles Blanks and Zeros

It is crucial to understand how Excel treats empty cells versus cells containing a zero, as it dramatically affects your final calculation.

• Blank Cells: The AVERAGE function completely ignores blank cells. If you have 10 students but only 9 took the test (leaving one cell blank), Excel divides the total by 9.
• Cells with Zero (0): The AVERAGE function includes zeros in its calculation. If that 10th student took the test but scored a 0, Excel divides the total by 10, which will significantly lower the final average.
• Text Strings: The function ignores any cells containing text (like “N/A” or “Absent”).

Averaging Multiple Non-Adjacent Ranges

You can calculate the average of numbers that are scattered across your spreadsheet by separating the ranges with a comma.

For example, if you want to find the average of Q1 sales (in range B2:B10) and Q3 sales (in range D2:D10) while ignoring Q2, you would write:

=AVERAGE(B2:B10, D2:D10)

Excel will add all the numbers in both ranges together and divide by the total count of numbers found in both blocks combined.