Convert formulas to values in Google Sheets

In this article, you will learn about two ways of replacing all formulas with their results in a spreadsheet.

There are some cases when you may need to have the calculated values instead of their formulas:

  • To transfer data between sheets or even spreadsheets quickly.
  • To keep formulas from recalculating if cells they refer to change.
  • To save the results of the RAND function.
  • To speed up your spreadsheet performance especially if there are lots of complex formulas.
  • To prevent others from peeping at your calculations when you share or send your spreadsheets (for example, to not give away any extra charges or sales you may offer as a seller).

Thus, today I offer you two options for making this possible.

Classic way to replace formulas with values in Google Sheets

Let's imagine you have a list of web pages and you use a special function to pull the domain names from those long links:
The IF formula to extract domain names from links.
Now you need to switch all formulas to results instead. Here's what you can do:

  1. Highlight all cells you need to modify.
  2. Take all formulas to the clipboard by pressing Ctrl+C on your keyboard.
  3. Then press Ctrl+Shift+V to paste back the values only:
    Insert results instead of original formulas.

    Tip. Ctrl+Shift+V is the Google Sheets shortcut for Paste value only.

The quickest way to convert formulas to values in your spreadsheet

If you'd rather avoid stumbling over incorrect buttons, we've got you covered. Our Power Tools - a collection of 20+ add-ons for Google Sheets - contains a perfect assistant.

  1. Run the collection from Add-ons > Power Tools > Start and click the Formulas icon:
    Formulas tool to help you insert results instead of formulas.

    Tip. To run the Formulas tool right away, go to Add-ons > Power Tools > Formulas.
  2. Select all cells you want to alter and choose Convert formulas to values:
    Change formulas to values using Ablebits tool.
  3. Hit Run and voila - all formulas are replaced in a click:
    Get values instead of their formulas in a click.

I highly recommend you to try other add-ons from Power Tools: 5 minutes saved here and 15 there may become a game changer in your work efficiency.

You may also be interested in:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!