Tips, Tricks & Other Helpful Hints: Inserting Subtotals in Excel

Subtotal Button
Subtotal Button

Excel’s subtotal feature allows you to produce a variety of subtotals for a set of data. These include:
• AVERAGE – arithmetic mean
• COUNT – counts the number of cells in a range that contain numbers
• COUNTA – counting the number of cells in a range that are not empty
• MAX – largest value in a set of values
• MIN – smallest value in a set of values
• PRODUCT – multiples numbers
• STDEV – standard deviation, compatible with Excel 2007 and earlier
• STDEVP – standard deviation for an entire population, compatible with Excel 2007 and earlier
• SUM – adds numbers
• VAR – estimates variance, compatible with Excel 2007 and earlier
• VARP – calculates variance for an entire population, compatible with Excel 2007 and earlier

NOTE: Excel does not allow subtotaling in a list formatted as a table. First, convert the table into a normal range of cells. Click a cell in the table and then click the “Table Tools Design” tab. Click the “Convert to Range” button, and then click “Yes.” Excel removes the filter buttons from the columns at the top of the list while still retaining the original table formatting.

1. Sort the list on the field for which you want subtotals inserted.

2. On the Data tab, click the “Subtotal” button.

3. In the “Subtotal” dialog box, select the field for which the subtotals are to be calculated in the “At Each Change In” drop-down list.

4. Choose the type of totals you want to insert in the “Use Function” drop-down list.

5. Select the check boxes for the field(s) you want to total in the “Add Subtotal To” list box.

6. Determine whether you want to “Replace current subtotals” (if there are any existing ones), have “Page break between groups,” or have “Summary below data.”

7. Click OK. Excel adds the subtotals to the worksheet. Depending on the amount of data, it may take Excel several seconds to complete the subtotals.

When you use the Subtotals command, Excel outlines the data at the same time that it adds the rows with the subtotals and grand total. This means that you can collapse the data list down to just subtotal rows or even just the grand total row simply by collapsing the outline down to the second or first level using the numbers on the left hand side of the screen.

Information originally taken from

These instructions, along with illustrations, can also be found in SharePoint > Software Users Group > Shared Documents > Excel > Inserting Subtotals in Excel.