Excel: Select Only Subtotal Rows, Not the Detail Rows

How-to basics · Updated November 18, 2026

To select only subtotal rows in Excel for Microsoft 365 on Windows, first create or use an outline, click outline level 2 to show the subtotal rows, select the range you want while excluding the grand total, then choose Home > Find & Select > Go To Special > Visible cells only. Press OK; Excel selects the visible cells and skips the collapsed detail rows. If you only need to view or print the subtotals, clicking outline level 2 may be enough.

The quickest method: use the outline

This method works when the subtotal rows are part of an Excel outline, such as one created by the Subtotal command. The outline groups detail rows beneath their summary rows. Collapsing the detail level leaves the summaries visible, so you can select visible cells without including the hidden records.

  1. Click a cell in the list. If you have not created subtotals yet, sort the list by the field you want to group, then choose Data > Outline > Subtotal. Set the grouping field, the calculation, and the column to summarize, then click OK.
  2. Use the outline level buttons, usually numbered 1, 2, and 3, at the left of the worksheet. Click 2 to show each subtotal row and the grand total while hiding the individual detail rows.
  3. Select the cells or rows that cover the subtotal results you need. Start below the column headings, and stop above the grand total if you do not want that row included.
  4. Choose Home > Find & Select > Go To Special, select Visible cells only, and click OK. On Windows, Alt+; is the shortcut for selecting visible cells only after you have selected a range.

The selection can look discontinuous because the hidden detail rows are omitted. This is expected: Excel has selected the visible portions of your range rather than turning the hidden rows back on. On Excel for Mac, use the Go To Special ribbon route if the Windows shortcut is unavailable or differs on your keyboard. Excel for the web has fewer desktop-style selection tools; if you cannot find the Go To Special option there, open the workbook in the desktop app for this workflow.

What the outline levels show

An outline is a way to expand or collapse groups of rows. When Excel’s Subtotal command inserts summaries, it also creates outline groups. For a typical list with one level of grouping, level 1 shows only the grand total, level 2 shows the subtotals and grand total, and level 3 expands the detail rows. The exact number of levels can vary if the worksheet has nested groups or manually created outlines.

This distinction matters because clicking level 2 does not itself select the subtotal rows. It changes which rows are visible. The Go To Special command then limits the selection to visible cells within the range you selected. Think of the two operations as separate: collapse the detail rows first, then select visible cells. If you skip the second operation, selecting a broad range can still include hidden cells for actions such as some edits or copy operations.

The grand total is usually visible at level 2 too, so it is easy to include it accidentally. If you want only category subtotals, leave the grand-total row outside the range before applying Visible cells only. If you do want the overall total, include it. Check the row labels and values before copying or formatting, especially when the worksheet has more than one outline level.

Create subtotal rows when they do not exist

If your sheet has category labels and numeric values but no summary rows, Excel can insert subtotals and create the outline for you. Before using the command, sort the records by the field that defines each group. For example, if you want a sales subtotal for each region, sort the records by Region so identical region names are together.

  • Click within the data list and open Data > Outline > Subtotal.
  • In At each change in, choose the grouping column, such as Region.
  • In Use function, choose the summary calculation, such as Sum or Count.
  • In Add subtotal to, check the numeric column or columns to summarize.
  • Choose OK, then click outline level 2 and select visible cells only as described above.

Excel inserts a subtotal after each change in the chosen field and adds a grand total by default. The command operates on a list rather than a modern formatted Excel table. If Subtotal is unavailable because the data is formatted as a table, use Table Design > Convert to Range, if appropriate, and then apply the command. Converting removes the table’s structured behavior, so consider making a copy first or using a different summary approach if you rely on table features.

When subtotal formulas were typed manually

A worksheet may contain rows labeled “Subtotal” without having an outline. Perhaps someone inserted formulas manually, copied summary rows from another workbook, or built the report with formulas. In that case, clicking outline level 2 will not reveal those rows unless an outline already exists. Excel does not identify a row as a subtotal merely because its formula uses the SUBTOTAL function or its label contains the word “Subtotal.”

If the rows are already visible and the detail rows are not hidden, you need another way to identify them. One practical option is to add a helper column that marks summary rows using a consistent label or other reliable characteristic, then filter that column to show only marked rows. If the rows follow a predictable pattern, you can also group the detail rows manually using Data > Group, then collapse the groups. Avoid relying on a visual clue, such as bold font alone, when the workbook contains inconsistent formatting.

