copy visible cells only

MS Office 2007/2010
When working with Subtotals and Filters in Excel, it can be frustrating when trying to copy this data to another location.  Excel copies all data.  It cannot differentiate the hidden cells.  Or can it?

1. Select the cells that you want to copy.

2. On the Home tab, in the Editing group, click Find & Select, and then click Go To.

3. In the Go To dialog box, click Special.
4. Under Select, click Visible cells only, and then click OK.
5. On the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut  You can also press CTRL+C.

6. Select the upper-left cell of the paste area.

 Tip   To move or copy a selection to a different worksheet or workbook, click another worksheet tab or switch to another workbook, and then select the upper-left cell of the paste area.

7. On the Home tab, in the Clipboard group, click Paste .

Keyboard shortcut  You can also press CTRL+V.

 Notes 

• Excel pastes the copied data into consecutive rows or columns. If the paste area contains hidden rows or columns, you might need to unhide the paste area to see all of the copied cells.
• If you click the arrow below Paste , you can choose from several paste options to apply to your selection.

END

 --------------------------------------------------------------------------------------------------------------------------
In this Excel tip, I show you how to copy visible cells only.
Let's say you have a spreadsheet that contains Subtotals, using the "Subtotal" command.
In the area on the left side of the screen, click on the "2" button to collapse all data so that the only visible cells are the Subtotal lines.
Next, press the "F5" key (the Go To Command).

In the "Go To" Command window, click on the "Special" button.
Select the "Visible cells only" option and click on the "OK" button.  See image above left.
Now when you copy, the only rows that are copied are the visible cells.
This procedure works in the same manner for filtered rows of data.
Use this function and you'll be able to copy visible cells only.  Show this tip to your friends and co-workers and you'll be a lifesaver!