Find Average Values with Excel's SUBTOTAL Function

Senior businesswoman entrepreneur working at laptop in studio office

Getty Images/Hero Images

Excel's Subtotal feature inserts the SUBTOTAL function into a database or a list of related data and extracts specific information. The function is not limited to finding the sum or total for selected rows of data. It also finds average values for each column or field of data in your database.

The instructions in this article apply to Excel 2019, 2016, 2013, 2010; Excel for Office 365, Excel Online, Excel for Mac, Excel for iPad, Excel for iPhone, and Excel for Android.

Enter Data in Excel

The first step to using the SUBTOTAL function in Excel is to enter your data into a worksheet. When doing so, keep the following points in mind:

  • Enter the data correctly. Errors caused by incorrect data entry are the source of many problems related to data management.
  • Leave no blank rows or columns when entering the data. This includes not leaving a blank row between the column headings and the first row of data.

To follow this tutorial, enter the data shown in the image below into cells A1 to D12.

A screenshot showing tutorial data used to find average values with the Subtotal feature in Excel

Sort the Excel Data

Before subtotals can be applied, your data must be grouped by the column of data you want to extract information. This grouping is done using Excel's Sort feature.​

This tutorial finds the average number of orders per sales region. Here's how to sort the data by the Region column heading.

  1. Drag select cells A2 to D12 to highlight them.

  2. Select Data.

  3. Select Sort to open the Sort dialog box.

  4. Place a check mark next to My data has headers.

  5. From the Sort by drop-down list, choose Region.

    A screenshot showing how to sort data in Excel
  6. Select OK.

The data in cells A3 to D12 is sorted alphabetically by the second column Region. The data for the three sales reps from the East region are listed first, followed by North, then South, and then West.

Find Region Averages

Once the data has been properly sorted, find the average sales for each region using the SUBTOTAL function. The syntax, and its arguments, for the function, are:

  • Function_num: The SUBTOTAL function performs a number of different functions with numerical data. This field denotes the function to be performed. For the AVERAGE function, use the number 1.
  • Ref1: This argument notes the range of numbers that are used in the SUBTOTAL function's calculations.

To use the AVERAGE feature of SUBTOTAL:

  1. Create a group of cells in which the regional sales data will display. For this tutorial, enter the data shown in cells F2 to F5.

    A screenshot showing how to set up a worksheet to add subtotal data in Excel
  2. To average East Sales data, select cell G2. This is where the results will appear.

  3. Select Formulas.

  4. Select Math & Trig > SUBTOTAL to open the Function Arguments dialog box. In Excel for Mac, the Formula Builder opens.

  5. Place the cursor in the Function_num text box and enter the number 1.

  6. Place the cursor in the Ref1 text box and highlight cells D3 to D5. These cells will be combined to calculate the average for East region sales.

    A screenshot showing how to subtotal data in Excel
  7. Select OK. Except in Excel for Mac, where you select Done. The average sales data appears in cell G2.

    A screenshot showing the results of the SUBTOTAL function in Excel
  8. Repeat the above steps for North Sales, South Sales, and West Sales. Select the appropriate Ref1 range for each scenario.