There is no universal built-in command that searches a worksheet and selects every row containing a subtotal formula. Subtotal formulas can be written in different ways, and a formula may calculate a subtotal without being part of a visible summary row. If the report is generated regularly, a consistent layout or a dedicated marker column makes later selection much safer.

Excel tables, filters, and visible cells

Do not confuse subtotal rows with filtered results. A filter hides records that do not match the criteria, but it does not create subtotal rows. You can use Go To Special > Visible cells only on a filtered range to select the displayed records, but that selects the visible records—not specifically rows that contain subtotals. If the filtered list also contains subtotal rows, the selection can include both types unless the range or filter distinguishes them.

Likewise, a Total Row in an Excel table is not the same as a set of grouped subtotal rows. Table Design > Total Row adds one summary row at the end of a table, and its calculation can respond to filtering. It does not create a separate subtotal for each category or the outline controls used by the Subtotal command. For totals by category, a PivotTable or a properly grouped list may be more suitable.

When using a filtered list, selecting visible cells only is useful for formatting or copying displayed values without including hidden records. Still, confirm the selection before making changes. If you are selecting an entire worksheet column rather than a bounded data range, visible cells outside the report may also be selected, depending on the current filter and sheet layout.

Copying, formatting, and editing the selection

Once visible subtotal cells are selected, you can apply formatting, such as bold text or a number format, to the displayed summaries. You can also copy the selection, but a selection made from separated visible areas is a multiple-area selection. Pasting a multiple-area selection may not behave like pasting one continuous block: Excel can reject some destination layouts or place data differently than expected.

For a clean extract, consider copying the visible subtotal cells and pasting them into a new worksheet, then inspect the result before using it. Another option is to create a PivotTable or a separate summary report if you need a stable, reusable output rather than a one-time copy. If your goal is simply to format subtotal rows, selecting visible cells within the collapsed outline is generally more direct than copying them elsewhere.

Be careful when deleting or clearing cells. Hiding detail rows does not remove them, and a selection made without Visible cells only can include hidden rows. Conversely, visible-cells-only selection affects only the visible parts of the selected range, which is precisely why it is useful here. Save a copy before making broad edits to an important report.

Common problems and how to fix them

  • Level 2 is missing: The list may not have an outline. Use Data > Outline > Subtotal to create one, or group the detail rows manually.
  • All detail rows are still visible: Check that you clicked the outline level button, not just a worksheet row. If outline controls are hidden, check Excel Options > Advanced for the setting that displays outline symbols when an outline is applied.
  • The grand total is selected too: Level 2 normally displays it. Select a range that ends before the grand-total row, or include it intentionally.
  • Subtotal is disabled: The data may be an Excel table. Convert the table to a range if that fits your workflow, or use a PivotTable for grouped summaries.
  • Only some rows appear in the selection: Visible cells only selects visible cells within the range you marked. Make sure the range spans every subtotal row you need and does not include irrelevant headers or columns.
  • The selection will not paste as expected: The visible cells form multiple separate areas. Paste into a suitable blank area or use a dedicated summary method, then verify the output.

Frequently Asked Questions

How do I select only subtotal rows in Excel?
Collapse the outline to level 2, select the range containing the subtotal rows but not the grand total if you want to exclude it, then choose Home > Find & Select > Go To Special > Visible cells only.

Does clicking outline level 2 select the subtotal rows?
No. It displays subtotal rows and usually the grand total by hiding detail rows. To make a selection that skips hidden details, use Visible cells only on the range you selected.

How do I select subtotals but not the grand total?
After collapsing to level 2, select a range that stops at the row above the grand total, then apply Visible cells only. The grand total is generally visible at level 2, so excluding its row from your range is the simplest approach.

Can I select subtotal rows in an Excel table?
A table’s Total Row is different from grouped subtotals, and the Data Subtotal command is not available for a formatted table. Convert the table to a range if suitable, or create a PivotTable or another summary for category-level totals.

Why does Excel select visible detail rows as well?
Visible cells only does not determine which rows are summaries. It selects every visible cell in the range, including detail records that remain visible. First hide the detail rows with an outline, or use a filter or marker column that isolates the subtotal rows.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.