Copying only Subtotals (Excel Training 2007/2010)



If you work with subtotals and collapse the outline, often you may want to copy just the subtotals to another sheet. Or if you have hidden rows in your data you may want to copy only the cells that are visible.

Select only visible subtotal cells


Fortunately, there are a couple of easy ways to do this.

The first option is by using a keyboard shortcut. With your data selected, press ALT+; (i.e. hold down the ALT key and press the semicolon key) to select only the visible cells;

If you prefer a ‘mouse-click’ method you can add the ‘Select Visible Cells’ command to your Quick Access Bar in Excel 2007 or your toolbars in Excel 2003.

If you’re using Excel 2007-2010…

1) Right-click anywhere on the Ribbon and select Customize Quick Access Toolbar;
2) From the ‘Choose commands from’ dropdown, select All Commands;
3) Scroll down the list of commands and click once on the Select Visible Cells command;
4) Click the Add>> button;
6) Click OK to close the Excel Options dialog and the Select Visible Cells button will be shown on your Quick Access Toolbar;

Add the Select Visible Cells tool to the Quick Access Toolbar


Add Select Visible Cells tool in Excel 2003

1) Right-click anywhere on your toolbars and select Customize;
2) Click the Commands tab in the Customize dialog;
3) In the Categories area, scroll down and select Edit;
4) In the Commands area click once on the Select Visible Cells tool;
5) Using the left mouse button, drag the Select Visible Cells tool up to your toolbars. When you see the insertion bar where you want to place the Select Visible Cells tool, release the mouse button.

Add Select Visible Cells to your toolbars


Now that you have only the visible cells selected, you can simply copy the data elsewhere so that you can work with it.

Select only visible subtotal cells