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.
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 Select Visible Cells tool in Excel 2003
1) Right-click anywhere on your toolbars and select Customize;
Now that you have only the visible cells selected, you can simply copy the data elsewhere so that you can work with it.