How to Paste into Visible Cells Only in Excel (6 Easy Methods)

We have the Delivery Status of Ordered Products as a sample dataset. We will filter this dataset and then try to paste values into only the visible cells.

Sample Dataset

Method 1 – Pasting into Visible Cells Only Through the Go To Command in Excel

Step 1 – Filter the Dataset

Click on the drop-down icon for the Delivery Status column.
Select the “In Transit” option and click OK.

Filtering Delivery Status

Step 2 – Paste Data into Visible Cells

Only the products that have an “In Transit” Delivery Status, will show in the dataset. We want to change these statuses to “Delivered”.

Copy cell B23.
Select the cells of the column Delivery Status .
Press F5. You can also use the keyboard shortcut Ctrl + G.
The Go To dialog box will open. Click on the Special option.

Using Keyboard Shortcut to Acces Go To Command

Choose the Visible cells only option from the Go To Special dialog box and click OK. This will select only the visible cells of the worksheet.
Use the keyboard shortcut Ctrl + V to paste the copied data into the visible cells.

Using Go To Special Dialogue Box

The “In Transit” status will be replaced by the “Delivered” status as shown in the following image.

Pasting into Visible Cells

Remove the filter (click on the drop-down and select Clear Filter From…).

Clearing Filters

Here’s the result.

Final Output after Pasting Values in Visible Cells

Method 2 – Use Excel Fill for Pasting into Visible Cells

Filter the dataset based on the Delivery Status (“Delivered”).
Select the cells of the filtered Delivery Status column.
Hold Ctrl and select the destination cells. We have selected the cells of the Shipment column as our destination cells.
Go to the Home tab, choose the Fill option from the Editing group, and select the Left option as we are pasting data to the left of the Delivery Status column.

Using Fill Option

Clear the filter.
The copied Delivery Status values are pasted to the Shipment column. The rest of the cells remain blank.

Values Pasted Only in Visible Cells in Shipment Column

Method 3 – Pasting into Visible Cells with a Keyboard Shortcut

Filter the dataset.
Select the cells of the Delivery Status column.
Type in the desired Delivery Status (“Delivered“).
Press Ctrl + Enter.

Using Keyboard Shortcut

Clear the filter in the Delivery Status column.

Final Output after Pasting Values in Visible Cells by Using Keyboard Shortcut

Method 4 – Using the Quick Access Toolbar to Paste into Visible Cells Only

Click on the Customize Quick Access Toolbar option.
Select the More Commands option from the drop-down.

Adding More Commands in Quick Access Toolbar

The Excel Options dialog box will open.

Click on the drop-down icon and select the All Commands option.

Viewing All Commands

Scroll down and select the Select Visible Cells option.
Click on Add and click OK.

Adding Select Visible Cells Option

The Select Visible Cells option will be added to the Quick Access Toolbar.

Copy the data that you want to paste. We copied cell B23.
Select the cells of the Delivery Status column.
Click the new icon that was created in the Quick Access Toolbar.
Use the keyboard shortcut Ctrl + V to paste the copied data.

Using Newly Added Quick Access Toolbar Option

Clear the filter to see the entire table.

Values Pasted Only in Visible Cells using Quick Access Toolbar

Method 5 – Apply a Simple Formula in Excel to Paste Only into Visible Cells

Filter the table.
Use the following formula in cell D5 and press Enter.

=[@[Delivery Status]]

Here, [Delivery Status] is the Named Range of the Delivery Status column.

Using Formula

Excel will automatically copy the formula for the rest of the cells in the Shipment column.

Note: If you don’t use a table, then your formula will be like “=E6”. You also need to drag the Fill Handle to copy down the formula.

Final Output after Pasting Values in Visible Cells Applying Formula

Clear the filters.

Method 6 – Pasting into Visible Cells with Excel VBA

Step 1 – Launch the VBA Editor

Go to the Developer tab and choose the Visual Basic option.

Note: The Developer tab is not available in Excel by default. You need to manually add the Developer tab.

Selecting Visual Basic Option

The Microsoft Visual Basic for Applications window will open.

Go to the Insert tab and select the Module option.

Inserting Module

Step 2 – Insert the VBA Code

A blank Module will be created.
Insert the following VBA code in it and click on the Save icon.

Writing and Saving VBA Code

Sub PasteIntoVisible()
Dim rng As Range
Dim visinputRange As Range
Dim dest As Range
Dim inputRange As Range
Dim r As Range
Set rng = Application.Selection
rng.SpecialCells(xlCellTypeVisible).Select
Set visinputRange = Application.Selection
Set dest = Application.InputBox("Choose dest:", Type:=8)
For Each inputRange In visinputRange
inputRange.Copy
For Each r In dest
If r.EntireRow.RowHeight <> 0 Then
r.PasteSpecial
Set dest = r.Offset(1).Resize(dest.Rows.Count)
Exit For
End If
Next r
Next inputRange
End Sub

Code Breakdown

List of Variables Used

Variable Name	Data Type
*rng*	Range
*visinputRange*	Range
*dest*	Range
*inputRange*	Range
r	Range

We created a sub-procedure named PasteIntoVisible and declared the necessary variables for the code.
We used the Set statement to assign the selected cells to the rng variable. After that, we used the rng.SpecialCells(xlCellTypeVisible).Select method to select only the visible cells of the selected range.
We also used an InputBox to take the destination range as input. Following that, we used the Set statement to assign the input obtained from InputBox to the variable named dest.
We used two nested ForNext loops to copy data from the range visinputRange and paste the copied data to the range named dest.

Step 3 – Run the VBA Code

Use the keyboard shortcut Alt + F11 to return to the worksheet from the VBA Editor window.
Apply a filter.
Select the cells that you want to copy.
Go to the Developer tab.
Select the Macros option.

Using Macros Option

The Macros dialog box will open. Choose the PasteIntoVisible macro and click on Run.

Running Macro

Insert the destination range in the prompt and click OK.

Selecting Destination Range

Clear the filters.

Values Pasted Only in Visible Cells Using VBA

How to Paste Formatting into Visible Cells Only in Excel

Copy the cell from which you want to copy the formatting.
Select the range where you want to paste the formatting.
Use the keyboard shortcut “Alt +;” to select only visible cells.
Go to the Home tab and choose the Paste option.
Select the Formatting (R) option from the drop-down.

Pasting Formatting into Visible Cells

Clear the filters and you will see that the formatting is pasted to the visible cells only.

Output after Pasting Formatting

Frequently Asked Question

How to Paste Data Validation into Visible Cells Only in Excel

Pasting data validation is exactly the same as pasting formatting. Just you need to copy a cell that has data validation instead of copying formatting. The rest of the steps are the same.

Is there a way to make “paste into visible cells only” the default paste option in Excel?

No, there is no built-in way to make the “paste into visible cells only” the default pasting option in Excel. But you can always create a custom macro or add-in that will automatically paste data into visible cells only. You can also use keyboard shortcuts to do this.