1. Expanding and Collapsing Subtotals
When data is grouped and subtotaled, outline symbols appear to the left of the row headings, as shown in Figure 1. Clicking the numbered icons at the top (1,2,3 in Figure 1)
will hide and unhide the data in the sheet. For example, clicking the 2
will hide the data rows, showing only the Total and Grand Total rows.
Clicking the 1 will hide the Total rows, showing only the Grand Total.
Clicking the 3 will unhide all the rows.
Below the numbered icons, next to each
Total and Grand Total, are the expand (+) and collapse (-) icons. These
will expand or collapse the selected group.
2. Copying Subtotals
If you hide the data rows, copy the subtotals, and
paste them to another sheet, all the data, including the hidden data
rows, will appear in the new sheet. To copy and paste only the
subtotals, select only the visible cells.
Copying Only the Totals to a New Location
To copy only the totals to a new location, follow these steps:
1. | Click the outline icon so that only the rows to copy are visible.
|
2. | Select the entire dataset.
|
3. | Go to Home, Editing, Find & Select, Go to Special, and select Visible Cells Only, as shown in Figure 2. Note: the dashed lines in the figure are shown for emphasis only. They will appear in the next step.
|
4. | Select Home, Clipboard, Copy.
|
5. | Select the cell where the data is to be pasted.
|
6. | Select Home, Clipboard, Paste. The formulas will be converted to values automatically.
|
A shortcut for step 3 is to press ALT+; (semicolon).
