Groups and Subtotals
Learn how to organize large datasets using grouping and automatically summarize data with the Subtotal command.
Video
Watch the lesson video, then complete the reading and challenge.
Lesson Notes
Read through the key concepts before you try the challenge.
Why Groups and Subtotals Matter
Large datasets can quickly become overwhelming. Groups and Subtotals allow you to organize data into collapsible sections and automatically calculate summaries such as totals, counts, or averages.
When subtotals are applied, Excel creates an outline structure that lets you expand or collapse levels of detail.
Grouping Rows or Columns
You can manually group selected rows or columns to create collapsible sections.
- Select the rows or columns you want to group.
- Go to the Data tab.
- Click the Group command in the Outline group.
Selected columns are grouped and can now be collapsed.
Hide and Show Detail
Once data is grouped, minus (-) and plus (+) buttons appear to the left of the worksheet.
Click the minus sign to collapse (hide) detail. Click the plus sign to expand (show) detail.
Important: Sort Before Using Subtotal
Before creating subtotals, you must sort your data by the column you plan to group by.
For example, if you want to subtotal by T-Shirt Size, sort the worksheet by T-Shirt Size first.
Creating a Subtotal
- Sort your worksheet by the column you want to subtotal.
- Go to the Data tab.
- Click Subtotal.
In the Subtotal dialog box:
• At each change in: Select the grouping column (e.g., T-Shirt Size) • Use function: Choose COUNT, SUM, AVERAGE, etc. • Add subtotal to: Select the column to calculate
Understanding Outline Levels
After applying subtotals, Excel creates outline levels on the left side of the worksheet.
Level 1 shows only the Grand Total. Level 2 shows subtotal rows. Level 3 shows all detailed data.
Removing Subtotals
To remove subtotals entirely:
- Go to Data → Subtotal.
- Click Remove All.
Clearing Groups Without Removing Data
If you want to remove grouping but keep the data, use Clear Outline.
Knowledge Check
What does the Subtotal command do?
Practice File
Download this file and follow along with the lesson.
Challenge
Apply what you've learned in this lesson.
Download the practice workbook and complete the following:
- Click the Challenge tab.
- Sort the worksheet by Grade from Smallest to Largest.
- Use Subtotal to group at each change in Grade.
- Use the SUM function.
- Add subtotals to Amount Raised.
- Select outline Level 2 so only subtotals and the grand total appear.