Copy & Paste multiple cells with formulas

John Stanik

Hi,

I'm trying to copy multiple cells from one sheet to another. The original sheet has formulas that tally up data. When I copy these cells, it pastes the results of the formula, but not the actual formula into the cell. Any suggestions on how to get multiple formulas to copy over (rather than copy and paste each individual formula into each individual destination cell)?

Thanks,

John

Paul Newcome

Have you tried "Paste Special"?

If so and that is still not working, you can try this:

Put some random special character that isn't used in any of the formulas as the very first character. This will convert everything into text strings. You can then copy/paste in bulk and finally do a find/replace to remove that special character which will then convert everything back into formulas.

For example, if you are not using an ! anywhere on the sheet, you can use that like so

!=COUNTIFS(.................................)

Copy/paste this over then find/replace can remove the !.

If you do this to all of the cells on the original sheet first, you should be able to copy/paste in bulk.

Paul Newcome

Have you tried "Paste Special"?

If so and that is still not working, you can try this:

Put some random special character that isn't used in any of the formulas as the very first character. This will convert everything into text strings. You can then copy/paste in bulk and finally do a find/replace to remove that special character which will then convert everything back into formulas.

For example, if you are not using an ! anywhere on the sheet, you can use that like so

!=COUNTIFS(.................................)

Copy/paste this over then find/replace can remove the !.

If you do this to all of the cells on the original sheet first, you should be able to copy/paste in bulk.

John Stanik

@Paul Newcome

You are awesome. Thanks so much for the quick response. I haven't done it yet, but I'm certain that will do the trick. Thanks again!!!

John

Paul Newcome

Happy to help. 👍️

Pat Canning

I haven't found this technique works if the formulas to be copied contain external references. The receiving sheet throws an #INVALID REF error.

Paul Newcome

@Pat Canning That is expected behavior if you have not yet set up the cross sheet reference(s) within the formula you are pasting.