Organizing Data with Groups and Subtotals in Excel

Large worksheets can feel overwhelming. Too much data can be hard to read. Luckily, Excel lets you group data. This helps you show or hide sections easily. You can also use the Subtotal command to summarize data.

Download our practice workbook.

Grouping Rows or Columns

To group rows or columns:

  1. Select the rows or columns you want to group. For example, select columns B, C, and D.

  2. Click the Data tab. Then, click the Group button.

  3. The selected rows or columns will now be grouped.

To ungroup data, select the grouped rows or columns. Then, click the Ungroup button.


Hiding and Showing Groups

To hide a group, click the minus sign (Hide Detail button).



To show a hidden group, click the plus sign (Show Detail button).

Creating Subtotals

The Subtotal command helps summarize data. It creates groups and uses functions like SUM, COUNT, and AVERAGE. For example, you can count the number of T-shirts ordered by size.

Steps to create a subtotal:

  1. Sort your data first. Make sure the column you want to subtotal is sorted.

  2. Click the Data tab, then click Subtotal.

  3. In the Subtotal box:
    • Select the column for the subtotal (e.g., T-Shirt Size).
    • Choose a function (e.g., COUNT to count orders).

  4. Select where to add the subtotal.

  5. Click OK. The worksheet will now show groups with subtotal rows.

Using Outline Levels

Subtotals create levels in the worksheet. You can switch between levels to control the amount of detail shown.

  • Level 1: Shows only the grand total.

  • Level 2: Shows subtotals but hides other data.

  • Level 3: Shows all data.

You can also use the Show and Hide buttons to expand or collapse groups.


Removing Subtotals

If you don’t need subtotals anymore:

  1. Click the Data tab, then click Subtotal.

  2. In the Subtotal box, click Remove All.

To remove groups but keep subtotals, click Ungroup, then select Clear Outline.


Now, you can organize large worksheets easily!