How to Paste in a Filtered Column Skipping the Hidden Cells

by

Copying and pasting are probably one of the most used actions in an Excel spreadsheet.

But when it comes to filtered data, copy-pasting data is not always smooth.

Ever tried pasting something to a table that has been filtered? It’s not as easy as it sounds.

In this tutorial, I will show you how to copy data from a filtered dataset and how to paste in a filtered column while skipping the hidden cells.

Copying from a Filtered Column Skipping the Hidden Cells

Suppose you have the below dataset:

Dataset to copy and paste in filtered rows

Given the above table, say you want to copy all the rows of employees from the IT department only.

For this, you can apply a filter to your table as follows:

  1. Select the entire table.
  2. From the Data tab, select the ‘Filter’ button under the ‘Sort & Filter’ group.
  3. You will notice small arrows on every cell of the header row. These are meant to help you filter your cells. You can click on any arrow to choose a filter for the corresponding column.
  4. In this example, we want to filter out only the rows that contain the Department “IT”. So, select the arrow next to the Department header and uncheck the boxes next to all the departments, except “IT”. You can simply uncheck “Select All” to quickly uncheck everything and then just select “IT”.
  5. Click OK. You will now see only the rows with Department “IT”.

All the rows with IT are filtered

Now, copying from a filtered table is quite straightforward. When you copy from a filtered column or table, Excel automatically copies only the visible rows.

So, all you need to do is:

  1. Select the visible rows that you want to copy.
  2. Press CTRL+C or right-click->Copy to copy these selected rows.
  3. Select the first cell where you want to paste the copied cells.
  4. Press CTRL+V or right-click->Paste to paste the cells.

This should cause only the visible rows from the filtered table to get pasted.

Occasionally you might come across issues in copying the visible rows, especially when working with Subtotals or similar features.

In such cases, copying only the visible rows is quite easy too. Here’s what you need to do:

  1. Select the visible rows that you want to copy.
  2. Press ALT+; (ALT key and semicolon key together). If you’re on a Mac, press Cmd+Shift+Z. This shortcut lets you select only the visible rows, while skipping the hidden cells.
  3. Press CTRL+C or right-click->Copy to copy these selected rows.
  4. Select the first cell where you want to paste the copied cells.
  5. Press CTRL+V or right-click->Paste to paste the cells.

So you see copying from filtered columns is quite straightforward.

But you can’t say the same when it comes to pasting to a filtered column.

Pasting a Single Cell Value to All the Visible Rows of a Filtered Column

When it comes to pasting to a filtered column, there may be two cases:

  • You might want to paste a single value to all the visible cells of the filtered column.
  • You might want to paste a set of values to visible cells of the filtered column.

For the first case, pasting into a filtered column is quite easy.

Let’s say we want to replace all the cells that have Department = “IT” with the full form: “Information Technology”.

For this, you can type the word “Information Technology” in any blank cell, copy it, and paste it to the visible cells of the filtered “Department” Column. Here’s a step-by-step on how to do this:

  1. Select a blank cell and type the words “Information Technology”.
  2. Copy it by press CTRL+C or Right click->Copy.
  3. Select all the visible cells in the column with the “Department” header.
  4. Paste the copied value by pressing CTRL+V or Right click->Paste.

You will find the value “Information Technology” pasted to only the visible cells of the column “Department”.

To verify this, remove the filter by selecting the Data->Filter. Notice that all the other cells of the “Department” column remain unchanged.

Copy and Paste Information Technology in all filtered rows

Two Ways to Paste a Set of Values to Visible Rows of a Filtered Column

Now let’s see what happens when you want to paste a set of values to the visible cells of a filtered column. Say you want to paste a list of salaries for only the rows containing Department=”Information Technology”.

If you try copying these cells and pasting them to the filtered Salary column, you will probably get an error message like “The command cannot be used on multiple selections”.

The command cannot be used on multiple selections prompt

This is because you cannot paste to cells in a range that contains hidden rows or columns. It’s one of Excel’s limitations. There’s no way around that, but there are some tricks that you can use to get this done.

