Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel Functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we apply the Filter feature to our dataset. But it causes various problems when we try to paste only into visible cells. The usual pasting method inputs the copied values into hidden cells, which sometimes isn’t desired. This article will show you 5 easy ways to Paste into Visible Cells Only in Excel.


Download Practice Workbook

Download the following workbook to practice by yourself.


5 Easy Methods to Paste into Visible Cells Only in Excel

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

paste into visible cells only excel

The below picture only contains the product Cable. The rows with the product AC got hidden after applying the filter.


1. Paste into Visible Cells Only Through Go To Command in Excel

In our first method, we’ll use the Go To command in Excel. As we copy a cell value and try to paste it into the filtered column in the worksheet, it also gets pasted in the hidden cells. To avoid this incident, follow the steps below.

STEPS:

  • First, select cell F5, 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.

Paste into Visible Cells Only Through Go To Command in Excel

  • Subsequently, press the F5 key or the Ctrl and G keys together
  • The Go To dialogue box will pop out.
  • There, click Special.

  • Afterward, press the Ctrl and V keys together to paste the value.
  • Hence, 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.

Read More: Excel VBA to Select First Visible Cell in Filtered Range


2. Use Excel Fill Feature for Pasting into Visible Cells

The Fill feature can paste values from the right column to the left one. Here, we’ll copy the cell contents in the E column and paste them into the B column. Therefore, learn the following steps.

Use Excel Fill Feature for Pasting into Visible Cells

STEPS:

  • Firstly, select the desired range of cells to copy.
  • Then, press the Ctrl key and select the column on the left where you want to paste.
  • After that, press the Alt and ; keys together to select only the visible cells.
  • Now, press Home ➤ Editing ➤ Fill ➤ Left.

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

Read More: [Fixed!] Paste Visible Cells Only Not Working (4 Possible Solutions)


3. Paste into Visible Cells with Keyboard Shortcut

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 job. Therefore, follow the steps to know how to perform the task.

STEPS:

  • First of all, 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.
  • This will select the visible cells only.

Paste into Visible Cells with Keyboard Shortcut

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

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


4. Apply Simple Formula in Excel to Paste Only into Visible Cells

Moreover, we can apply a simple formula to input 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 Simple Formula in Excel to Paste Only into Visible Cells

STEPS:

  • At first, select cell D5 and type the formula:
=E5
  • Then, press Enter.
  • Use the AutoFill tool to fill the series.
  • Thus, it’ll simply paste the values.

Read More: Excel VBA: Select Visible Cells After Autofilter (4 Examples)


5. Paste with Excel VBA

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

STEPS:

  • In the beginning, select Developer Visual Basic.

Paste with Excel VBA

  • Then, under the Insert tab, click Module.
  • A new window will pop out.
  • There, paste the code given below:
Sub PasteIntoVisible()
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 VBA window.
  • Now, select the range to copy.

  • Then, select Developer ➤ Macros.
  • Consequently, a Macro dialogue box will pop out.
  • There, choose the macro and press Run.

  • Another dialogue box will pop out asking to choose the destination.
  • In the Input box, select the range of cells in the table where you want to paste the values.
  • Press OK.

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

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


Conclusion

Henceforth, you will be able to Paste into Visible Cells Only in Excel following the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

My name is Aung. I have my B.Sc. degree in EEE. From now on, I will be working with 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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo