Contact Us
Log In Contact Us
Excel Subtotal | CustomGuide
Limited Free Version
Excel Basic

Excel Subtotal

How to Use the Outline and Subtotal Features in Excel

Excel Subtotal

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.

Add Subtotals

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.
    Outline and Subtotal

    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.
    Outline and 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.
    Outline and Subtotal
  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.
    Outline and Subtotal

Outline Options

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
    Outline and Subtotal

Remove Outlines and Subtotals

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

The chart is moved to its own worksheet.

You Get:
  • LinkedIn-ready certificate
  • Skill assessments
  • Bite-sized, interactive lessons
  • Saved progress

Links to Other Courses