How to Filter Subtotals in Excel by Group or Total

Formulas & functions · Updated November 20, 2026

To show subtotal rows without their detail rows, use the outline buttons Excel creates with Data > Subtotal, then click outline level 2. To filter groups by subtotal amount, use a PivotTable or make a values-only copy of the subtotal results before filtering: Excel’s SUBTOTAL formulas can change when a filter hides their detail rows. Start with a regular, consistently structured range sorted by the field you want to group.

What You’ll Need

  • Excel for Microsoft 365 on Windows. The Subtotal command is a desktop Excel feature; it is not available in Excel for the web.
  • A data range with one header row, one record per row, and no completely blank rows or columns within the range.
  • A column to group by, such as Region or Department, and a numeric column to total, such as Sales or Hours.
  • If the data is formatted as an Excel Table, convert it to a normal range first. Select a cell in the table and choose Table Design > Convert to Range, then confirm. The Subtotal command does not work directly on a Table.

Step-by-Step

  1. Check and prepare the source data. Make sure each column has a unique, descriptive heading, such as Region, Rep, and Sales. Remove blank rows inside the data and check that the group names are consistent: “North” and “North ” (with an extra space) can be treated as separate groups. Keep one record on each row. Save a copy if the worksheet is important, because adding or removing subtotals changes the layout of the range.
  2. Sort by the field you want to group. Click a cell in that field, then choose Data > Sort A to Z or Data > Sort Z to A. If you need a different sort, use Data > Sort and set the grouping column as the primary sort. Subtotal inserts a new subtotal each time the value in the chosen field changes, so unsorted or inconsistently sorted group names can create repeated subtotal sections.
  3. Open the Subtotal dialog. Click in the range and choose Data > Subtotal. In the dialog, set At each change in to the group field, for example Region. Under Use function, select the calculation you want, such as Sum, Count, or Average. Under Add subtotal to, tick the numeric column to calculate, such as Sales. Leave Replace current subtotals selected when adding a fresh set; clear it only when you intentionally want to retain an existing subtotal layer. Click OK.
  4. Show subtotal rows and hide the detail rows. Excel inserts subtotal rows and creates an outline, with level buttons beside the row numbers. Click level 2 to collapse the detail records while keeping each group subtotal and the grand total visible. Level 1 generally shows only the grand total; the most detailed level expands the records. If the outline buttons are not visible, check File > Options > Advanced and make sure Show outline symbols if an outline is applied is enabled for the worksheet.
  5. Choose the right meaning of “filter.” If you only need a compact view of every group total, level 2 is usually the safest choice. It hides details through the outline rather than applying an AutoFilter to individual rows. You can expand one group using its plus button to inspect its records, then collapse it again. This is useful for reviewing totals, but it does not select groups based on a condition such as Sales greater than 5,000.
  6. For a repeatable filter by group total, create a PivotTable. Select the original detail data (not the inserted subtotal rows), then choose Insert > PivotTable and create it on a new worksheet. Drag the grouping field to Rows and the numeric field to Values. If Excel summarizes the numbers as Count rather than Sum, open the value field settings and select Sum. Use the drop-down beside the row field to select groups, or choose Value Filters to set a condition on the summarized value. A PivotTable is usually the better choice when you need to filter totals repeatedly or refresh the summary as records change.
  7. To filter existing Subtotal results, first make a static report copy. Filtering the original detail range can hide records that the inserted SUBTOTAL formulas use, changing their displayed results. To preserve the figures, select the entire subtotal range and copy it to a new worksheet. On the new sheet, choose Home > Paste > Values so the displayed totals become fixed values. Keep the original worksheet unchanged as the source if you need its live formulas or outline.
  8. Mark the subtotal rows in the static copy. Add a column with a heading such as Is subtotal. If Sales is in column C, and the first data row is row 2, enter the following formula in D2, then fill it down through the copied range. It marks cells in column C whose formulas contain SUBTOTAL; ordinary detail values return FALSE. If you pasted values in the preceding step, do this marking on a separate working copy made before converting formulas to values, or mark the rows first and paste the complete range, including the helper column, as values.
  9. Filter the marked report. Select a cell in the copied range and choose Data > Filter. Use the Is subtotal drop-down to keep only TRUE. Then, if desired, use the numeric column’s drop-down and choose Number Filters to set a condition such as Greater Than or Between. Since this report copy contains fixed values, filtering out the detail rows will not recalculate the subtotal amounts. Check whether the grand total is included: if it is, keep it for context or exclude it when you want to compare group totals only.

For the helper-column example, the assumed layout is subtotal formulas or their copied formula text in C2:C100, with the helper formula entered in D2. The formula returns TRUE for a cell whose formula text contains SUBTOTAL and FALSE for other cells; fill it down to cover the last row.

=IFERROR(ISNUMBER(SEARCH("SUBTOTAL(",FORMULATEXT(C2))),FALSE)

