Welcome to ExcelDemy Forum
! We apologize for the late reply and thank you for explaining your problem in brief words. However, it would be really easier for us to understand better if you could share your Excel file.
As we understand, you are facing issues to copy-paste formulas in a Filter
column. This is because Excel does not allow pasting to cells in a range that contains hidden rows or columns (which, in our case, has hidden rows). Thus, it shows the below error message.
View attachment 125
Fortunately, we can find an alternative way to overcome this issue. Follow the steps below:
View attachment 126
- In the beginning, select cell E6 as this is the value we want to paste in the filtered column. Do the same if you are working with a formula.
- Afterward, press the Ctrl and C keys together to copy.
- Next, select the cells in the filtered column where you want to paste the E6 cell value.
View attachment 127
- Further, press the F5 key or the Ctrl and G keys together to call the "Go to" dialogue box.
- There, select Special.
View attachment 128
- Meanwhile, in the Go To Special dialogue box, select Visible cells only and press OK.
View attachment 129
- Lastly, press the Ctrl and V keys together to paste the value and 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.
I hope this helps you. If not, we would really appreciate your reply here with a demo dataset. Good luck!