Abrar-ur-Rahman Niloy
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 of paste visible cells only not working in Microsoft Excel.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
4 Possible Solutions If You Are Unable to Paste Visible Cells Only
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.
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.
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.
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.
- Now select Visible cells only in the Go To Special box.
- 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.
As you can see the paste visible cells only feature is now working as intended.
Read More: How to Paste into Visible Cells Only in Excel (5 Easy Methods)
Solution 2: Use 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.
- 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.
The values will be pasted as follows.
After expanding, we can see that the values will be in the right place.
Read More: How to Copy Visible Cells Only in Excel (4 Fast Ways)
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.
- 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.
- This will make the dataset rearrange.
- Finally, copy and paste the values in these cells now.
This way you can work around pasting specific values to visible cells only.
Read More: Excel VBA to Select First Visible Cell in Filtered Range
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.
- Then switch to selecting the visible cells only by pressing Alt+; on your keyboard.
- While the cells are selected, type in the following formula.
=D7
- Now press Ctrl+Enter on your keyboard.
After expanding the dataset, it will look like this now.
Read More: How to Copy Visible Cells Only without Header Using VBA
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.
For more guides like this, visit Exceldemy.com.
Related Articles
- How to Select Visible Cells in Excel with VBA (5 Easy Methods)
- Sum Visible Cells with Criteria in Excel (5 Easy Methods)
- How to Average Only Visible Cells in Excel (3 Quick Methods)
- Excel VBA: Select Visible Cells After Autofilter (4 Examples)
- How to Count Only Visible Cells in Excel (5 Tricks)
- Sum Only Visible Cells in Excel (4 Quick Ways)
