Copy & Paste multiple cells with formulas
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
Best Answer
-
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.
Answers
-
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.
-
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
-
Happy to help. 👍️
-
I haven't found this technique works if the formulas to be copied contain external references. The receiving sheet throws an #INVALID REF error.
-
@Pat Canning That is expected behavior if you have not yet set up the cross sheet reference(s) within the formula you are pasting.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives