Excel has a handy feature that allows you to automatically add subtotals to a data list. (How to create subtotals is covered in other issues of ExcelTips.) You may be wondering, once the subtotals are in place, how you can copy the subtotals to a different worksheet. This is actually rather easy to do, if you follow these steps:
- Add your worksheet subtotals as you normally would.
- Collapse the information in the list so that only the subtotals are showing. (Click on the small 2 in the outline levels shown at the top of the gray area at the left of the worksheet.)
- Select the range of cells containing the subtotals you want to copy. (Do not select complete rows or columns; just select the range of cells.)
- Press F5. Excel displays the Go To dialog box.
- Click on Special. Excel displays the Go To Special dialog box. (See Figure 1.)
- Make sure the Visible Cells Only option is selected.
- Click on OK. Excel selects just the visible information from the range you specified in step 3.
- Press Ctrl+C to copy the rows to the Clipboard.
- Select the cell where you want to paste the subtotals.
- Press Ctrl+V to paste the information.
Figure 1. The Go To Special dialog box.