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
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,
Hiya….Thanks Swe……
Thx!It works for me!!
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.
Thanks for the great posting. it worked…. keep posting the good work….
thanks for the info! really a great help
)
Though, how may one copy filtered data and paste them to the same position e.g. when updating some data of a table? I’m asking that because as far as I know Excel by default pastes selected or filtered data in consecutive rows and not in the same position.
For example,
If there is a Table 1 as the following:
A B C D
1 junction vehicles passengers signals
2 i 500 2000 5
3 ii 200 700 2
and a Table 2 as the following:
A B C D
1 junction vehicles passengers signals
2 i 500 2000 5
3 ii 450 1500 3
and there is need to copy from Table 1 and paste to Table 2 only the data: 500, 700 and 2 into the same positions, i.e. into the cells B2, C3 and D3, how can that action be achieved within Excel?
THANKS A LOT FOR THE SOLUTION
I have noticed that when you RIGHT CLICK AND COPY, only the filtered region is copied.
This above works even where F5+Special doesnt work!
However, when the copy shortcut(Ctrl+C) is used, there entire unfiltered data range is copied.
I’m sorry but this absolutely doesn’t work for me. I have a filtered data set and used your “go to visible cells only” option and copied it. I’m still get all the unfiltered data. Waste of time
A certain anomaly in what you’ve dubbed a useless repository. Thanks.