In Excel, group rows to collapse and expand related detail, and use Subtotals to insert calculated summary rows for sorted data. They work together, but they are different tools: grouping changes what is displayed, while subtotals calculate values such as sums or counts.
Grouping and subtotals: the difference
Excel’s outline feature groups selected rows so you can hide or reveal detail with a click. It does not calculate a total. For example, you could group the five rows of line items that make up a month, then expand that group whenever you need to inspect the entries.
The Subtotal command adds summary rows to a list, usually whenever a selected category changes. It can calculate a sum, count, average, minimum, maximum, and other supported summaries for each group of records. Excel also outlines the resulting detail rows, so you can collapse them to show just the subtotals or a grand total.
There is a third related option: the SUBTOTAL worksheet function. It calculates a result in a cell, but does not create outline groups by itself. The command and function are related because the command uses subtotal formulas in the rows it inserts.
When to use each approach
- Use Group when you already have summary values, or simply want to fold and unfold a block of rows. It is useful for sections in a budget, project plan, or report.
- Use the Subtotal command when a flat list has repeated categories and you want Excel to add a subtotal row for each category, along with an outline.
- Use the SUBTOTAL function when you need a calculation in a chosen cell, especially one that should respond to filtering.
For example, a sales list with one row per transaction can be sorted by region and subtotaled by region. A monthly report that already has a row for each month’s total may need only row grouping to hide its underlying detail. If the data is likely to be reshaped or analyzed in several ways, a PivotTable may be more suitable than inserting subtotal rows into the source list.
Prepare the data before using Subtotal
The Subtotal command works best with a simple, continuous list. Put one field name in each column’s header, keep each record on one row, and avoid blank rows or columns inside the list. Remove existing subtotal rows before applying the command again unless you deliberately want nested summaries.
Sort the records by the field you plan to group on. If you want a subtotal for each region, sort by Region first. The command detects a change in that field as it moves down the list; it does not gather matching values from scattered parts of an unsorted list. You can add a secondary sort, such as Region and then Salesperson, if you want subtotals at both levels.
Excel Tables are convenient for expanding lists and managing formulas, but the Data > Subtotal command is unavailable for a table. To use that command, select a cell in the table, choose Table Design > Convert to Range, and confirm. This changes the table into an ordinary range, so consider whether you still need table features before converting.
Insert subtotal rows with the ribbon
- Click a cell in the list and sort by the field that should define each group, such as Region.
- On the ribbon, select Data > Subtotal. If the command is unavailable, check whether the data is an Excel Table and convert it to a range if appropriate.
- In the dialog, choose the grouping field in At each change in, such as Region.
- Choose a calculation in Use function, such as Sum or Count.
- In Add subtotal to, select the numeric columns to summarize. You can subtotal more than one column.
- Choose whether to replace existing subtotals, insert a page break between groups, or show the summary below the detail, then select OK.
For a regional sales report, select Region in At each change in, Sum in Use function, and Sales in Add subtotal to. Excel inserts a regional sum after each block of transactions and a grand total at the end. If you also select Units, it adds a subtotal for that column too. The labels and arrangement in the dialog can vary slightly by Excel build, but the essential choices are the grouping field, calculation, and columns to summarize.
Group and ungroup rows manually
To create a group without inserting calculations, select the row numbers for the detail rows. On Windows, choose Data > Group, then choose Rows if Excel asks what to group. A bracket and a minus button appear beside the row numbers; click the minus button to collapse the rows. The button changes to a plus sign, which expands them again. The summary row should usually remain outside the group so it stays visible when the details are collapsed.
You can also use the Windows shortcut Alt+Shift+Right Arrow to group selected rows and Alt+Shift+Left Arrow to ungroup them. On Mac, shortcuts can depend on the Excel version and keyboard layout; use the Data tab’s Group and Ungroup commands if the Windows shortcuts do not apply. In Excel for the web, use the Data tab’s outline controls when available; some desktop outline features and dialogs may differ.
To remove a manual group, select its rows and choose Data > Ungroup. To remove all outline groupings on a sheet, use the arrow next to Ungroup and select Clear Outline. Clearing an outline removes the grouping structure, not the row contents. For command-generated subtotals, use the Subtotal dialog’s Remove All button to remove the inserted subtotal rows and outline.
Control the outline and read its levels
When a sheet has groups or subtotals, outline level buttons numbered 1, 2, and so on appear near the top-left of the worksheet. Selecting a level displays that level of summary. In a simple subtotal report, level 1 commonly shows only the grand total, level 2 shows the category subtotals, and the highest level shows all detail. The exact number of levels depends on how many nested groups or subtotal levels the sheet contains.
Use the plus and minus controls beside a group to expand or collapse only that section. If those symbols are missing, the outline may be hidden or the rows may not be grouped. Check File > Options > Advanced for the worksheet display setting for outline symbols, and check whether the relevant rows are hidden. A collapsed group hides rows from view; it does not delete them or change their stored values.
Excel can also create an outline for suitable data that already contains summary formulas. Select a cell in the range and look for Data > Group > Auto Outline. Auto Outline depends on Excel recognizing a consistent relationship between detail and summary rows; it may not work well with irregular layouts. Manual grouping gives you more control when the structure is not easy to infer.
How SUBTOTAL formulas behave
The SUBTOTAL function is useful when you want a calculation without inserting subtotal rows. For example, with sales amounts in C2:C4 of 120, 80, and 150, this formula returns 350:
Assuming the sales figures are in C2:C4: =SUBTOTAL(9,C2:C4)
In this function, 9 means SUM. Other function numbers select calculations such as AVERAGE, COUNT, MAX, and MIN. A useful distinction is that function numbers 1–11 include manually hidden rows, while 101–111 exclude manually hidden rows. Rows excluded by a filter are excluded by either form. For example, 109 means SUM while excluding manually hidden rows as well as filtered-out rows.
Excel’s Subtotal command inserts SUBTOTAL formulas rather than ordinary SUM formulas. This helps avoid counting lower-level subtotal results again when creating nested subtotals, and it lets the formulas respond to filtered lists. If you edit or copy subtotal formulas yourself, make sure the reference ranges include the intended detail rows and do not accidentally include unrelated records.
Common problems and practical fixes
- The Subtotal command is disabled: The selected data may be an Excel Table. Convert it to a range if you want to use the command, or use a PivotTable or worksheet formulas instead.
- Some categories get separate subtotals: Sort by the grouping field first. Differences such as extra spaces or inconsistent spelling can also make apparently similar labels count as separate categories.
- The grand total seems wrong: Check which columns were selected, whether numeric values are stored as text, and whether filters are hiding records. Remember that SUBTOTAL excludes filtered-out rows.
- Rows do not collapse as expected: Confirm that you selected the detail rows rather than the summary row, and check for nested groups or hidden outline symbols.
- Repeated use creates confusing results: Remove old subtotals with Data > Subtotal > Remove All, then sort and apply the new grouping. Existing subtotal rows can otherwise be treated as part of the data.
Grouping is a display organization, not a security measure. Anyone with access to the workbook may be able to expand the rows or reveal hidden content. Do not rely on collapsing rows to protect confidential information.
Subtotals versus PivotTables
Subtotal rows are easy to read in a normal worksheet and can be printed with the underlying records. They are a good fit for a list that is already sorted into a clear hierarchy and needs a quick set of totals. Their main limitation is that they alter the layout of the list by inserting rows, and changing the grouping often means removing and rebuilding the subtotals.
A PivotTable summarizes data without inserting subtotal rows into the source range. It is generally better when readers need to rearrange categories, compare multiple fields, filter interactively, or create several views from the same records. A PivotTable may require refreshes after the source data changes, depending on how it is configured. Manual grouping remains the simplest choice when the goal is merely to hide and show existing rows.
Frequently Asked Questions
How do I collapse all grouped rows in Excel?
Select outline level 1 near the top-left of the sheet to show the most summarized view. In a straightforward subtotal outline, this typically hides the detail and leaves the grand total. Choose a higher level to reveal more detail.
Can I group rows without adding a subtotal?
Yes. Select the detail rows and choose Data > Group. This creates an expandable outline but does not insert a calculation. Keep any heading or summary row outside the selected detail rows if it should stay visible.
Why is Subtotal greyed out in Excel?
A common reason is that the records are formatted as an Excel Table. Convert the table to a range if you want to use Data > Subtotal. The command may also be unavailable if the selection or workbook state does not support it.
Do grouped or collapsed rows affect formulas?
Grouping alone only hides rows; it does not remove their values from calculations. A normal SUM can include hidden rows. SUBTOTAL has specific behavior for filtered and manually hidden rows, controlled partly by its function number.
How can I remove subtotal rows but keep my data?
Choose Data > Subtotal and select Remove All. This removes the command-generated subtotal rows and outline while leaving the underlying records. If you created groups manually, use Data > Ungroup or Clear Outline instead.
Related
- Excel Solver Answer Report: Create and Read It
- How to Run Solver in Excel: Setup and Optimization
- K-Means Clustering in Excel: Solver and Easier Methods
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Find and Replace in One ColumnYes. In Excel for Microsoft 365 on Windows, select the column or the specific cells you want to…
- How to Auto Wrap Text in Google Sheets: A Quick GuideTo automatically show long text on multiple lines in Google Sheets, select the cells, then choose Format >…
- View Mail Merge Rules in Word and Edit Their FieldsIn Microsoft Word for Microsoft 365 on Windows, open the merge document and choose Mailings > Rules to…
- How to Align Checkboxes in Excel: Step-by-StepTo align checkboxes in Excel, first identify whether they are in-cell checkboxes or floating objects. For in-cell checkboxes,…
- Excel Remove Duplicates: Keep One Copy of Each RowYes. In Excel for Microsoft 365 on Windows, select your data and choose Data > Remove Duplicates; Excel…
- Excel Time Value: How Excel Stores and Calculates TimeIn Excel, a time value is a number representing a fraction of a 24-hour day: for example, 6:00…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.