Problem Pasting in Excel Filtered List

It looks like a simple task – filter a list, so you an see the items you’re interested in. Then, copy data from one column, and paste it into another. But you can have a serious problem pasting in Excel filtered list.

Unfortunately, it can quickly turn into a big mess, and you’ll end up overwriting some of your data.

In the screen shot below, I selected 5 cells in column B, and pasted them into the same rows in column E. However, only 2 of the dates were pasted.

problem pasting in filtered list

Find the Missing Data

What happened to the other 3 dates that I pasted? When I cleared the filter on the list, I could see that all 5 dates were there. They weren’t pasted into the visible rows though – they were pasted into a block of 5 cells, starting with cell E6, where I pasted the data.

In those 5 cells, only two were chocolate chip orders, so those are the only two that I saw, when the list was filtered.

Yikes! I hit the Undo button, immediately, to get the original data back.

Do you have the same problem pasting in Excel filtered lists?

filtercopypaste03

Why does Excel copy the individual, visible cells in a filtered list, then crush our dreams, by pasting them into a contiguous block?

I’d like the option to paste into visible rows, or have that automatically happen, if the list is filtered. Please and thanks!

Workarounds for Pasting

Even though I’ve asked nicely, Microsoft probably won’t be able to fix this problem pasting in excel filtered list by tomorrow. Or ever.

So, to avoid overwriting your data, be sure that you’re not pasting anywhere that rows are hidden. Here are a couple of workarounds that I use:

  • You could clear any filters, and sort by a column that will bring the rows of interest together. Then do the copying and pasting, while no rows are hidden.
  • To copy the selected items into the same rows, in a nearby column, use the Fill Right or Fill Left shortcut. I’ve made a video that shows how this works. Thanks to Khushnood Viccaji for sharing that tip in the comments below.
  • For filters that are more complex, where you can’t do a simple filter to bring the rows together, you could insert a temporary column, and mark the rows, as shown in the screen shot below. Then, clear the filters, sort the temporary column, and copy and paste in the marked rows.
  • The Paste Buddy add-in, from Excel Campus, has a Paste Visible feature that lets you paste the copied data into the visible cells only.

problem pasting in excel filtered list

Video: Problem Pasting in Excel Filtered List

Watch this video to see why you have a problem pasting in Excel filtered list, and a couple of workarounds, to help you avoid the problem.

Download the Sample File

To download the sample file for this video, please visit the AutoFilter Tips page on my Contextures website. The file is in xlsx format, and does not contain macros.

____________

Save

You may also like...

