How to Copy and Paste in Excel When Filter Is On (5 Methods)

The Filter feature is a very useful tool for anyone working with MS Excel. But, it causes various problems when we try to copy and paste in Excel datasheets keeping this feature on. So, this article will show you the effective methods to Copy and Paste in Excel when the filter feature is on.

To illustrate, I’m going to use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales of a company. There are two products: Cable and TV. Here, we’ll apply the Filter feature to the products.

How to Copy and Paste in Excel When Filter is On: 5 Methods

1. Keyboard Shortcuts to Copy and Paste When Filter is On in Excel

We can follow some effective methods to avoid the problems in Excel sheets when we try to copy and paste with the Filter on. In our first method, we’ll use Keyboard Shortcuts to copy and paste cell values in the filtered dataset. But first, follow the steps below to apply the Filter to the products.

STEPS:

• Firstly, select the range including the Headers.

• Then, select Filter from the ‘Sort & Filter’ drop-down list in the Editing group under the Home tab.

• After that, select the drop-down symbol beside the header Product. There, check the Cable box only and press OK.

1.1 Copy Only the Visible Cells

When we copy the filtered columns in Excel, it automatically copies the hidden cells along with the visible cells. But, most of the time that is not our desired operation. So, to copy only the visible cells, we’ll use ‘Alt’ and ‘;’ keys together.

STEPS:

• At first, select the range.

• Then, press the ‘Alt’ and ‘;’ keys together to select only the visible cells.
• After that, press the ‘Ctrl’ and ‘C’ keys to copy.
• Now, select cell F5 to paste the copied values.

• Finally, press the ‘Ctrl’ and ‘V’ keys together and it’ll paste the cells like it’s shown below.

1.2 Paste a Value or Formula in the Visible Cells

As we copy a cell value and try to paste it in the filtered column in the Excel sheet, it also gets pasted in the hidden cells maintaining the serial. To avoid this incident, follow the steps below.

STEPS:

• In the beginning, select cell F5 as this is the value we want to paste in the filtered column.

• Then, press the ‘Ctrl’ and ‘C’ keys together to copy.
• After that, select the cells in the filtered column where you want to paste the F5 cell value.

• Subsequently, press the ‘F5’ key or the ‘Ctrl’ and ‘G’ keys together and a dialogue box will pop out.
• There, select Special.

• Then, in the Go To Special dialogue box, select Visible cells only and press OK.

• Next, press the ‘Ctrl’ and ‘V’ keys together to paste the value and it’ll return the desired result.

• Eventually, if you remove the Filter feature, you’ll see the new value only in the visible cells of the previously filtered column.

1.3 Paste a set of Values from Left to Right in a Filtered Table

It shows an error when we copy the visible cells and paste them into another column of the same filtered table. But, we can apply some tricks to do the task. Therefore, follow the steps to know how to perform the task.

STEPS:

• Firstly, select the range.

• Next, press the ‘Ctrl’ key, and at the same time, select the range of cells where you want to paste.

• Then, press the ‘Alt’ and ‘;’ keys together.

• At last, press the ‘Ctrl’ and ‘R’ keys together and it’ll paste the values in the required column.

2. Use Fill Feature for Pasting a Set of Values from Right to Left in Filtered Table

We applied a keyboard shortcut to paste a set of values from Left to Right in a filtered table. But, there is no such way to do that from Right to Left. However, we can use the Excel Fill feature to do the operation. Hence, learn the process given below.

STEPS:

• At first, select the range of cells.

• Then, press the ‘Ctrl’ key and select the column in the left where you want to paste.
• After that, press the ‘Alt’ and ‘;’ keys together to select only the visible cells.

• Now, press Left from the Fill drop-down list in the Editing group under the Home tab.

• As a result, it’ll paste the values in the selected column on the left side.

3. Excel Find & Select Feature to Copy Only the Visible Cells in Filtered Column

We know Excel provides useful Features to perform many operations. In this method, we’ll apply the Excel ‘Find & Select’ feature for copying the visible cells only.

STEPS:

• First, select the range you want to copy.

• Then, under the Home tab, select Go To Special from the Find & Select drop-down list in the Editing tab.

• Consequently, a dialogue box will pop out and there, select Visible cells only.
• After that, press OK.

• Now, select Copy in the Clipboard section.

• Finally, select any cell you where you want to paste.
• In this example, select cell F7. There, press the ‘Ctrl’ and ‘V’ keys together and it’ll return the accurate outcome.

4. Apply Formula to Paste a Set of Values to the Visible Cells

Moreover, we can apply a simple formula to copy and paste a set of values in the same filtered table. For this case, we want to copy the values in column E and paste them into column D only for the product Cable. Therefore, see the steps described below to create the formula.

STEPS:

• At first, select cell D5 and type the formula:

• Then, press Enter and use the AutoFill tool to fill the series.
• As a result, it’ll simply paste the values.

Read More: Copy and Paste Formulas Without Changing Cell References

5. Paste a Set of Values with Excel VBA When Filter is On

Lastly, we will paste a set of values in the same filtered table using Excel VBA code. So, follow along and learn the process.

STEPS:

• First of all, under the Developer tab, select Visual Basic.

• Then, under the Insert tab, select Module.

• A new window will pop out.
• There, paste the code given below:

Sub Paste()
Dim rg As Range
Dim visible_source As Range
Dim destination As Range
Dim source As Range
Dim r As Range
Set rg = Application.Selection
rg.SpecialCells(xlCellTypeVisible).Select
Set visible_source = Application.Selection
Set destination = Application.InputBox("Choose Destination:", Type:=8)
For Each source In visible_source
source.Copy
For Each r In destination
If r.EntireRow.RowHeight <> 0 Then
r.PasteSpecial
Set destination = r.Offset(1).Resize(destination.Rows.Count)
Exit For
End If
Next r
Next source
End Sub

• After that, close the Visual Basic window.
• Now, select the range to copy.

• Then, select Macros under the Developer tab.

• Consequently, a Macro dialogue box will pop out.
• There, select Paste in the Macro name and press Run.

• Another dialogue box will pop out asking to choose the destination.
• In the Choose Destination box, type: $D$5:$D$10 or, select the range of cells in the table where you want to paste the values and press OK.

• At last, the required output will appear in column D.

Read More: Copy and Paste Without Changing the Format in Excel

Download Practice Workbook

To practice by yourself, download the following workbook.

Conclusion

Henceforth, you will be able to copy and paste in Excel when the filter is on with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

<< Go Back to Copy Paste in Excel | Learn Excel