An Excel Tip That Will Save You From Hours of Headache - Copying Visible Cells Only

This is the most useful Excel trip I learned recently and it saved me many headaches while working. I am also very surprised at how few people know about this feature.

So often during our work, we filter our excel sheets to show only certain data we care about at the time. Often, I wanted to copy this data to another excel sheet. So I would select all the data I could see and hit copy. I would paste the item in the other sheet only to have Excel copy over the data that I thought I had filtered out in addition to the data I wanted. Ugh!

In order to fix this problem, complete the following steps when copying over filtered data.

1.  Select the data you want to copy.
2. Hit F5 or On the Home tab, in the Editing group, click Find & Select, and then click Go To.
3. Click the “Special” button in the lower left hand corner.
4. Click on the “Visible Cells Only” Circle box.
5. Hit OK.
6. Press Ctrl “C” or Edit Copy
7. Paste data into the appropriate sheet.

Only the visible data should have been copied over.

We can describe the feature with the following simple example.

In the example, Row 2 is hidden (as shown in Image 1)

                                    Image 1

When you copy and paste this range, the result will be shown as Image 2

                                    Image 2


To copy visible cells only, execute the following easy steps.

1. Select the range A1:A4.

2. On the Home tab, click Find & Select, Go To Special...

 

                             Image 3

3. Click Visible cells only and click OK.

 

                       Image 4

 

 Excel selects the visible cells.

4. Press Ctrl + C to copy the range.

5. Select cell A6 and press Ctrl + V to paste the range.

Result:

                                        Image 5

 By default, Excel copies both visible and hidden cells. However, it is possible to copy visible cells only as shown in the above Example.

Like it or not, most of your time as an accountant is spent in Excel. As such, it is best to learn as many excel tricks as you can in order to make your life easier.  Hopefully, you can implement the tip “Copying Visible Cells Only”  in your work going forward. If you have some of your own favourite Excel tips, I invite you to leave a comment below for others to reference.