How To Copy And Paste Filtered Data In Excel 2016

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

Just when it comes to filtered data, re-create-pasting data is not ever shine.

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

In this tutorial, I will show yous 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:

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:

Select the entire table.
From the Data tab, select the 'Filter' button nether the 'Sort & Filter' grouping.
You will notice small arrows on every cell of the header row. These are meant to help y'all filter your cells. Y'all tin click on any arrow to choose a filter for the corresponding column.
In this example, we want to filter out only the rows that contain the Department "IT". And then, select the arrow next to the Department header and uncheck the boxes next to all the departments, except "It". You can only uncheck "Select All" to quickly uncheck everything and so just select "It".
Click OK. You will now see only the rows with Section "IT".

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

So, all you demand to do is:

Select the visible rows that you desire to re-create.
Printing CTRL+C or correct-click->Copy to copy these selected rows.
Select the first cell where you want to paste the copied cells.
Printing 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 yous 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 like shooting fish in a barrel likewise. Here'southward what y'all need to do:

Select the visible rows that yous want to copy.
Press ALT+; (ALT key and semicolon primal together). If you lot're on a Mac, press Cmd+Shift+Z. This shortcut lets you lot select but the visible rows, while skipping the subconscious cells.
Press CTRL+C or correct-click->Copy to copy these selected rows.
Select the first cell where you lot want to paste the copied cells.
Press CTRL+V or right-click->Paste to paste the cells.

So yous encounter copying from filtered columns is quite straightforward.

Merely yous tin can't say the same when it comes to pasting to a filtered column.

Pasting a Single Jail cell Value to All the Visible Rows of a Filtered Cavalcade

When information technology comes to pasting to a filtered column, at that place may exist two cases:

Y'all might want to paste a single value to all the visible cells of the filtered column.
Yous might want to paste a set of values to visible cells of the filtered cavalcade.

For the beginning example, pasting into a filtered cavalcade is quite easy.

Let'due south say we want to replace all the cells that have Section = "IT" with the total form: "Information Engineering".

For this, you tin can type the word "Information Engineering science" in any blank prison cell, re-create it, and paste it to the visible cells of the filtered "Section" Column. Here'south a pace-by-step on how to do this:

Select a blank jail cell and type the words "It".
Copy information technology past press CTRL+C or Right click->Copy.
Select all the visible cells in the cavalcade with the "Section" header.
Paste the copied value by pressing CTRL+V or Right click->Paste.

You volition find the value "Data Applied science" pasted to only the visible cells of the column "Department".

To verify this, remove the filter by selecting the Data->Filter. Find that all the other cells of the "Department" cavalcade remain unchanged.

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

Now let'southward run into what happens when you lot want to paste a set of values to the visible cells of a filtered column. Say y'all want to paste a listing of salaries for only the rows containing Section="Information technology".

If y'all try copying these cells and pasting them to the filtered Bacon column, you lot will probably become an error message similar "The command cannot be used on multiple selections".

This is because you cannot paste to cells in a range that contains hidden rows or columns. It's one of Excel's limitations. At that place'south no mode effectually that, but there are some tricks that you lot tin employ to go this done.

Here are 2 tricks that you tin 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 merely copy the value of the cell to the destination cell.

For the above case (where you want to re-create a gear up of salary values to just the rows of with Department= "Information Technology"), here are the steps you need to follow:

Press the equal sign ('=') in the outset cell of the column you want to paste to (G3).
Now select the start cell from the list yous want to copy (H3 in our example).
This will only create a reference to the cell. You should meet the formula: =H3 in prison cell G3.
Re-create this formula down by dragging downwards the fill up handle (at the bottom right corner of cell G3). This should paste the formula merely to the visible cells of column Thou.
To verify this, remove the filter by selecting Data->Filters. Here's an epitome of column G without filters after the copy-paste operation. To go far clearer for you lot to run across, I've highlighted the copied cells in light dark-green.
Now what you lot copied were just references to the original cells. So if you endeavour to remove the original cells once you're washed re-create-pasting, the copied values will disappear from column G too.
To avoid this, you need to paste these formula results as values. This is quite piece of cake. While you're in the unfiltered mode, copy all the cells of cavalcade G, right-click and select 'Paste Values' from the popup menu.
That's it, y'all tin now go ahead and delete the original values.

Pasting a Gear up 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 Equally Range Dim dest_cell Every bit Range Ready s = Application.Pick due south.SpecialCells(xlCellTypeVisible).Select Set visible_source_cells = Application.Selection Set destination_cells = Awarding.InputBox("Please select the destination cells:", Type:=eight) For Each source_cell In visible_source_cells source_cell.Copy For Each dest_cell In destination_cells If dest_cell.EntireRow.RowHeight <> 0 And so dest_cell.PasteSpecial Fix destination_cells = dest_cell.Beginning(1).Resize(destination_cells.Rows.Count) Leave For Stop If Next dest_cell Next source_cell End Sub

Follow these steps to use the higher up lawmaking:

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

Your macro is at present ready to run. To run the code:

Showtime, select the cells that you want to re-create.
Run the script by navigating to Developer->Macros-> paste_to_filtered_col
The code will ask y'all to select your destination cells (where yous want to paste the copied cells).
Select the cells and click OK.

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

Yous can also create a modest shortcut (using the Quick Access Toolbar) to run your macro whenever y'all demand it. Here's how:

Click the Customize Toolbar arrow, which y'all'll discover higher up Excel'southward menu ribbon.
Select 'More Commands' from the dropdown carte du jour that appears.
This will open up the 'Excel Options' dialog box.
Click on the drib-downwardly listing below 'Choose Commands From' and select 'Macros'.
Select the name of the macro you lot created. In our case, information technology's 'ThisWorkbook.paste_to_filtered_col' Click on the 'Add>>' button.
Click OK.

You lot'll now get a Quick Admission macro push button to apace run your macro with a single click.

Whenever you demand to copy and paste a set of cells to a filtered column, just select your source cells, click on this created button and 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 institute this tutorial useful!

Other Excel tutorials yous may like: