Dear Chili,
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:
- 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 126
- Further, press the F5 key or the Ctrl and G keys together to call the "Go to" dialogue box.
- There, select Special.
View attachment 127
- Meanwhile, in the Go To Special dialogue box, select Visible cells only and press OK.
View attachment 128
- 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.
View attachment 129
I hope this helps you. If not, we would really appreciate your reply here with a demo dataset. Good luck!
Regards,
Yousuf Shovon