How to copy filtered data and paste to filtered cells in MS Excel

Printer-friendly versionPrinter-friendly versionSend by emailSend by emailPDF versionPDF version
The Excel Filter

Filters and filtering are great tools when working with huge data in Microsoft Excel. However, have you tried copying and pasting filtered cells to another filtered cell range?

If you have already tried it, then you might notice that the common or conventional method of Ctrl+C and Ctrl+V key combination is not working because the copied data are pasted to hidden or filtered-out cells.

Here is a sample filtered data:

Sample filtered data

In the sample above, column B is filtered to YES. This is manifested by the row headers skipping numbers (1, 2...6..8, etc) and the active filter icon in cell B1.

Now, suppose we select the YES contents in column B, copy it using Ctrl+C and paste the copied cells in the next column, here is what happens:

Sample copy and paste of filtered cells

As you can see, there were 11 cells containing YES being copied, but when pasted, only 6 YESes appeared. The reason for this is because the other cells are pasted in the hidden or "filtered out" cells, as shown below:

Filtered data are continuous when pasted

This is a problem when working with huge data since the pasting produces an incorrect result. If left unnoticed, it will be very very hard to trace back the error. And headache soon follows.

This is an unacceptable behavior of MS Excel but perhaps Microsoft has a logical reason behind this. The only way to easily COPY and PASTE filtered data to filtered cell range is by dragging the Fill Handle.

What is a Fill Handle?

A fill handle is the lower right-hand corner of a selected cell that resembles like a + sign. Here is a screen shot:

The fill handle of active cell

In order to easily copy the sample filtered data above to adjacent cells, without using the Ctrl+C and Ctrl+V key combinations, you simply need to select the cells to copy, click and drag the fill handle of the selection which will appear in the bottom cell of the selection, then drag it to the next column. When the mouse is released, the filtered cells' data are copied correctly as shown below:

Copying filtered data to filtered cells by dragging the fill handle

When the mouse is released, here is how it looks:

Perfect copying of filtered data to filtered cells in Excel

When you remove the filters in column B to display all contents, here is how it looks:

Perfect copying of filtered cells in Excel

IMPORTANT NOTES

  • This procedure is straightforward but since this involves "dragging and dropping by the fill handle", this will only work when copying and pasting to adjacent columns. If you intend to copy the filtered data to a non-adjacent column, you need to HIDE the columns in between so that you can perform the drag and drop.
  • Sometimes, when you filter out data and want to just copy the data as a continuous column, that is, when you want to paste them in a non-filtered range, then use the Ctrl+C and Ctrl+V key combination.