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:
- Choose the 2 Group & Outline button to put the data in subtotal view.
- 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.
- 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.
- In the Go To Special dialog, select Visible Cells Only
- Select Visible Cells Only.
- 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.
- The white lines indicate the hidden rows are unselected.
- Press Ctrl+C to copy. The marching ants will surround each row.
- Copy the subtotal lines.
- Switch to a new workbook. Press Ctrl+V to paste. Excel will paste just the subtotal rows.
- 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).