1

I have a spreadsheet with a number of formulas that need to be removed with the values left behind. I generally just use copy & paste-as-values, but I am working with a number of sheets with a number of tabs.

Is there a way of stripping formulas in bulk but leaving behind the values?

The end users want to be able to sort and filter their data without regard to the formulas. This question is very similar, but I would be open to using scripts or add-ons.

2

If the copy-paste answer isn't satisfactory, you can write a script that methodically goes through every cell in the spreadsheet, finds its formula, calculates the value, and replaces it. I've done it before.

If this is too coarse, you can use custom coded dialogues and scripts so your users can specify which sheets/ranges to include or avoid. This would involve some alerts with prompts, input handling, and possibly hooking menu items and subitems to your coded formulae.

Just take a look at Google's reference for their scripting and you can get it done. Here's a good starting point:

https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(String)

| improve this answer | |
1
  • CTRL + A
  • CTRL + SHIFT + V

and if it's too much you can use AutoHotKey to automate it

| improve this answer | |
  • As mentioned above, "I generally just use copy & paste-as-values". Unfortunately this is a work machine, so installs of external software are restricted. – mfg Aug 2 '18 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.