Excel is an amazing spreadsheet tool, but there are some things that drive me crazy.
One such thing is when you need to copy and paste data into a column with filtered rows.
Excel would paste this data into the visible cells as well as the hidden cells that have been filtered out. In most cases, this is not what you want. In most cases, you want to paste your data only in the visible cells and not the hidden ones.
So how do we do this then?
In this article, I will show you a couple of workarounds for pasting into filtered columns in Excel while skipping the hidden cells.
Since there is no one solution that fits all, I will show you different scenarios and the most appropriate solution for each scenario (including a simple VBA code that works great).
Copy Paste One Single Cell Value in a Filtered Column
Let’s start with the most straightforward example.
Below, I have a dataset that I am going to use.
Now, I have filtered this data to only show the records for the Marketing department.
Now, I want to copy the value in cell G1 and paste the same in all the visible cells in column D (i.e., only the cells that are visible and not the ones that are hidden after filtering).
This is very easy, as you can simply copy the value in cell G1, then select all the cells in column D and paste it.
Here are the steps to do this:
- Copy cell G1.
- Select all the cells in column D in which you want to paste the value in G1.
- Use Control + V to paste the value. You can also right-click on any of the visible cells and then click on Paste Values.
When I copied cell G1 and then selected cells in a column that has a filter applied to it, and then pasted the cell, it was only pasted in the cells that were visible.
You can confirm this by removing the filter, and you will notice that the value in cell G1 has only been pasted in the cells that were visible when the filter was applied.
Also read: Copy Visible Cells Only in Excel
Copy Paste Cells from the Same Row in a Filtered Column
Let’s look at another situation (which is not as straightforward).
Below, I have a filtered data set, and I have some values in column G that I want to copy and paste into the visible cells in column D.
Now, you cannot do a simple copy and paste in this situation, as it won’t work.
If I copy the cells in column G, it is only going to copy the cells that are visible, but if I then paste it in the cells in column D, it is going to paste it in the visible cells as well as the ones that are not visible.
So if you try it, you may end up getting a result as shown below.
Also, if you think you can try selecting only the visible cells in column D (which you can do by using the shortcut ALT + semicolon) and then paste the values copied from column G, Excel will give you the following error.
Thankfully, when you have the values that you want to copy in the same row as your filter data set, you can use a very simple formula.
Here are the steps to do this:
- Select cell D6 (which is the first cell in our filtered data set in column D)
- Enter an equal to sign
- Select cell F6. This will make the reference in cell D6 as =F6.
- Press the Enter key
- Copy the formula in all the cells in the column. You can do a simple copy-paste or drag the fill handle down to apply the formula to the entire column.
- Once you have the values in column D, you can select all the cells again and then convert the formula into values. To do this, select the cells, then use Control + C to copy them, and then use Control + Shift + V to paste the formula as values.
The good thing here is that when you copy and paste a formula in all the cells in a filtered dataset, it is only copied to the visible cells.
While this method works great, it’s likely that you may not get the data you want to copy and paste into the filtered rows in the same rows.
What if the data you want to copy and paste into the filtered rows is in a different location in the same worksheet or in a completely different worksheet or workbook?
The next two methods would tackle that.
Also read: How to Copy and Paste Column in Excel?
Copy Paste Cells From Another Range Sheet or Workbook into a Filtered Column
Below, I have a filtered data set.
I also have some values on a different sheet that I want to copy and paste into column D in our filter dataset above.
No copy-paste trick would work in this situation, so I have to get a bit creative.
Now, there could be two situations.
- There is a common column in both datasets that I can use to fetch the value from the table in the worksheet to get the result in the filtered dataset. For example, in our case, I can use the Names column in both datasets to look up the value in the worksheet and fetch it in the filter data set.
- There is no common column, and I cannot use any lookup formulas
Let’s see what to do in both of these situations.
Using VLOOKUP (if there is a lookup Value)
Let’s first take an example where I have the names column in both datasets, and I can use a lookup function (VLOOKUP or XLOOKUP) to fetch the value for each name in the filtered dataset.
Below is the VLOOKUP formula that would work:
=VLOOKUP(A6,'Copy From'!$A$2:$B$8,2,0)
Enter this formula in cell D6 (or whatever is the first cell in the filtered column) and then copy it for all the other cells.
In case you prefer using XLOOKUP, you can use the below formula
=XLOOKUP(A6,'Copy From'!$A$2:$A$8,'Copy From'!$B$2:$B$8,"",0)
Once you have the formula results, you can convert these formulas into static values, and you’re done.
Also read: Copy and Paste Multiple Cells in Excel (Adjacent & Non-Adjacent)
Using VBA (works in all scenarios)
Now, let’s talk about the most complicated situation (which is also perhaps the most common one).
I have the filtered data set below.
I have these values in a different worksheet or workbook, and I want to copy and paste them into column D in the filtered column.
There is no common column, so I cannot use any lookup formulas.
In such a case, I will have to rely on the big guns – a VBA macro code.
Below is the VBA macro code that would do this for me:
Sub PasteintoFilteredColumn()
Dim visibleSourceCells As Range
Dim destinationCells As Range
Dim initialDestinationLastRow As Long
Dim sourceCell As Range
Dim destCell As Range
Set visibleSourceCells = Application.Selection.SpecialCells(xlCellTypeVisible)
Set destinationCells = Application.InputBox("Please select the destination cells:", Type:=8)
Application.ScreenUpdating = False
initialDestinationLastRow = destinationCells.Rows(destinationCells.Rows.Count).Row
For Each sourceCell In visibleSourceCells.Cells
For Each destCell In destinationCells.Cells
If destCell.EntireRow.Hidden = False Then
sourceCell.Copy
destCell.PasteSpecial Paste:=xlPasteValues
If destCell.Row < initialDestinationLastRow Then
Set destinationCells = destCell.Offset(1, 0).Resize(initialDestinationLastRow - destCell.Row)
End If
Exit For
End If
Next destCell
Next sourceCell
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Here are the steps to put the VBA code in the backend in Excel:
- Hold the ALT key and then press the F11 key. This will open the Visual Basic editor in the workbook. You can also do the same thing by clicking on the Developer tab and then clicking on the Visual Basic icon.
- In the VB Editor, click on the Insert option in the menu and then click on Module. This will insert a new module where we will copy and paste the above VBA macro code.
- Copy and paste the above VBA code into the Module code window.
- Click on the Save icon and close the VB editor.
Now that we have the VBA code in place, below are other steps to use it to copy data into the filtered data set while skipping the hidden cells:
- Select the cells that you want to copy and paste.
- Click the Developer tab and then click on the Macros icon. This will open the Macros dialog box.
- Select the PasteintoFilteredColumn macro and then click on Run.
- It will show you an input message box asking you to select the destination cells where you want to paste these selected data.
- Navigate to the filtered data and select the cells where you want to paste the previously selected data.
- Click OK
As soon as you click OK, the magic behind the screen begins, and VBA pastes the data only into the visible filtered cells.
CAVEAT: Remember that the changes done by a VBA code cannot be undone, so it’s always a good idea to keep a backup copy of your data set
Also read: How to Select Entire Column (or Row) in Excel – Shortcut
Use Google Sheets to Paste into Filtered Column
And finally, if you’re ok with putting your data in Google Sheets, then you don’t need any special tricks or formulas or VBA code.
Regular copy-paste is going to work in Google Sheets.
Let me show you how it works.
Below, I have the same filtered data set in Google Sheets.
In another sheet, I have these values in a column that I want to copy and paste into column D in my filter data set.
Here is how it works in Google Sheets:
- Copy the data that you want to paste (you can use Control + C).
- Select the filtered column range in which you want to paste this data
- Paste the Data (use Control + V)
And that’s it – it’s that simple.
When it comes to pasting data in filtered rows, Excel and Google Sheets work differently. While Excel always pastes the data in all the cells, whether visible or not, Google Sheets only pastes the data in the visible cells.
So, if it is possible for you to convert your Excel file into a Google Sheets or copy-paste data into a Google Sheets, then get the copy-paste done, and then bring the data back into Excel, you can also use this method.
Note: Remember that by default, there are only 1000 rows in Google Sheets. So if your data set is bigger than that, you need to increase the number of rows in Google Sheets first.
So these are some of the methods you can use to paste data into filtered column while skipping the hidden cells.
While I wish this were the default behavior in Excel, I hope the methods covered in this article help you get this done easily.
I hope you found this article helpful. In case you have any questions or you know of a method that can be used that I should have covered, do let me know in the comments section.
Other Excel articles you may also like:
1 thought on “Paste into Filtered Column (Skipping Hidden Cells) in Excel”
Hi! I’m Dusan
Thanks for posting the article “Paste in filtered column (skip hidden cells) in Excel”. The analysis of the macro code in VBA Excel helped me the most, because I deal with programs in VBA Excel.
Sincerely, Dušan!