Excel 2013: Groups and Subtotals

Lesson 20: Groups and Subtotals

/en/excel2013/filtering-data/content/

Introduction

Worksheets with a lot of content can sometimes feel overwhelming and even become difficult to read. Fortunately, Excel can organize data in groups, allowing you to easily show and hide different sections of your worksheet. You can also summarize different groups using the Subtotal command and create an outline for your worksheet


Optional: Download our practice workbook.

To group rows or columns:

  1. Select the rows or columns you want to group. In this example, we'll select columns A, B, and C.
    Screenshot of Excel 2013Selecting columns to group
  2. Select the Data tab on the Ribbon, then click the Group command.
    Screenshot of Excel 2013Clicking the Group command
  3. The selected rows or columns will be grouped. In our example, columns A, B, and C are grouped together.
    Screenshot of Excel 2013The grouped columns

To ungroup data, select the grouped rows or columns, then click the Ungroup command.

Screenshot of Excel 2013Clicking the Ungroup command

To hide and show groups:

  1. To hide a group, click the Hide Detail button.
    Screenshot of Excel 2013Hiding a group
  2. The group will be hidden. To show a hidden group, click the Show Detail button.
    Screenshot of Excel 2013Clicking the Show Detail button to show the hidden group

Creating subtotals

The Subtotal command allows you to automatically create groups and use common functions like SUM, COUNT, and AVERAGE to help summarize your data. For example, the Subtotal command could help to calculate the cost of office supplies by type from a large inventory order. It will create a hierarchy of groups, known as an outline, to help organize your worksheet.

Your data must be correctly sorted before using the Subtotal command, so you may want to review our lesson on Sorting Data to learn more.

To create a subtotal:

In our example, we will use the Subtotal command with a T-shirt order form to determine how many T-shirts were ordered in each size (Small, Medium, Large, and X-Large). This will create an outline for our worksheet with a group for each T-shirt size and then count the total number of shirts in each group.

  1. First, sort your worksheet by the data you want to subtotal. In this example, we will create a subtotal for each T-shirt size, so our worksheet has been sorted by T-shirt size from smallest to largest.
    Screenshot of Excel 2013The worksheet sorted by t-shirt size
  2. Select the Data tab, then click the Subtotal command.
    Screenshot of Excel 2013Clicking the Subtotal command
  3. The Subtotal dialog box will appear. Click the drop-down arrow for the At each change in: field to select the column you want to subtotal. In our example, we'll select T-Shirt Size.
  4. Click the drop-down arrow for the Use function: field to select the function you want to use. In our example, we'll select COUNT to count the number of shirts ordered in each size.
  5. In the Add subtotal to: field, select the column where you want the calculated subtotal to appear. In our example, we'll select T-Shirt Size.
  6. When you're satisfied with your selections, click OK.
    Screenshot of Excel 2013Creating a subtotal
  7. The worksheet will be outlined into groups, and the subtotal will be listed below each group. In our example, the data is now grouped by T-shirt size, and the number of shirts ordered in that size appears below each group.
    Screenshot of Excel 2013The outlined and subtotaled data

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 image of button for levels 1, 2, 3 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.


    Screenshot of Excel 2013Viewing data at the lowest level
  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.
    Screenshot of Excel 2013Viewing data at the next level
  3. Click the highest level to view and expand all of your worksheet data. In our example, we'll select level 3.
    Screenshot of Excel 2013Viewing data at the highest level

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

Screenshot of Excel 2013Showing and hiding the new groups within the outline

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.
    Screenshot of Excel 2013Clicking the Subtotal command
  2. The Subtotal dialog box will appear. Click Remove All.
    Screenshot of Excel 2013Removing subtotaling
  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.

Screenshot of Excel 2013Removing all groups

Challenge!

  1. Open an existing Excel workbook. If you want, you can use our practice workbook.
  2. Try grouping a range of rows or columns together. If you are using the example, group columns D and E.
  3. Use the Show and Hide Detail buttons to hide and unhide the group.
  4. Try ungrouping the group. If you are using the example, ungroup columns D and E.
  5. Outline your worksheet using the Subtotal command. If you are using the example, outline by T-shirt size.
  6. Remove subtotaling from your worksheet.

/en/excel2013/tables/content/