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.

Whether you need to transfer data between sheets or even spreadsheets, keep formulas from recalculating (for example, the RAND function), or simply speed up your spreadsheet performance, having the calculated values instead of their formulas will help.

Today I offer you two options to make this possible: the standard and the fastest.

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 values only (right-click a cell > Paste special > Paste values 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 30+ 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.

    Tip. You can repeat this action even quicker from the main Power Tools window.

    Once you convert formulas to values, this action will appear in the Recent tools tab at the bottom of the main window. Click there to run the tool again or star it to add it to your Favorite tools for future use:
    Find the action in the recently used or add to favorites.

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.

11 responses to "Convert formulas to values in Google Sheets"

  1. Claire says:

    Hi. Is there a way to convert formulas to values automatically when data is entered for the first time rather than having to manually copy and paste or run power tool? Thanks in advance.

    • Hi Claire,

      I'm afraid our tools cannot be run automatically. You need to start Power Tools whenever you want to convert formulas to values.

      However, there's a way to do that faster.
      Once you go through Convert formulas to values, return to the main Power Tools window where there are Recent and Favorite tools at the bottom. Recent tools will feature the last action - Convert formula to value.
      Hover the mouse over it and when the star icon appears, click on it. The operation will be added to the Favorite tools tab.
      The next time you run the add-on, just initiate the conversion in one click from that tab.

      Hope this will ease your task.

  2. Richard Grabowski says:

    Hi Claire,
    I have a VLOOKUP that returns a value from another spreadsheet (not tab) if a matching value to the VLOOKUP exists. Pretty standard.
    However; if no match exists the result is the cell gets filled in with #N/A
    This pretty much ruins my ability to perform any more calculations on that column.

    So, do you know of a way to change #N/A to a number?
    If not, can we stop the result from displaying #N/A?
    If not, is there a way to create and if statement using VLOOKUP as the logical_expression in an if statement and as the value if true and using "0" if false?

    I love the powertools! great for repeating tasks and quick hits.

    Rich

  3. Brady Charron says:

    Another way to accomplish this is found here:.
    Copy the data, right click > paste special > paste values only.

  4. Peggy Cook says:

    Hi
    Is there a way to have a formula that calculates a value but after a particular date or time is then fixed
    So even if you change the values in the cells it is using to calculate a figure then this will no longer effect that figure?
    Is it also possible to have a formula that does not start calculating until a particular date or time?

  5. HM says:

    Hi Natalia,

    I have shared a sample google sheet with support@apps4gs.com where I need to change a formula to text.
    I have already spoken to Katerina over the phone but the PowerTools were not successful in removing the = sign on the cells.

    Please let me know the best way to do so.

    Thanks!

    • Hi Howard,

      Thank you for sharing the file. When you search for '=' in formulas with our Advanced Find and Replace, it doesn't remove the sign from the beginning. You see, the equal sign is often used as a comparison operator further in the formula. If we remove/replace the first one as well, it will break all formulas whatsoever. I will forward this idea to our developers and we'll try to find a better solution.

      In the meantime, there is a workaround. I added a few columns to your table to illustrate it step-by-step:

      1. Use a helper column with a formula like this:
        =IFERROR(G2,FORMULATEXT(G2))

        If there are formulas with errors, the content of the formula will be pulled (as in H4:H9), otherwise the number itself (H2:H3).

      2. Use Advanced Find & Replace to search for equal signs (=) in Values and replace them all with commas (,)
      3. Go to the Formulas group and convert formulas to values.
      4. Go to the Text group > Remove > Remove characters by position > Remove characters before text and enter +. Click Remove and all extra characters before the plus sign will be removed. Then remove the remaining first character which is the plus itself.
        If the number of characters in cells is always the same, on this step you can use Remove the first N characters instead to delete everything before numbers right away.

      I included the screenshots of all the options I used right under the columns.
      Hope this workaround will be helpful.

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)