Copy Just Totals from Subtotaled Data

September 23, 2022 - by Bill Jelen

Problem: I’ve added subtotals and collapsed to the #2 view. My manager wants me to send him just the total rows in a file. When I copy and paste, I get all of the detail rows as well.

Strategy: You can use an obscure command in the Go To Special dialog box to assist with this task. Follow these steps:

1. Choose the 2 Group & Outline button to put the data in subtotal view.
2. Select the entire data set. Use Ctrl+* or Ctrl+A or Ctrl+Shift+8. Gotcha: If you forget to hold shift and press Ctrl+8, Excel will remove the Group & Outline symbols. Bring them back again with Ctrl+8.
3. Bring up the Go To Special dialog by choosing Home, Find & Select, Go To Special. Alternatively, you can press the F5 key and click the Special button in the lower-left corner of the Go To dialog.
4. In the Go To Special dialog, select Visible Cells Only

The Go To Special dialog box with Visible Cells Only selected. This is a versatile dialog, with fifteen different choices and 8 sub-choices. Since this is the first time the dialog is mentioned in the book, the main choices are: Comments, Constants, Formulas, Blanks, Current Region, Current Array, Objects, Row Differences, Column Differences, Precedents, Dependents, Last Cell, Visible Cells Only, Conditional Formats, and Data Validation.
The sub-choices work like this: If you choose Constants or Formulas, then you can choose any combination of Numbers, Text, Logicals, or Errors.
If you choose Precedents or Dependents, then you can choose either Direct Only or All Levels. If you choose Data Validation or Conditional Formats, then you can choose All or Same. — Figure 710. Select Visible Cells Only.

5. Click OK. There will be thin white lines above and below each subtotal. If you are in Excel 2007, the color of selected cells is too light to make out the white lines. You’ve now selected only the visible cells.

A data set with Subtotals and the #2 Group and Outline button selected shows only the Customer Total rows, the heading, and the Grand Total row. Choose Go To Special, Visible Cells Only to select only the visible rows. — Figure 711. The white lines indicate the hidden rows are unselected.

6. Press Ctrl+C to copy. The marching ants will surround each row.

If you selected Visible Cells Only, then when you copy, each individual row will have the marquee or "dancing ants" — Figure 712. Copy the subtotal lines.

7. Switch to a new workbook. Press Ctrl+V to paste. Excel will paste just the subtotal rows.

After pasting to a new workbook, you get just the subtotal rows. — Figure 713. Paste the subtotals only to a new workbook.

You might think that you would have to select Paste, Values instead of just doing a paste. However, the Paste command works okay. Excel converts the SUBTOTAL functions to values.

Additional Details: Instead of selecting Go To Special, Visible Cells Only, you can press Alt+; (that is, hold down the Alt key and type a semicolon).

Alternate Strategy: There is a Quick Access Toolbar icon for Select Visible Cells. Follow the steps in Make Your Most-Used Icons Always Visible.

The fast way to Select Visible Cells is an icon in the Quick Access Toolbar. It looks like four window panes in a 2 by 2 grid with a gap in between — Figure 714. Add Select Visible Cells to the QAT.

This article is an excerpt from Power Excel With MrExcel

Title photo by charlesdeluvio on Unsplash