Finding the Average Value With Excel's AVERAGE Function

Use the AVERAGE function to find the mean in a range of data

Excel has a few functions that calculate the central tendency in a range of data: AVERAGE, MEDIAN, and MODE. The most commonly derived measure of central tendency is simple average (mean), and it is calculated by adding a group of numbers together and then dividing by the count of those numbers.

Here's how to find the arithmetic mean using with the AVERAGE function.

These instructions apply to Excel for Microsoft 365, Excel 2019, 2016, 2013, 2010, and Excel for Mac.

AVERAGE Function Syntax and Arguments

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:

  • Number1 (required) is the data from which you want the function to find the average.
  • Number2 to Number 255 (optional) is the additional data you want to include in the average calculation. The maximum number of entries allowed is 255.
Screenshot of Excel showing AVERAGE example functions

Options for entering the function and its arguments include:

  • Typing the complete function into a worksheet cell
  • Inputting the function and arguments using the Function Dialog Box
  • Entering the function and arguments using Excel's Average Function shortcut

Excel AVERAGE Function Example

Excel has a shortcut to enter 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.

  1. Select cell D4, which is where the formula results will display.

    Cell D4 selected in Excel worksheet.
  2. Select the down arrow beside the AutoSum button in the Editing group of the Home tab to open the drop-down menu.

    The AutoSum menu
  3. Select Average in the list to enter the AVERAGE function into cell D4.

  4. Highlight cells A4 to C4 to enter these references as arguments for the function and press the Enter key on the keyboard.

    Cells A4:C4 selected in Excel.
  5. The number 10 should appear in cell D4; this is the average of the three numbers (4, 20, and 6).

Best Practices for Using the AVERAGE Function

  • You can add individual cells as arguments, rather than a continuous range, but you must separate cell reference with a comma.
  • Excel ignores blank cells, text entries, and cells containing Boolean values (TRUE or FALSE).
  • If you make changes to the data in the selected cells after entering the function, it will, by default, automatically recalculate to reflect the change.

How AutoAverage Select Ranges

  • The default range includes only cells containing numbers.
  • The AVERAGE function should be entered at the bottom of a column of data or the right end of a row of data since 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, you should always check that it's accurate before pressing the Enter key on the keyboard to complete the function.

Blank Cells vs. Zero 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.

Turn Off/On 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 off, such cells are left blank, but still included in average calculations.

  1. Select File > Options to open Excel Options.

  2. Select Advanced in the left-hand pane of the dialog box.

    Advanced settings in Excel
  3. 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.

    The "Show a zero" setting
  4. To display zero values in cells ensure that the Show a zero in cells that have zero value checkbox is selected.

Turn Off/On Zero Cells in Excel for Mac

  1. Select the Excel menu.

  2. Select Preferences in the list to open the Preferences dialog box.

  3. Select 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.