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

Get FREE Advanced Excel Exercises with Solutions!

With Microsoft Excel’s filtering feature, we can hide irrelevant rows and make particular rows visible. It is a handy feature to sort out and work with essential data. But Excel doesn’t provide any direct way to copy and paste this visible data. Despite that, we can work around this problem by some methods. This article will focus on possible fixes when paste visible cells only not working in Microsoft Excel.


Paste Visible Cells Only Not Working: 4 Possible Solutions

As mentioned earlier, copying and pasting only visible cells does not have any direct method. At least up to the latest version of Excel. What we are gonna need in this case is to work around the problem. Now we are going to focus on some solutions that we can use to paste visible cells only ignoring not working errors in Excel. Keep in mind that not all methods are applicable in all cases. Some are case-specific and contain only a specific output. Nevertheless, they get the job done. So follow through with these methods to find the one that suits your needs.

We are going to use the following dataset for all demonstrations.

dataset for workarounds


Solution 1: Use Go To Special Feature to Paste Visible Cells Only

Let’s hide some rows in between the dataset first for the demonstration.

hiding rows to show paste visible cells only not working

We have hidden the 8th and 9th rows in the dataset. If we try to copy and paste the whole dataset below it, it will look like this.

paste visible cells only not working in hidden rows

As you can see, the hidden rows came over with other rows. In this case of pasting not working as intended, we can follow this procedure to copy and paste visible cells only in Excel.

Steps:

  • First, select the whole dataset you want to copy.

  • Then press F5 or Ctrl+G on your keyboard.
  • In the Go To box, click on Special.

go to box for paste visible cells only not working

  • Now select Visible cells only in the Go To Special box.

go to special box for paste visible cells only not working

  • Then click on OK.
  • As a result, only the visible cells will be selected in the process. Next, copy the selection by pressing Ctrl+V on your keyboard.
  • Finally, paste it to your desired location.

paste visible cells only not working solved

As you can see the paste visible cells only feature is now working as intended.


Solution 2: Use the Fill Command Instead of Copying

Now let’s hide some rows using the filter feature of Excel. This way we are making only the companies from Japan visible in our dataset.

Now let’s say we want to copy the employee count values to the right of the cells. If we simply copy and paste it, after expanding the result will look like this.

To work around this type of problem, we can use the Fill feature of Excel. Unfortunately, this method only works for adjacent cells. But it gets the job done. Follow these steps to see how you can use this feature to copy and paste values in this specific case where it is not working as intended.

Steps:

  • First of all, select the range D7:D16 on the visible dataset.

  • Now select the cells on the right by pressing Ctrl on your keyboard and left click and drag them down from cell E7 to E16.

selecting cells to copy into

  • After that, go to the Home tab on your ribbon.
  • Then select Fill from the Editing group.
  • Next, select Right from the drop-down list.

selecting fill option for paste visible cells only not working

The values will be pasted as follows.

After expanding, we can see that the values will be in the right place.

paste visible cells only not working

Read More: How to Copy Only Visible Cells in Excel


Solution 3: Use Formatting Trick

Let’s say we want to paste new values into each row of the visible cells.

We can use the method described in the following to paste it into the cells only visible after filtering. But first, we need to move these rows elsewhere before filtering as it can hide these new values too.

Steps:

  • First, select the visible cells on the filtered dataset.

  • Now go to the Home tab on your ribbon.
  • Then select the Fill Color icon from the Font group and select any color you want. We are going for yellow for the demonstration.

selecting fill color option

  • Now expand the rows, and the dataset will look like this.

  • Next, right-click on any of the colored cells and select Sort from the context menu.
  • Then select Put Selected Cell Color on Top.

sorting colored cells

  • This will make the dataset rearrange.

  • Finally, copy and paste the values in these cells now.

inserting final values

This way you can work around pasting specific values to visible cells only.

Read More: How to Paste into Visible Cells Only in Excel


Solution 4: Use Cell Reference in Formula Box

While not exactly copying and pasting, you can use formulas instead to fill up cell values of visible cells. This can be another handy workaround.

Follow these steps to see how you can do that.

Steps:

  • First, select the range E7:E16.

selecting cells to copy into

  • Then switch to selecting the visible cells only by pressing Alt+; on your keyboard.

detaching visible cells only

  • While the cells are selected, type in the following formula.

=D7

entering formula to work around paste visible cells only not working

  • Now press Ctrl+Enter on your keyboard.

After expanding the dataset, it will look like this now.

pasting from visible cells only


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


Conclusion

These were all the workarounds we can use in case of paste visible cells only not working in Excel. Hopefully, you can follow the method depending on the outcome you desire with ease now after going through the article. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo