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.

copy and paste in excel when filter is on


Download Practice Workbook

To practice by yourself, download the following workbook.


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

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.

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

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

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

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

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


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.

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

  • 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.

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

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

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

Read More: How to Autofilter and Copy Visible Rows with Excel VBA


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.

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

  • 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.

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

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

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

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

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

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

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

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

Read More: How to Copy Merged and Filtered Cells in Excel (4 Methods)


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 pastes 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.

Read More: How to Copy Rows in Excel with Filter (6 Fast Methods)


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.

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

STEPS:

  • At first, select the range of cells.

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

  • 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.

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

  • 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.

Read More: Formula to Copy and Paste Values in Excel (5 Examples)


Similar Readings


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.

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

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

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

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

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

  • 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.

Read More: How to Copy and Paste Visible Cells Only in Excel (3 Easy Ways)


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.

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

STEPS:

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

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

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

Read More: How to Copy Visible Cells Only without Header Using VBA


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.

Paste a Set of Values to the Visible Cells Only with Excel VBA

  • Then, under the Insert tab, select Module.

Paste a Set of Values to the Visible Cells Only with Excel VBA

  • 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

Paste a Set of Values to the Visible Cells Only with Excel VBA

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

Paste a Set of Values to the Visible Cells Only with Excel VBA

  • Then, select Macros under the Developer tab.

Paste a Set of Values to the Visible Cells Only with Excel VBA

  • 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: Excel VBA to Copy Only Values to Destination (Macro, UDF, and UserForm)


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

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

3 Comments
  1. Hi Aung,
    Thanks for your article.
    However I still fail to copied filtered values and paste to filtered column on other file.
    Any hints

    Thank you
    Hendry

    • Thank you Hendry for reaching out.
      I’ve looked into your matter. You can easily carry out the mentioned operation using the 5th method in this article i.e. VBA method.
      Kindly try with VBA and if you still can’t get the job done, feel free to email me. My mail: [email protected]
      Good luck.

  2. but the above process will not work when there is coloum with merged cells.
    if any one has clues please share

Leave a reply

ExcelDemy
Logo