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?
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!