Common Mistakes

  • Applying an ordinary filter and expecting it to keep whole groups together. AutoFilter evaluates rows, not subtotal groups. It can leave a subtotal row visible while hiding some or all of its detail records. Use the outline for a compact view, or use a PivotTable or a static report copy for criteria based on totals.
  • Filtering live SUBTOTAL formulas and trusting the displayed amounts. SUBTOTAL responds to rows hidden by a filter, so a total can change after filtering. Preserve a values-only snapshot before filtering, or use a PivotTable designed for summarized-value filters.
  • Forgetting to sort before inserting subtotals. Excel adds a subtotal whenever the selected group field changes. Sort that field first, or the same group may appear in several separate sections.
  • Trying to subtotal an Excel Table. Convert the Table to a range before using Data > Subtotal. If you need a structured, refreshable summary, keep the Table and build a PivotTable from it instead.
  • Selecting the wrong total column or calculation. Confirm the correct field is checked under Add subtotal to and that the calculation matches the data. For example, Sum is suitable for sales amounts; Count is useful for counting numeric entries, not for adding them.
  • Including an old subtotal layer by accident. Reapplying Subtotal can create confusing nested results. Remove existing subtotals with Data > Subtotal > Remove All before rebuilding the outline, unless you deliberately need nested subtotals.

Tips

  • Use an outline when your goal is simply to expand and collapse details. It preserves the source records and is faster than building a separate filterable report.
  • Use a PivotTable when people need to choose groups, filter by summarized values, or refresh totals after records change. Refresh it after the source data changes; a PivotTable does not always update automatically.
  • Keep the original detail data on its own sheet and put the filtered, values-only report on another. Label the report with its date or source so readers know it is a snapshot, not a live calculation.
  • Before filtering a snapshot, check that the helper column covers every subtotal row and the grand total. A missing helper formula can silently omit a group from the filtered view.
  • On Mac, the Data tab and Subtotal dialog are available in desktop Excel, though their placement can vary with the ribbon layout. Excel for the web may let you view some existing workbook features, but use desktop Excel for the Subtotal workflow described here.

Troubleshooting

The Subtotal command is unavailable or greyed out: Check whether the selected data is an Excel Table and convert it to a range using Table Design > Convert to Range. Also check that the worksheet is not protected and that the selection includes a normal range with a header row.

Excel creates several subtotal rows for what seems like one group: Inspect the grouping values for extra spaces, spelling differences, or inconsistent punctuation, then clean the values and sort the field again. A subtotal is inserted at every change in the group value, not for every visually similar label.

The outline buttons disappeared: Look for collapsed row indicators beside the row numbers, and check the outline-symbol setting under File > Options > Advanced. If the rows are hidden by a filter rather than by an outline, clear the filter first and use the outline level buttons.

A subtotal changes or becomes zero after filtering: That is a warning that the filter is hiding source rows used by the SUBTOTAL formula. Clear the filter to restore the full calculation. For a filterable report, paste the results as values before filtering, or build a PivotTable and apply a Value Filter.

The helper formula shows FALSE for a subtotal row: Confirm that the referenced cell is actually a formula containing SUBTOTAL, rather than a typed or pasted number. Make sure the formula refers to the correct subtotal column and that it was filled down across the subtotal rows. If the workbook uses a different calculation or you already pasted values, identify the subtotal rows before the paste or use a PivotTable instead.

The value filter seems to omit the grand total: Grand totals are not always handled like group items. Check the report range and filter settings, and do not treat the grand total as another group when comparing individual totals. You can keep the grand total outside the filtered list for context.

Frequently Asked Questions

Can I filter subtotal rows using Excel’s regular Filter button?
Yes, but AutoFilter works row by row rather than understanding subtotal groups. It can separate subtotal rows from their details, and filtering can alter live SUBTOTAL results. Use it on a values-only report copy or use a PivotTable for group-level filtering.

How do I show only subtotals and hide the detail rows?
After inserting subtotals with Data > Subtotal, click outline level 2 beside the row numbers. This displays the subtotal rows and grand total while collapsing the detail records; it does not filter groups by their values.

How can I show groups whose totals exceed a number?
Create a PivotTable from the original detail data, place the group field in Rows and the amount field in Values, then use the row field’s Value Filters. If you need to stay with an existing Subtotal report, make a values-only copy and filter the group subtotal values there.

Can I add more than one level of subtotals?
Yes. Excel supports nested subtotal levels, but add the outer grouping first and then the inner grouping. For a simpler analysis with several fields, a PivotTable is often easier to maintain and rearrange.

Will subtotals update when I add new records?
Inserted Subtotal formulas refer to ranges in the worksheet and may not automatically include records added outside those ranges. Rebuild or adjust the subtotal range as needed. A PivotTable based on an Excel Table is generally easier to refresh as the source grows.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.