• Excel Book Excerpt

Excel Copy Just Totals from Subtotaled Data

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Copy Just Totals from Subtotaled Data

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

LE10000674.jpg 

Figure 651 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.

LE10000675.jpg

Figure 652 The white lines indicate the hidden rows are unselected.

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

LE10000676.jpg

Figure 653 Copy the subtotal lines.

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

LE10000677.jpg

Figure 654 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).

<-Previous Topic Next Topic->

For more resources for Microsoft Excel:

privacy policy