Excel: Copy Subtotal Rows Only Without Detail Rows

Yes. In Excel for Microsoft 365 on Windows, collapse the outline to level 2, select the range you want, choose Home > Find & Select > Go To Special > Visible cells only, and copy it. Excel then copies the visible subtotal rows instead of the hidden detail rows; level 2 also shows the grand total, so remove that row afterward if you do not want it.

How Excel subtotal rows and outlines work

Excel’s Subtotal command inserts summary rows into a sorted list and groups the underlying detail rows into an outline. For example, a sales list might contain several transactions for each region. Subtotal can add a sum after each region’s transactions and a final grand total at the bottom. The outline controls let you show or hide the detail behind those summaries.

On Windows desktop Excel, the command is on the Data tab in the Outline group: Data > Subtotal. It is designed for a normal range, not an Excel table. If Subtotal is unavailable because your data is formatted as a table, use Table Design > Convert to Range first, or create summaries another way. Sort the data by the field you want to group, such as Region, before inserting subtotals; otherwise, the same region can appear in separate groups.

The outline buttons usually show levels 1, 2, and 3. Level 1 displays only the grand total, level 2 displays each group’s subtotal plus the grand total, and level 3 displays the detail rows as well. The exact visible content depends on the outline and any extra grouping in the worksheet, but level 2 is the usual starting point when you want subtotal rows without their details.

Copy subtotal rows only: the reliable method

Click outline level 2 at the upper-left of the worksheet, beside the row numbers. Check that you can see the group subtotal rows and the grand total, but not the transaction-level rows.
Select the cells to copy, not just a single cell. For example, select A2:F100 if the summaries occupy columns A through F and row 1 contains the headings. Adjust the range to match your sheet.
Choose Home > Find & Select > Go To Special, select Visible cells only, and click OK. This step makes the selection explicitly exclude rows hidden by the outline.
Copy with Ctrl+C, click the destination cell or worksheet, and paste with Ctrl+V. On a Mac, use Command+C and Command+V; the menu route to Go To Special may differ by Excel release, so use Find & Select and choose Visible cells only if available.

If you want headings in the copied result, include the heading row in your selection. If you do not, start below it. A worksheet title or notes above the data can also be left out by selecting only the summary table’s actual range. Copying to a new worksheet is often a clear way to keep the extracted summaries separate from the original outline.

Why select Visible cells only?

Collapsing an outline hides rows; it does not delete them or change the underlying range. A selection made over a range that includes hidden rows can therefore be confusing: the highlighted area may span both visible and hidden records. Go To Special’s Visible cells only option narrows the selection to the cells currently displayed. The Windows shortcut for that option is Alt+; (Alt plus semicolon), after selecting the range.

This is useful beyond subtotals. The same command can copy visible rows from a filtered list or a sheet with manually hidden rows. It also means that any other hidden rows or columns inside your selected range are omitted. Before copying, check that the visible cells are exactly the ones you intend to transfer; an unrelated filter or hidden column can change the result.

Excel for the web has fewer desktop-style selection and outline tools, and the Subtotal command is not available there in the same way as in Windows desktop Excel. For a dependable workflow involving Excel’s Subtotal-created outline, use the desktop app. On Mac desktop, use the visible-cells option in Go To Special rather than relying on the Windows Alt+; shortcut.

Exclude the grand total or include the headings

At outline level 2, Excel normally shows both the group subtotals and the grand total. If your goal is a list of group summaries only, there are two straightforward choices. The simplest is to copy the visible cells and delete the grand-total row from the pasted copy. Alternatively, select the visible subtotal rows in separate blocks while leaving the grand-total row out, then copy the resulting selection; separate-area selections can be less convenient to paste, so check the destination layout.

Do not use level 1 to try to hide only the grand total. Level 1 generally shows the grand total by itself and hides the group subtotals too. If you need only certain groups, you can copy the level-2 summaries and remove unwanted rows from the destination, or use a filter on the copied results. Keep a copy of the original data if the extracted list will be used for reporting or further calculations.

For a clean output, decide whether the heading row belongs in the copied block. A heading is helpful when you will share or reuse the copied data, but including it when pasting into an existing table can create a duplicate header. Also check that every subtotal row has the same column structure; merged cells or irregular layouts can make a pasted block harder to work with.

Choose between formulas and fixed values

