Groups and Subtotals

In Excel groups and subtotals let you can hide data and summarize groups. Use groups and subtotals in Excel to better read data too.

To view groups by level:

When you create subtotals, your worksheet it is divided into different levels. You can switch between these levels to quickly control how much information is displayed in the worksheet by clicking the Level buttons to the left of the worksheet. In our example, we'll switch between all three levels in our outline. While this example contains only three levels, Excel can accommodate up to eight.

  1. Click the lowest level to display the least detail. In our example, we'll select level 1, which contains only the grand count, or total number of T-shirts ordered.
    Setting the group detail to level 1
  2. Click the next level to expand the detail. In our example, we'll select level 2, which contains each subtotal row but hides all other data from the worksheet.
    Setting the group detail to level 2
  3. Click the highest level to view and expand all of your worksheet data. In our example, we'll select level 3.
    Setting the group detail to level 3

You can also use the Show and Hide Detail buttons to show and hide the groups within the outline.

Using the Show and Hide Detail buttons in a subtotal


To remove subtotals:

Sometimes you may not want to keep subtotals in your worksheet, especially if you want to reorganize data in different ways. If you no longer want to use subtotaling, you'll need remove it from your worksheet.

  1. Select the Data tab, then click the Subtotal command.
    The Subtotal button on the Data tab
  2. The Subtotal dialog box will appear. Click Remove All.
    Removing subtotals in the Subtotal dialog
  3. All worksheet data will be ungrouped, and the subtotals will be removed.

To remove all groups without deleting the subtotals, click the Ungroup command drop-down arrow, then choose Clear Outline.

Clear Outline in the Ungroup menu on the Data tab