Method 3 (pasting a formula into visible cells only) works perfectly. This will save me a lot of time and the hassle of accidentally pasting into hidden cells and overwriting other values. Multiple other "solutions" proposed on Microsoft support pages, KZfaq, reddit, etc were not working. This is a game changing solution to a problem that has been plaguing me.