When you copy subtotal rows, Excel may carry their formulas into the new location. Excel’s Subtotal feature generally inserts formulas using the SUBTOTAL function, which calculates a result over the detail range. A copied formula can keep referring to source cells, and relative references may adjust when pasted elsewhere. That is useful when you want a live summary linked to the original data, but it can be surprising if you expect a standalone report.

If the copied figures should remain as they look now, paste values instead of formulas. After copying, choose Home > Paste > Values at the destination. This retains the displayed results but not the formulas that produced them. The pasted values will not update if the source transactions change, so keep the original worksheet or another source of record if updates may be needed later.

Formatting is a separate choice. A regular paste can bring across number formats, borders, and other cell formatting along with content. If you want the values without the original styling, use the paste options available from Home > Paste, or paste values and then apply the destination’s formatting. For a formal report, verify number formats and totals after pasting rather than assuming the destination inherited everything as intended.

Common problems and how to fix them

Detail rows still appear. Confirm that the outline is collapsed to level 2 and use Visible cells only before copying. If you copied before setting that option, repeat the selection and copy.
Only one total appears. You may have clicked level 1, which typically shows the grand total alone. Click level 2 to reveal the group subtotals.
The grand total is included. This is expected at level 2. Remove it from the pasted result or exclude it with a carefully selected range or separate selection.
The pasted formulas show unexpected results. They may still refer to the original detail range, or their references may have adjusted. Paste values if you need a fixed snapshot.
The copied output skips rows you wanted. Check for filters, manually hidden rows, and hidden columns. Visible cells only excludes all cells hidden at the time, not just detail rows hidden by the outline.
Subtotal is disabled. Check whether the data is an Excel table. Convert the table to a range if appropriate, and make sure the data is sorted by the grouping field before using Subtotal.

Another source of confusion is thinking that hiding detail rows removes them from calculations. The outline changes what is displayed, not the source records. Excel’s SUBTOTAL calculations are designed to work with grouped data and can treat filtered or manually hidden rows differently depending on the function number used. The copied summary is a selection of displayed cells; it is not a replacement for the underlying data or a guarantee that future source changes will be reflected if you paste values.

Alternatives when you need a reusable summary

Copying visible subtotal rows is a practical one-time extraction. If you need summaries that refresh as records are added or changed, consider a PivotTable instead. A PivotTable groups a field such as Region and summarizes a numeric field such as Sales without inserting subtotal rows into the source list. You can copy its displayed results when needed, but refresh the PivotTable to bring in updated source data.

For a short list or a one-off report, the outline method is often quicker because the subtotals already exist. For recurring analysis, a PivotTable can be easier to maintain, especially when you want to rearrange categories or change the summary calculation. If the requirement is simply to display selected records, filters may be more suitable than inserting subtotals. Choose based on whether the output is a snapshot, a live calculation, or an interactive summary.

Before sharing an extracted report, check that it communicates what each number represents. Keep useful labels such as the group name and the measure being summed, and make clear whether the grand total is included. If you paste values, note that the result is static; if you retain formulas, keep their source references intact. These small checks help prevent a clean-looking summary from being mistaken for a complete or automatically updated dataset.

Frequently Asked Questions

Can I copy only subtotal rows in Excel without the grand total?
Yes. Copy the level-2 visible cells, then delete the grand-total row from the pasted copy. Level 2 normally shows both group subtotals and the grand total; level 1 hides the group subtotals as well.

What is the shortcut for copying visible cells only?
On Windows, select the range and press Alt+; to select visible cells only, then press Ctrl+C. Paste with Ctrl+V. On Mac, use Go To Special’s Visible cells only option and Command+C and Command+V rather than the Windows shortcut.

Will copying subtotal rows copy formulas or values?
A normal paste can carry formulas and formatting. To keep only the displayed results, use Home > Paste > Values. Those values will not update when the original data changes.

Why can’t I use Data > Subtotal?
The Subtotal command is intended for a normal cell range and may be unavailable when your records are formatted as an Excel table. Convert the table to a range if that suits your workbook, or use a PivotTable for a reusable summary.

Does this method work with filtered rows too?
Yes. Visible cells only also excludes rows hidden by a filter. That is useful when intended, but check filters and manually hidden rows before copying so the result does not omit summaries unexpectedly.

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.