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: ,

29 Comments on Copying filtered data in Excel 2007

  1. Always Thinkin says:

    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…

  2. Rahul Roy says:

    Even the paste special function is not reliable. In many of the cases, it does not work. Pl. help with some other advice.

  3. Arjun Rajiven says:

    Phew!!.. Thanx a lot mate.. :D

  4. Selva says:

    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 :D

  5. Chani says:

    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.

  6. November7 says:

    And it did not help (((( I still get the whole thing copied…..

    • November7 says:

      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 )

  7. Garca says:

    Hi Thank u so much for your suggestion

  8. Renzo says:

    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!

  9. dhruti says:

    Hi,

    Thanks .Your solution is worked.Thanks a lot for suggest.

  10. Chani says:

    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.

  11. elizabeth says:

    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!

  12. FIZA says:

    THANK U SOOOOO MUCH

  13. shree says:

    Thank you !!!!!!!!!!

  14. Hiren Rughani says:

    Thanks F5 + Special Option worked for me……

  15. Melissa says:

    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.

  16. Nilesh Sachdev says:

    Thanks for the great posting. it worked…. keep posting the good work….

  17. kristel balita says:

    thanks for the info! really a great help :) )

  18. ad says:

    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?

  19. SORAV KUMAR says:

    THANKS A LOT FOR THE SOLUTION

  20. Gilbert says:

    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.

  21. matthew says:

    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

  22. T says:

    A certain anomaly in what you’ve dubbed a useless repository. Thanks.

Leave a Reply

*