Filters > AutoFilter > Troubleshoot Filtered List Copy Paste ProblemsSee 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 ListThis 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.
Video Timeline
|
Problem Pasting in Filtered ListIt 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 PasteFor example, in the Sales Order table shown below:
Filter, Copy & PasteIt 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 DataWhat 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 RowIf 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 - ShortcutHere are the steps for copying values from one column to another, in the same row.
Copy to the RightTo copy data to the right, into the same rows in a filtered list follow the steps below. Step 1 - Select CellsA) 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 LeftTo copy data to the left, into the same rows in a filtered list follow the steps below:
|
Workarounds: Avoid Pasting in Filtered ListIf 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.
|
Get the Sample FileCopy 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. More Tutorials |
Last updated: August 22, 2022 2:43 PM