Find Average Values with Excel's Subtotal Feature

By Ted French, About.com Guide

Finding the Average Value Using Subtotals

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

In this step we will use the Subtotal feature to find the average number of orders per sales region. To find the average value, the Subtotal feature uses the AVERAGE function.

For this tutorial:

1. Drag select the data in cells A2 to D12 to highlight them.
   
   
2. Click on the Data tab of the ribbon.
   
   
3. Click on the Subtotal button located on the right side of the data ribbon to open the Subtotal dialog box.
   
   
4. For the first option in the dialog box At each change in: select Region from the drop down list.
   
   
5. For the second option in the dialog box Use function: select Average from the drop down list.
   
   
6. For the third option in the dialog box Add subtotals to: check off # Orders only from the list of options presented in the window.
   
   
7. For the three check boxes at the bottom of the dialog box, check off:
   
   Replace current subtotals
   Summary below data
   
   
8. Click OK.
   
   
9. The data table should now include rows that show the average number of orders for each sales region (rows 6, 9, 12, and 16) as well as the Grand Average (average number of orders for all regions - row 17). It should match the image on page 1 of this tutorial.

Return to Index page

Previous

• Excel SUBTOTAL Feature Overview
• Enter the Subtotal Tutorial Data
• Sorting the Data
• Finding the Average Value Using Subtotals

Next