Find Average Values with Excel's Subtotal Feature

Find Averages with Excel's Subtotal Feature
© Ted French

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. This step by step tutorial includes an example of how to find average values for a specific column of data in a database. The steps in this tutorial are:

  1. Enter the Tutorial Data
  2. Sorting the Data Sample
  3. Finding the Average Value
01
of 02

Enter the Subtotal Tutorial Data

Find Averages with Excel's Subtotal Feature
Find Averages with Excel's Subtotal Feature. © Ted French

Enter the Subtotal Tutorial Data

Note: For help with these instructions see the image above.

The first step to using the Subtotal feature in Excel is to enter the data into the 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.

For this tutorial:

Enter the data into cells A1 to D12 as seen in the image above. For those who do not feel like typing, the data, instructions for copying it into Excel, are available at this link.

02
of 02

Sorting the Data

Find Averages with Excel's Subtotal Feature
Find Averages with Excel's Subtotal Feature. © Ted French

Sorting the Data

Note: For help with these instructions see the image above. Click on image to enlarge it.

Before subtotals can be applied, your data must be grouped by the column of data you want to extract information from. This grouping is done 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. Be sure not to include the title in row one in your selection.
  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 data 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 last the West region.
Format
mla apa chicago
Your Citation
French, Ted. "Find Average Values with Excel's Subtotal Feature." ThoughtCo, Jan. 18, 2018, thoughtco.com/find-average-values-subtotal-feature-3123432. French, Ted. (2018, January 18). Find Average Values with Excel's Subtotal Feature. Retrieved from https://www.thoughtco.com/find-average-values-subtotal-feature-3123432 French, Ted. "Find Average Values with Excel's Subtotal Feature." ThoughtCo. https://www.thoughtco.com/find-average-values-subtotal-feature-3123432 (accessed January 19, 2018).