Finding the Average Value With Excel's AVERAGE Function

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

Man discussing financial analysis with a woman in the living room.

 Hero Images Inc. / Getty Images

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.

Note: These instructions apply to Excel 2019, 2016, 2013, 2010, Excel for Mac, and Excel for Office 365.

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:

=AVERAGE(Number1,Number2,...Number255)
  • 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. Click cell D4, which is where the formula results will display.

  2. Click the Home tab of the ribbon.

  3. Click the down arrow beside the AutoSum button on the ribbon to open the drop-down menu.

  4. Select Average in the list to enter the AVERAGE function into cell D4.

    

    Excel spreadsheet with Average function selected from drop-down menu.
  5. Highlight cells A4 to C4 to enter these references as arguments for the function and press the Enter key on the keyboard.

  6. 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

Screenshots of Excel showing Options on a Windows PC

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.

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.

  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.

  5. 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 (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.