See how to copy paste when filter is applied in Excel list. Use Excel shortcut to copy right or left. Workaround to copy paste multiple cells, avoid problem pasting in hidden cells
Video: Problem Pasting in Filtered List
This video shows the problems that you can have when you try to copy and paste data into a filtered list.
See why the problem occurs, and a couple of workarounds, to avoid the problem. There are written steps below the video.
Problem Pasting in Filtered List
It should be easy to filter a list in Excel, and then copy cells from one column, and paste into a different column. However, Excel pastes into the hidden rows too, and that can create serious problems in your workbook.
The steps and screen shots below show the problem you might have, when pasting into a filtered list.
Getting Ready to Copy and Paste
For example, in the Sales Order table shown below:
Filter, Copy & Paste
It seems simple enough - just filter the list, copy dates, then paste into the blank cells.
To do that, I tried the following steps -- but things did not go well!
However, there is a problem, as you can see in the screen shot below:
What happened to the other 4 dates?
Find Missing Data
What happened to the other 4 dates that I copied and pasted into column E?
To start troubleshooting the paste problem, I followed these steps:
Fortunately, I noticed the problem immediately, so I followed these steps to fix the mess:
Warning: Copy and Paste Limitation
Video: Copy Values to Same Filtered Row
If you are copying and pasting in the same rows in a filtered list, you can use Excel's built-in fill commands. With this technique, you can safely copy data from one column to another, without losing data in the hidden rows.
Watch this video to see the steps, and there are written instructions below the video.
Note: Thanks to Khushnood Viccaji (LinkedIn profile), who shared this tip.
Solution: Paste Into Same Row - Shortcut
Here are the steps for copying values from one column to another, in the same row.
Copy to the Right
To copy data to the right, into the same rows in a filtered list follow the steps below.
Step 1 - Select Cells
A) If your data to copy is in Column A:
B) If your data to copy is NOT in Column A:
Step 2: Complete the Copy and Paste
OR, Copy to the Left
To copy data to the left, into the same rows in a filtered list follow the steps below:
Workarounds: Avoid Pasting in Filtered List
If possible, use the Fill Right or Fill Left shortcut solution, shown above, to copy and paste data in a filtered list.
However, that technique will only work if you copy and paste values within the same row
If you are copying data from another location, and want to paste it into a filtered list, here are a few workaround options for dealing with this situation.
Copy Paste Problem: Download the Filtered List Copy Paste sample file used in the videos on this page. The zipped Excel file is in xlsx format, and does not contain macros.
Last updated: August 22, 2022 2:43 PM