Excel Subtotals

Use Excel's built-in Subtotal command to automatically add subtotals and grand totals to a list of data.

Subtotal Video

You can watch the steps for creating subtotals, and preventing duplicate grand totals, in the Subtotal video, show below. The written instructions are below the video.

Sort the Data

Before applying subtotals, the data must be sorted by the columns on which you want to base the subtotals. In this example, Category and Product will be subtotaled, so the data is sorted by those two columns.

Apply the First Subtotal

After the data is sorted, follow these steps to apply the first subtotal. In this example, the Category column will be subtotalled first.

1. Select a cell in the list, and on the Excel Ribbon, click the Data tab, then click Subtotals.
2. In the "At each change in" box, select the first column that you want to base the subtotals on -- Category in this example.
3. Select the function that you want to use when totaling the columns.
4. Select all the columns in which you want a subtotal.
5. Remove the check mark from "Replace current subtotals" (unless there are existing subtotals that you want to remove).
6. Check or uncheck the page break and summary below data options, based on your preferences.
7. Click OK, to apply the Subtotals.

The data will show a subtotal after each change in the Category column, and there will be a Grand Total at the bottom of the data.

At the top left, grouping buttons are added, so you can view specific parts of the data:

1 - Grand Total only

2 - Grand Total and Subtotals

3 - All data and totals

You can also click the + and - buttons in the grouping bar, to show or hide sections of the data.

Apply the Second Subtotal

Next, repeat the previous steps to apply the second subtotal. In this example, the Product column will be subtotalled second.

Be sure to remove the check mark from "Replace current subtotals", so the Category subtotals are not removed.

After the second subtotals are applied, the data will show a subtotal after each change in the Category column, and each change in the Product column, and there will be a single Grand Total at the bottom of the data.

Another grouping button is added at the top left of the worksheet.

Remove Subtotals

If you no longer need the subtotals, follow these steps to remove them.

1. Select a cell in the list, and on the Excel Ribbon, click the Data tab, then click Subtotals.
2. Click the Remove All button, to remove the Subtotals.  

Duplicate Grand Totals

With some data, a second Grand Total might appear, if you add a second layer of subtotals. This occurs if there are errors in the columns that are being totaled.

In the screen shot below, there are two Grand Total rows, because there is an error in the Total Price column

Prevent Duplicate Grand Totals

To prevent duplicate grand totals, use the IFERROR function, or IF and ISERROR functions, to handle the errors. For example:

=IFERROR(E2*D2,"")

If you can't alter the formulas to prevent errors, you can hide the duplicate Grand Total rows, after creating the subtotals.

Get the Sample File

Click here to get the sample file for Excel Subtotals. The zipped file is in xlsx format, and does not contain macros.

More Tutorials

Pivot Tables

SUBTOTAL Filtered List

SUBTOTAL Function

Functions List

30 Functions in 30 Days