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. The full transcript is available below the video. Video Timeline
Video TranscriptIf you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript Video TranscriptHere is the full transcript of the video above -- Use Shortcut to Paste in Excel Filtered List - From Same Rows. IntroductionIn my previous video, I showed you the problems you can run into when you try and copy and paste in a filtered list. For example, if I filter this list to only show the chocolate chips, and then try to copy these dates, and paste them into this column, it doesn't paste correctly. So I'm going to do a copy (we can see they're copied here) and then paste, and only two of them show up. The rest have been pasted in the background, in the hidden rows. So I'm going to undo that, and I'm going to show you a tip that I learned from my friend, Khushnood Viccaji, and it makes copying and pasting much easier. Select Visible CellsFirst, I'm going to select the cells that I want to copy. Then press Ctrl and select the cells where you want to paste the data. To get this information from here to here, we're going to make sure only the visible cells are selected. So press the Alt key and semi-colon(;) And now only the visible cells in our selection are selected, and you can see little lines to separate them. Copy to the RightNow to get from here to here, we're going to fill, and we can fill to the right. So I'm going to use the keyboard shortcut, which is Ctrl + R and it takes this data, and puts it in the cell to the right. Even though it wasn't to the immediate right, that shortcut works well for getting information to the next cell that's selected on the right. So none of the other data was overwritten. We can see that all the data went into the correct cells. Now in this case, we were filling to the right, so we could use a keyboard shortcut. Copy to the LeftBut if these dates weren't filled in and we wanted to copy from here to the left, there's no shortcut for that. So I'm going to select these cells first, and then the cells where I want to paste. Again, to select just the visible cells (press) Alt + Semi-colon; go to the HOME tab, and here's the fill. If I click that I can fill left, and now the dates from this cell are filled into the selected cells at the left. Get the WorkbookFor more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website at www.contextures.com |
Solution: Paste Into Same Row - ShortcutHere are the steps for copying values from one column to another, in the same row.
Note: Thanks to Khushnood Viccaji (LinkedIn profile), who shared this tip. 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: November 24, 2023 9:50 AM