Problem: Your manager wants just the total rows sent to him in a file. You’ve added subtotals and then chosen the #2 Group & Outline button to see just the data that you want, as shown in Fig. 676.
Strategy: There is an obscure command on the Go To Special dialog box to assist with this task. Follow these steps.
Additional Details: The process of selecting Go To – Special – Visible Cells Only can be reduced to holding down Alt while hitting EGSY and then Enter. Alt+E opens the Edit menu. Alt+G selects Go To. Alt+S presses the Special button. Alt+Y selects Visible Cells Only (note that the “y” is underlined in the GoTo Special dialog in Fig. 679). Enter selects the default OK button.
Summary: This is a fairly obscure trick. I think it is more obscure because no one in his or her right mind uses the Go To dialog. If you are at cell A10 and need to go to cell A100, it is pretty easy to just hit PgDn a few times. Considering how seemingly useless the Go To dialog is, the fact that it houses the Special button is ironic.
Commands Discussed: Edit – Go To – Special – Visible Cells Only
1) Choose the #2 Group & Outline button to put the data in subtotal view.
2) Select everything from the headings to the grand total by selecting one cell with data and hitting Ctrl+* (you can use the * key on the numeric keypad).
3) Bring up the Go To dialog by choosing Edit – Go To or by hitting the F5 key on the keyboard. As shown in Fig. 678, choose the Special button in the lower left corner of the Go To dialog.
4) In the Go To Special dialog, select Visible Cells Only and choose OK, as shown in Fig. 679.
The change will be almost imperceptible and may not even be noticeable in the printed resolution of this book. Amongst the blue highlighting, you will see fine white lines above and below each subtotal, as shown in Fig. 680. This is Excel’s way of saying, “Hey – there are hidden rows back there which are not part of the selection.”
5) Next, copy with Ctrl+C. Switch to a new workbook. Paste with Ctrl+V. You will have just the subtotal rows, as shown in Fig. 681.
You might think that you would have to do a Paste Special – Values instead of just a paste. However, the Paste command works OK. As you can see in the formula bar above, the SUBTOTAL function, which used to be in the cell, is converted to a value.
However, when you copy this view and paste to a new workbook, all of the detail rows come along as well, as shown in Fig. 677.
Images
