How to use subtotal feature in Excel

A subtotal is the aggregation of a data set, typically showing the totals of a certain section in that data set. Excel gives you tools to calculate subtotals in different ways. In this article, we are going to show how to use the subtotal feature in Excel.

Download Workbook

Subtotal Feature

You can find a dedicated button for Excel's Subtotal Feature in the Outline section under the Data tab of the Ribbon along with Grouping options.

If you click the button without selecting a proper data, you will be warned with the following message:

Thus, start by selecting tabular data that you want to get subtotals. You can select either a single cell in the dataset or the complete range.

Click the Subtotal button to see the options you have.

1. At each change in: You can select the column to aggregate by. You can only select one column at a time.
2. Use function: You can determine the aggregation function between 11 operations supported by the SUBTOTAL function: sum, average, count, max, min and statistical functions.
3. Add subtotal to: You can select which column(s) will be aggregated.
4. There are three additional options:
   1. To determine whether to keep or remove existing subtotals.
   2. Add a page break that will be helpful if you will print your document.
   3. Ability to select the total position. Uncheck the option if you want them at the top of each group.
5. Finally, buttons for applying or removing the subtotals, or cancelling your work.

Clicking OK will insert subtotal calculations by inserting rows between groups along with outline controls.

Be sure the column you want to group remain adjacent. Otherwise, you will duplications in groupings:

Outline controls will help you to hide details when you need to focus on subtotal values.

If you check the formula in a subtotal value cell, you will see the SUBTOTAL function with the selected aggregation option. You can edit the formula manually to fit your needs. For example, you can replace 9 (SUM) with 109 (SUM while ignoring hidden values).