Here are two tricks that you can use to paste a set of values to a filtered column, skipping the hidden cells.

Pasting a Set of Values to Visible Rows of a Filtered Column – Using a Formula

In this method, we use a formula to simply copy the value of the cell to the destination cell.

For the above example (where you want to copy a set of salary values to only the rows of with Department= “Information Technology”), here are the steps you need to follow:

  1. Press the equal sign (‘=’) in the first cell of the column you want to paste to (G3).
  2. Now select the first cell from the list you want to copy (H3 in our example).
  3. This will just create a reference to the cell. You should see the formula: =H3 in cell G3.Enter the first cell reference
  4. Copy this formula down by dragging down the fill handle (at the bottom right corner of cell G3). This should paste the formula only to the visible cells of column G.
  5. To verify this, remove the filter by selecting Data->Filters. Here’s an image of column G without filters after the copy-paste operation. To make it clearer for you to see, I’ve highlighted the copied cells in light green.Formula result is visible in earlier filtered rows only
  6. Now what you copied were just references to the original cells. So if you try to remove the original cells once you’re done copy-pasting, the copied values will disappear from column G too.
  7. To avoid this, you need to paste these formula results as values. This is quite easy. While you’re in the unfiltered mode, copy all the cells of column G, right-click and select ‘Paste Values’ from the popup menu.Paste as Values
  8. That’s it, you can now go ahead and delete the original values.

Pasting a Set of Values to Visible Rows of a Filtered Column – Using VBScript

This is a fairly easier and quicker method. All you need to do is copy the VBScript given below into your developer window and run it.

Sub paste_to_filtered_col()
Dim s As Range
Dim visible_source_cells As Range
Dim destination_cells As Range
Dim source_cell As Range
Dim dest_cell As Range
Set s = Application.Selection
s.SpecialCells(xlCellTypeVisible).Select
Set visible_source_cells = Application.Selection
Set destination_cells = Application.InputBox("Please select the destination cells:", Type:=8)
For Each source_cell In visible_source_cells
source_cell.Copy
For Each dest_cell In destination_cells
If dest_cell.EntireRow.RowHeight <> 0 Then
dest_cell.PasteSpecial
Set destination_cells = dest_cell.Offset(1).Resize(destination_cells.Rows.Count)
Exit For
End If
Next dest_cell
Next source_cell
End Sub

Follow these steps to use the above code:

  1. Select all the rows you need to filter (including the column headers).
  2. From the Developer Menu Ribbon, select Visual Basic.
  3. Once your VBA window opens, Click Insert->Module and paste the above code in the module window.

Your macro is now ready to run. To run the code:

  1. First, select the cells that you want to copy.
  2. Run the script by navigating to Developer->Macros-> paste_to_filtered_col
  3. The code will ask you to select your destination cells (where you want to paste the copied cells).
  4. Select the cells and click OK.Select the range

Your selected cells will now be copied and pasted to the destination cells. You can go ahead and delete the original cells if you want.

Pasted using Macros

You can also create a small shortcut (using the Quick Access Toolbar) to run your macro whenever you need it. Here’s how:

  1. Click the Customize Toolbar arrow, which you’ll find above Excel’s menu ribbon.
  2. Select ‘More Commands’ from the dropdown menu that appears.
  3. This will open the ‘Excel Options’ dialog box.
  4. Click on the drop-down list below ‘Choose Commands From’ and select ‘Macros’.
  5. Select the name of the macro you created. In our case, it’s ‘ThisWorkbook.paste_to_filtered_col’ Click on the ‘Add>>’ button.
  6. Click OK.

You’ll now get a Quick Access macro button to quickly run your macro with a single click.

Macro in Quick Access Toolbar

Whenever you need to copy and paste a set of cells to a filtered column, just select your source cells, click on this created button and then select the destination cells.

When you click OK, you should get your source cells copied into your selected destination cells.

So this is how you can copy and paste in a filtered column while skipping the hidden rows.

I hope you found this tutorial useful!

Other Excel tutorials you may like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment