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.
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 )
Hi Thank u so much for your suggestion
Hey guys.
This will definitely work, but to make it simpler, before applying the filter, select ALL the data you want and then filter. If you do it this way, there is no need to go the F5 route, since it will copy only the visible cells. So select the entire data range, (NOT just the headers) then apply the filter and you won’t need additional steps to copy filtered data.
Cheers!
Hi,
Thanks .Your solution is worked.Thanks a lot for suggest.
This did not work for me. Even when I selected the data before hitting F5. Just when I thought I had it, Excel tells me I cant change part of a merged cell or multiple selections.
This did not work for me. I have 41,324 data. If I start to copy the filtered data using your method, it prompts, “MS office excel cannot create or use the data range reference because its too complex…”
Please help me. Thanks!
First you filter you data.
Then you make a selection of what you want to copy.
Then press ALT ;
That is you press the ALT key and semicolon simultaneously.
Then Ctrl C and then paste the required cells at required location .
It did work for me.
Thanks works excel 2010
This works for me also in Excel 2010. None of the others did. Thanks so much,
THANK U SOOOOO MUCH
Thank you !!!!!!!!!!
Thanks F5 + Special Option worked for me……
Thank you so much, as I wasted a few hours on this ‘new issue’ when working on an production report. Even though I still got the error when copying the filtered data, it did only paste the subset, rather than the entire population.