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 works by inserting the SUBTOTAL function into a database or a list of related data. Using the Subtotal feature makes finding and extracting specific information from a large table of data quick and easy.

Even though it is called the "Subtotal feature", you are not limited to finding the sum or total for selected rows of data. In addition to the total, you can also find average values for each column or field of data in your database.

of 03

Entering Data in Excel

Screenshot of Excel showing unsorted data

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:

  • It is important to enter 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 above into cells A1 to D12. Be sure to enter each data value correctly to follow along accurately.

of 03

Sorting the Excel Data

Screenshot of Excel showing how to sort 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 by using Excel's Sort feature.​

In this tutorial, we want to find the average number of orders per sales region so the data must be sorted by the Region column heading.

Sorting the Data by Sales Region

  1. Drag select cells A2 to D12 to highlight them.
  2. Click on the Data tab of the ribbon.
  3. Click on the Sort button located in the center of the data ribbon to open the Sort dialog box.
  4. Choose Sort by Region from the drop-down list under the Column heading in the dialog box.
  5. Make sure that My list has headers is checked off in the top right corner of the dialog box.
  6. Click OK.
  7. The data in cells A3 to D12 should be now sorted alphabetically by the second column Region. The data for the three sales reps from the East region should be listed first, followed by North, then South, and then West.
of 03

Finding Region Averages

Screenshot of Excel showing use of the SUBTOTAL function

Once your data has been properly sorted, you can proceed to find the average sales for each region using the SUBTOTAL function. The syntax, and its arguments, for the function, are noted below:

= SUBTOTAL (Function_num, Ref1)
  • Function_num: The SUBTOTAL function can perform a number of different functions with numerical data; this field denotes what function we wish to perform. For the use of the AVERAGE function, we will use the number 1.
  • Ref1: This argument notes the range of numbers that will be used in the SUBTOTAL function's calculations.

Using the AVERAGE feature of SUBTOTAL

  1. Begin by creating a group of cells in which the regional sales data will be displayed. You can see how we went about this task in cells B14 - B17 in the example image above.
  2. Let's begin with averaging East Sales data. Click on cell C14 to highlight it as the cell we wish to put our results within.
  3. Within the Formulas tab, select the Math & Trig option.
  4. From the drop-down menu, select the SUBTOTAL function — this will open the Formula Builder.
  5. Click on the Function_num line and enter the number 1.
  6. Click on the Ref1 line, then highlight cells D3 to D5 — these are the cells we will combine to calculate the average as they contain the totals for East region sales.
  7. Click Done and the average sales data will now show in cell C14.
  8. Repeat the above steps for North Sales, South Sales, and West Sales, selecting the appropriate Ref1 range for each scenario.