52 Responses

  1. Khushnood Viccaji says:

    I work around this limitation by doing a Fill> Left or Fill> Right.

    This is done by :
    (a) selecting the two sets of cells using the Ctrl key and the mouse — or the F8 key to extend selection.
    (b) selecting visible cells — press “Alt ;”
    (c) Fill> Right or Fill> Left depending on which are the source / destination cells.

    • Jubin Jacob says:

      Hi, Can you please explain the above with an example and send it to mail id [deleted]. I am currently using excel 2007

      Thanks

    • Evord says:

      If the data you want to paste is from another workbook or sheet, the Fill left or Right wont work

  2. Nirajkumar says:

    This is very helpful, it looks easy but its a life saver :)

  3. rahul says:

    Hi This fix should be deployed in Excel 2013.

  4. Debbie says:

    Thank you for the “x” column option. Saved me from throwing the PC in the bin!

  5. K1203 says:

    OMG life saver!!! thanks

  6. Qfay says:

    The “X” option, thank you so much, this issue has been driving mad for ages could not find a work around it.

  7. Jane says:

    Thanks, Very useful!

  8. Albert says:

    This is to: Khushnood Viccaji

    You are amazing.

    Thank you!!

  9. Mbhoni Baloyi says:

    thanks a lot

  10. Mbhoni Baloyi says:

    How do i create an excel setup project?

  11. Afzal says:

    Dear Khshnood

    Can you please expalin wihh example? I shall be thankful to you
    Regards

    Afzal

  12. jitendra sachdeva says:

    really very useful. thanks

  13. S4N0KUN says:

    I find another way;
    after you filtering the data; just put into the destination row(the row that you want to copy the filtered data) ‘= column name’ than drag/copy down to the last row of the destination row; here you’ll get the same value of the row you want copy in filtered mode ; when done just do ‘copy value’ to clear the function ‘=’ or equation.

    Let me try explain using example above :

    1. We want to fill the blank column ship date for chocolate chip — this make the destination cell copy
    2. We filtered the column ‘shipdate’, uncheck all except for blank(you can view the blank checklist on the very bottom of the filtered list)
    3. After the data is filtered, start typing ‘=’ on the first column of shipdate, then move your cursor to column Date then press enter, this will make the blank column on chocolate chip will be the same as column Date
    4. Still in filtered mode, put your cursor on edge of that column( previosly blank and become the same as value on column date) (note :the cursor arrow then change to ‘+’), click and drag down
    5. When you done, block that range, or simply do ‘ctrl + c’ then ‘paste value'(ctrl + v, press ctl one time, then press ‘v’)

    I hope this help
    (by the way I was applying this trick on excel 2010 in windows, i never try in other version or system. Feel free to correct me if its not working in other version)

  14. Virendra Yadav says:

    No use of this, why people copy past the same thing in same sheet.

    Resole that how to past from other sheet in filtered data.

  15. Snow says:

    S4N0KUN – Thank you! Worked for me

  16. Radu says:

    The real chalenge is to paste in filtered cells from another filtered sheet

  17. Brenda says:

    Alt;
    Thanks for this Khushnood! I was just copying one cell to many, so I can copy one cell, select where to paste, Alt;, then paste. Simple workaround for copying one cell into several and I see how your paste left, right, up or down works too.

  18. moschg says:

    great Khushnood Viccaji!
    Used your method, worked like a charm :)
    the X method would be useful too!
    thanks guys!

    moschg

  19. CHRIS says:

    I COPIED A LIST WITH NO FILTER, THEN I WANT TO PASTE IT TO EXCEL WITH FILTER. HOW TO PASTE TO VISIBLE CELLS ONLY?

  20. Nosheen says:

    Thanks a million! I have looked forever to find this!

  21. Martin says:

    Hi

    I use Go to Special – Visble cells only. Then copy the data to that selction.

    Cheers!

  22. Jon says:

    Your sorting idea worked great! Thank you!

  23. JAYAKRISHNAN says:

    This is very helpful,reduced my work load thank you

  24. Nadine says:

    this works well. Is there also a way to ‘paste values’ only using this technique?

  25. saskia says:

    dear debra

    thank you for all the tips

    but I think you should write a warning concerning the sort thing, because if the cells contain formulas, and we sort, we will be in much trouble,
    one must pay attention to the sort,

    thanks,

  26. Evord says:

    Nice, saved my time

  27. TEJAS SHAH says:

    hi to all
    solution is here
    copy filter field from your workbook of office 2007 or 2013

    open new workbook in excel, 2003 of office 2003

    right clicl – paste special – paste – XML spreadsheet –

    U WILL FIND YOUR RESULT.

    TEJAS SHAH
    09712772601

  28. Eddy Bonner says:

    Simplest way (IMO):
    Filter the list you want to paste from. In the column you want to paste to click in the top cell and type the formula “=A2” (assuming the column you want to paste from is “A” and the first row (1) is a header). Right click in the cell where you just typed the formula and select “Copy”. Highlight the remaining cells in the column you want to paste to (the ones below). Paste the formula into those cells (he formula will only be pasted into visible cells). Now CLEAR the FILTER. You will see the contents of the cells match those from the source columns, however the cells contain formulas, not values. Depending on your purpose this may be sufficient. If, however, you need the cells to contain the actual values from the source column take this one last step. Highlight all the cells in the destination column (the one with the formulas you just created). Right click the top cell, select “paste special”, select “paste values”, and click “OK”.

  29. Kevan says:

    Great solution Eddy, thanks!

  30. Brenda Dolieslager says:

    Hi all – I have a question about copying and pasting filtered data. I have recorded a macro to filter a table with 7 columns (A7:G38) on Column B (which hides cells that do not have relevant content), then I sort on Column C (ordering figures from largest to smallest). NOTE: depending on content in other parts of the workbook, the rows between A7:G38 that have relevant content in this table are not always the same, sometimes for example row 77 may have irrelevant content and be hidden by the filter function, and sometimes it may have relevant content and appear as visible content after the filter. I then used the mouse to select and copy (as visible cells) the top 5 cells in each of Columns B, C and G, copying B and C first and pasting these into B7:B11 and C7:C11 on a different worksheet, copying G last, and pasting this into D7:D11 on the destination worksheet. I realised, however, that the recorded macro refers to the original row number of the data (e.g., B77:C77) when it copies. This means that if next time I run this macro the top 5 visible rows are instead made up of different original row numbers, the macro will still copy B77:C77 rather than the top 5 visible rows. I cant find a solution that I can understand and/or make work in all my research on this issue and wonder if someone could give me some help as to how I need to tell the macro to copy the first 5 visible rows, irrespective of the original row numbers attached to this data? I would really appreciate your help!

  31. Pritesh Shukla says:

    Hi Khushnood Viccaji/ Afzal / Debra,
    Can you please tell is this method work for two different Workbooks/Sheets.?
    If yes ? then How?
    If No ? Then any alternate method to copy paste the filtered data between two different excel workbook??
    Waiting for you genius and intelligent tip.:)

  32. Brian says:

    What if you want to paste data that is longer than the existing filtered rows? i.e. – you not only want to overwrite the cells showing, but add new data below that in blank cells at bottom? Just sorting your data won’t work in that case because you’ll go down into other cells that you still don’t to replace.

    • @Brian, instead of using X marks in the filter column, fill the whole column with zeros first, before you filter.
      Then, apply your filters, and in the filtered rows, change the zero to 1.
      Remove the filter, sort by the column with 0s and 1s, and all the 1s will be at the bottom.

  33. B Tur says:

    Lets say you are trying to copy data from one spreadsheet, to another spreadsheet that has a filter already in place. How do you copy the data from the first sheet, and then paste it into the spreadsheet with the filter in place? If anyone knows thanks so much!

    • B Tur says:

      And I should’ve added, I need the data to paste the data from the first spreadsheet, to only the visible cells of the second spreadsheet that has the filter in place.

  34. Ron says:

    Please share the ticket at the Microsoft support – this behavior is highly unintuitive and destroying data! Would like to increase the importance to fix this

  35. Frankie Henderson says:

    Why don’t you just paste from Clipboard? that captures just the data that is showing, and pastes as it was shown, without the hidden rows.

  36. M Muralidhar says:

    We have two excel files. 1st file covers all branches information 2nd file has few branches but which are covered in 1st file. Both files have same information in columns. The question is can we paste all the data in 2nd file and paste in 1st file against same branches of 2nd file.

  37. Abdul says:

    Steps to easily paste on visible cells only in excel without any macro or additional software is as follows:
    Suppose original data is:
    A
    E 200
    G
    N 400
    K

    You have hidden two rows for E and N, now the visible data is:
    A
    G
    K

    You want to paste 100 for A, 300 for G and 500 for K to get the following results:
    A 100
    E 200
    G 300
    N 400
    K 500

    The procedure to get this result easily is given below:

    1. Copy the visible cells from the excel sheet

    2. Paste the copied cells in a new excel sheet. You will get:
    A
    G
    K

    3. Paste the data you want to paste, in new excel sheet. You will get
    A 100
    G 300
    K 500

    4. Use VLOOKUP formula in your original excel file on visible cells, with reference to data in new excel file. Then remove the filter from original file to see all hidden cells. You will get the following result:
    A 100
    E 200
    G 300
    N 400
    K 500

  38. Mike J says:

    My suggestion:

    Filter the range as required.
    Select the first cell in the column and type the value required.
    Select the remaining visible cells.
    Press Ctrl + “, then Ctrl+Enter. Job done.

    Ctrl+” means copy the cell above
    Ctrl+Enter means do this for all selected cells.
    The operation will work only on visible cells.

Leave a Reply to Brian Cancel reply

Your email address will not be published. Required fields are marked *