How to use outline subtotal in Excel

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,"")

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

Last updated: July 11, 2021 7:35 PM

Outlining data makes your data easier to view. In this example we will total rows of related data and collapse a group of columns.

1. First, sort the data on the Company column.

2. On the Data tab, in the Outline group, click Subtotal.

3. Select the Company column, the column we use to outline our worksheet.

4. Use the Count function.

5. Check the Company check box.

6. Click OK.

Result:

7. To collapse a group of cells, click a minus sign. You can use the numbers to collapse or expand groups by level. For example, click the 2 to only show the subtotals.

Note: click the 1 to only show the Grand Count, click the 3 to show everything.

To collapse a group of columns, execute the following steps.

8. For example, select column A and B.

9. On the Data tab, in the Outline group, click Group.

10. Click the minus sign above column C (it will change to a plus sign).

Result:

11. To remove the outline, click any cell inside the data set and on the Data tab, in the Outline group, click Subtotal, Remove all.

Many spreadsheets are created in a hierarchical style. For example, a worksheet might contain a column for a person or company, followed by a column with sales data. By outlining your worksheets, you make them easier to understand and read. Instead of sifting through irrelevant information, you can collapse an outline to display each group’s bottom line. There are several ways to outline a workbook:

• Using the Auto Outline Feature: The Auto Outline command automatically outlines a selected range of cells or the entire worksheet, based on formulas and the direction of references.
• Grouping Data: You can group rows and columns manually by selecting them.
• Using the Subtotals Feature: The Subtotals command calculates subtotal values for the labeled columns you select. Excel automatically inserts and labels the total rows and outlines the list.
• Using the Consolidate Feature: You can consolidate several sheets using the Consolidate feature.

This lesson explains how to turn on the subtotals feature and then use the outline options to simplify the data view.

Before you turn on subtotals, there are a few preliminary steps that must be completed. First, make sure your data is arranged into labeled columns. The data in each column must also be of the same type and it needs to be sorted based on the column you want to group the subtotals by.

1. Click in the column you want to subtotal.
2. Click Sort & Filter on the Home tab.
3. Select a sort option.
   

   Once the data is sorted, the subtotal can be applied.

4. Click the Data tab.
5. Click Subtotal.
6. Click the At each change in list arrow.
7. Select the column you want to subtotal.
   

   This command specifies what it is that you want to subtotal. For example, if you have a list of people, the company they work for, and the amounts of their sales, and you want to subtotal the list by the company name, you would select the column that contains the company name.

8. Click the Use function list arrow,

   Usually you create subtotals with the SUM function, but you can also create subtotals using functions such as COUNT, AVERAGE, MAX, and MIN.

9. Select the function you want the subtotal to perform.
   

10. In the Add subtotal to box, select the check box next to each column that has values you want to subtotal.

    Be careful when selecting your data. The Quick Analysis button will not appear when using the Ctrl key to make multiple selections.

11. Click OK.
    

Once subtotals are applied, the outline levels appear at the left. The outline allows you to collapse the detailed rows or columns and view only the subtotals.

1. Select an outline level.
2. Expand a category to view additional details
   

1. Click the Data tab.
2. Click the Subtotal button.
3. Click Remove All.
   

The chart is moved to its own worksheet.