Filters > AutoFilter > Troubleshoot

Filtered List Copy Paste Problems

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.

• Tip: To avoid this problem, when pasting values into the same filtered row, go to the Video: Copy Values Same Row, further down this page.

Video Timeline

• 00:00 Introduction
• 00:13 Filter a Sales List
• 00:48 Copy Visible Row Data
• 01:06 Paste in Filtered Rows
• 01:36 Check Incorrect Pasted Data
• 02:23 Workaround 1: Sort Data
• 02:57 Workaround 2: New Column
• 04:15 Conclusion

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:

• There are a few blank cells in the ShipDate column.
• Those products were sent out on the same day they were ordered
• I'll copy the column A dates into column E
• It's important that the existing shipping dates (highlighted in yellow) are not deleted or changed

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!

• ShipDate column is filtered, to show only the rows with blank cells in that column.
• With some of the rows hidden by the filter, I selected 6 cells in column B
• Then, I copied copied the selected cells, with the shortcut, Ctrl + C
• After that, I selected cell E6, and pasted there, with the shortcut, Ctrl + V

However, there is a problem, as you can see in the screen shot below:

• only 2 of the 6 dates appeared in column E
• one of the dates is in the wrong row - date from row 28 is in row 9

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:

• First, I cleared the filter on the ShipDate column
• With the filter removed, I could see that all 6 dates had been pasted into column E.
• Dates had been pasted into a continuous block of 6 cells
  • starting with cell E6
  • down to cell E11
• Some of the existing ShipDates had been overwritten!

Fortunately, I noticed the problem immediately, so I followed these steps to fix the mess:

• Press Ctrl + Z (twice) to undo the previous steps
• OR, use the Undo command on the Home tab of the Excel Ribbon.

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.

• Note: This technique will work, even if the columns are not adjacent to one another.

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:

• In the filtered list, select the cells where you want to paste
• Press the Ctrl key on your keyboard, and then select the cells that you want to copy (in the same rows)

B) If your data to copy is NOT in Column A:

• In the filtered list, select the cells that you want to copy
• Press the Ctrl key on your keyboard, and then select the cells where you want to paste (in the same rows)

Step 2: Complete the Copy and Paste

• Next, to select only the visible cells in the selection, press the keyboard shortcut:
  • Alt + ;     (Alt + semi-colon)
• Finally, to copy to the right, press the keyboard shortcut:
  • Ctrl + R

OR, Copy to the Left

To copy data to the left, into the same rows in a filtered list follow the steps below:

• In the filtered list, select the cells that you want to copy
• Press the Ctrl key on your keyboard, and then select the cells where you want to paste (in the same rows)
• Next, to select only the visible cells in the selection, press the keyboard shortcut:
  • Alt + ;     (Alt + semi-colon)
• Finally, to copy to the left, go to the Home tab on the Excel Ribbon
  • There isn't a built-in Excel keyboard shortcut to Fill Left
• At the right end of the Home tab, find the Editing group
• Click the Fill command, then click the Left command

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.

1. Fill Right or Left: If you're copying values within the same row, from one column to another, use the Fill Right or Fill Left technique, shown in the sections below.
2. Sort the Data: Instead of filtering, sort your data, to bring together the rows where you want to paste multiple cells. Then, copy your data from another location, and paste into the sorted (but not filtered) rows. There are sorting tips on the Sort Excel Data page.
3. Mark the Rows: 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 by the temporary "mark" column, and copy and paste in the marked rows

Get the Sample File

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.

More Tutorials

Copy Paste Error Problems

AutoFilter Basics

AutoFilter Tips & Fixes

AutoFilter Programming