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?
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.
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 Filtered List Pasting Problems page on my Contextures website.
The zipped sample file is in xlsx format, and does not contain any macros.
____________
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.:)
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.
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!
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.
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
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.
If you paste from the Clipboard, it also pastes into the hidden rows.
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.