Finding the Average Value with Excel's AVERAGE Function

Use the AVERAGE function to find the arithmetic mean

Male accountant discussing average financial analysis with Asian female in the living room.
Getty Images

Mathematically, there are many ways of measuring central tendency or, as it is more commonly called, the average for a set of values. The most commonly calculated measure of central tendency is simple average and it is calculated by adding a group of numbers together and then dividing by the count of those numbers.

To make it easier to measure the simple average of a data set, Excel has a number of functions that will calculate the more commonly used average values. These different functions including AVERAGE, MEDIAN, and MODE. For this tutorial, we will be explaining how to specifically find the arithmetic mean with the AVERAGE function.

AVERAGE Function Syntax and Arguments

Screenshot of Excel showing AVERAGE example functions

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments. The syntax of the AVERAGE function is:

= AVERAGE ( Number1, Number2, ... Number255 )
  • Number1 (required): The data to be averaged by the function.
  • Number2 to Number 255 (optional): Additional data values to be included in the average — the maximum number of entries allowed is 255.

Options for entering the function and its arguments include:

  1. Typing the complete function into a worksheet cell.
  2. Entering the function and arguments using the Formula Builder.
  3. Entering the function and arguments using Excel's Average Function shortcut.

Excel AVERAGE Function Example

Screenshot of Excel showing the AutoSum option

Excel has a shortcut to entering the AVERAGE function, sometimes referred to as AutoAverage due to its association with the better known AutoSum feature – located on the Home tab of the ribbon.

The steps below cover how to enter the AVERAGE function, as shown in row four of the example image above, using the shortcut mentioned above.

Entering the AVERAGE Function

  1. Click on cell D4 — the location where the formula results will be displayed.
  2. Click on the Home tab of the ribbon.
  3. Click on the down arrow beside the AutoSum button on the ribbon to open the drop-down.
  4. Click on the word Average in the list to enter the AVERAGE function into cell D4.
  5. Click on the Functions icon on the toolbar above the to open the drop-down list of functions;
  6. Select Average from the list to place a blank copy of the function in cell D4.
  7. By default, the function selects the numbers in the cell D4, change this by highlighting cells A4 to C4 to enter these references as arguments for the function and press the Enter key on the keyboard.
  8. The number 10 should appear in cell D4; this is the average of the three numbers – 4, 20, and 6.
Screenshot of Excel showing the Formula Builder

Keep These Notes in Mind

  • Individual cells, rather than a continuous range can be added as arguments but each cell reference must be separated by a comma.
  • Text entries and cells containing Boolean values (TRUE or FALSE) and cells that are blank are ignored by the function in rows 6, 8 and 9.
  • After entering the function, if changes are made to the data in the selected cells, the function, by default, automatically recalculates to reflect the change.

How AutoAverage Select Ranges

  • The default range includes only cells containing numbers, the range of selected numbers is interrupted by a cell containing text or a blank cell.
  • The AVERAGE function is designed to be entered at the bottom of a column of data or at the right end of a row of data; it looks first for number data above and then to the left.
  • Since the AVERAGE function is, in effect, guessing at the range it selects for the Number argument, this selection should always be checked for correctness before pressing the Enter key on the keyboard to complete the function.

Blank Cells vs. Zero Cells

Screenshot of Excel showing how to enable/disable zero values

Blank Cells

When it comes to finding average values in Excel, there is a difference between blank or empty cells and those containing a zero value. Blank cells are ignored by the AVERAGE function, which can be very handy since it makes finding the average for non-contiguous cells of data very easy.

Zero Cells

By default, Excel displays a zero in cells with a zero value – such as the result of calculations, but if this option is turned off, such cells are left blank, but still included in average calculations.

Turn Off/On Zero Cells (Windows PC):

  1. Click on the File menu.
  2. Click Options in the list to open the Excel Options dialog box.
  3. Click on the Advanced category in the left-hand pane of the dialog box to see the available options.
  4. In the right-hand pane, in the Display options for this worksheet section, clear the checkbox for Show a zero in cells that have zero value checkbox.
  5. To display zero values in cells ensure that the Show a zero in cells that have zero value checkbox is selected.
Screenshots of Excel showing Options on a Windows PC

Turn Off/On Zero Cells (Mac):

  1. Click on the Excel menu.
  2. Click Preferences in the list to open the Preferences dialog box.
  3. Click on the View category option.
  4. In the top pane, in the Show in Workbook section, clear the checkbox for Zero values checkbox.
  5. To display zero values in cells ensure that the Zero values checkbox is selected.