When you apply filters in Excel 2007, and then copy the filtered data from one sheet to another, you will find (to your frustration) that what has been copied to the new sheet is not just the filtered data, but also the invisible data that was filtered out. Oh, the ways of Microsoft! If I wanted to copy all the data, I would do it without using the filter. When I apply a filter, I am only interested in the resultant subset. And to think that this wasn’t a problem in previous versions of Excel baffles me even more.
Help is at hand, though. If you want to copy only the filtered result set, select it, then hit F5 to open the “Go To…” dialog. In this dialog box, click on the “Special…” button, and select the “Visible cells only” option. Now when you copy, only the visible cells are copied to the clipboard. And solves the problem!
No related posts.
Tags: Excel 2007, workaround
Syju on 
Oh.
Ok.
Thank you for posting this workaround – I can’t believe MS bungled such an obvious and common action. I thought I was going crazy when I couldn’t get it to work like it has in 2003, 2000, 98…
Even the paste special function is not reliable. In many of the cases, it does not work. Pl. help with some other advice.
Phew!!.. Thanx a lot mate..
But sometimes I have noticed, the copy-paste on filtered data copies only the visible cells even without doing this workaround. Dont know when it works though
This is a great fix, however if you have a complicated spreadsheet and there are merged cells, excel wont let you copy and paste anyway.
And it did not help (((( I still get the whole thing copied…..
Emm… was just shown the correct way – I did not select the area I wanted to copy before doing this F5 thing… Ok, now it works )