Problem Pasting in Excel Filtered List

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.

Problem Pasting in Excel Filtered List

Pasting data into a filtered list 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.

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, go to the AutoFilter Tips page on my Contextures website. The file is in xlsx format, and does not contain macros.
____________

54 thoughts on “Problem Pasting in Excel Filtered List”

  1. 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.

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

  2. 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)

  3. 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.

  4. 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.

  5. great Khushnood Viccaji!
    Used your method, worked like a charm 🙂
    the X method would be useful too!
    thanks guys!
    moschg

  6. 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,

  7. 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

  8. 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".

  9. 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!

  10. 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.:)

  11. 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.

    1. @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.

  12. 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!

    1. 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.

  13. 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

  14. 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.

  15. 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.

  16. 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

  17. 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.

  18. Your workarounds are definitely good ideas I will use to copy and paste data in a file that has approx. 200'000 rows of data.
    Thanks for sharing, very useful!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.