Convert formulas to values in Google Sheets

In this article, you will learn two quickest ways to convert formulas to values in Google Sheets.

Whether you need to transfer data between sheets or files, keep formulas from recalculating in future (for example, the TODAY() function), or simply speed up your file performance, having the calculated values instead of their formulas will help.

Below I'll show you two quickest options spreadsheets offer to make this possible: a classic one and the one that does this in a click.

Classic way to convert formulas to values in Google Sheets

Imagine you use special formulas to pull the domain names from the list of web pages: Extract domain names from the links using the IF formula.

Here's a classic easy way to convert all formulas to values instead:

  1. Highlight all cells with formulas that you need to convert.
  2. Copy the data by pressing Ctrl+C (for Mac it's Cmd+C) on your keyboard.
  3. Then press Ctrl+Shift+V (for Mac it's Cmd+Shift+V) to paste everything back as values: All formulas turned to values.

    Tip. If you're not a key-combos fan, you can get the same result by right-clicking the selected cells. First, select Copy, then Paste special > Paste values only: Copy-paste formulas as values using the context menu in Google Sheets.

One-click way to convert formulas to values in Google Sheets

If you'd rather avoid stumbling over incorrect buttons or surfing spreadsheet menus, we've got you covered.

Our collection of 40+ add-ons for Google Sheets — Power Tools — contains a perfect assistant.


You run it once, it remembers the action, and in future, you convert formulas to values with one click:

  1. Run Power Tools from the Extensions menu in Google Sheets: Start Power Tools.
  2. Go to Formulas: Formulas tool in Power Tools.
  3. Select cells with formulas that you want to convert, then choose Convert formulas to values and hit that blue Run button: The option to convert formulas to values in Google Sheets.

    Voila! All formulas are replaced: All formulas turned to values.

  4. Return to the main Power Tools window and find Recent tools at the bottom. One click here in future will instantly convert selected formulas to values in Google Sheets.

    You can even star the action to have it in your Favorite tools for future use in a click: Find the action in Recent, add it to Favorites, convert formulas to values in one click.

I highly recommend you try this and other 40+ add-ons from Power Tools.

5 minutes saved here and 15 there will become a game changer in your work efficiency.

24 comments

  1. Hi there, Ctrl+C ; Ctrl+Shift+V allows to get rid of the formula, thus not having it changed dynamically. However, it doesn't keep the data the way it is. As an example, typing in any date in A1, then typing in A2 "=A1", return in A2 A1's date as date in A2, but then, using Ctrl+C ; Ctrl+Shift+V on cell A2 return A1's date but as a string (44402 for example).
    How can I get rid of the formula, but keep the formatting ???
    Thank you for your answer
    Loïc

    1. Hello Loïc,

      Ctrl+Shift+V doesn't save the format, it is used to paste the values only. You need to copy and paste the format from A1 afterwards: use Ctrl+Alt+V for that. Or use the Paint Format tool (the paint roller icon in the toolbar) to copy and apply the format.

      1. Thank you for your quick reply.
        I understand that I have to do it in two steps, right ?

        2. Thank you, it works perfectly!

  2. I'd like to convert all formulas to values in any row in which the status cell shows as 'closed' (I have a drop down with 'open' and 'closed' as options). How might I do that?

  3. Hi, is there a way to cut and paste values only?

    1. Hi Jet,

      Have you tried the ways described in this blog post? If they don't work for you, then please describe your task in detail, I'll try to suggest a solution.

  4. This feature is not working for me. Maybe it's because it's 36527 Rows and 10 Columns but it keeps giving me an error in Russian.

    1. It might be important to note that I have 10 sheets in this project structured exactly like it, ~3.6 million cells. But I'm only working with one sheet at a time.

      Here's the error.
      "
      Something went wrong
      Сервис "Таблицы" слишком долго не может получить доступ к документу 19PDLgQOARSjFdgsTLxD5dyrl5Usg8LYgr8jxtWoZR20.
      If you see this error again,
      please contact us
      "

      1. Hello,

        Thank you for reporting this problem to us. I apologize for the late reply.
        Please note that our add-ons don't impose any limitations to the number of cells they process.

        If you're still getting this error, please send its screenshot to us – support@ablebits.com
        Also, when you see the error, press F12 (or Ctrl+Shift+I) on your keyboard (Cmd+alt+J or Cmd+opt+J if you have a Mac) and go to the Console tab. Copy the last 15-20 lines and send them in that email as well.

        All this info will help us understand the cause of the problem. Thank you!

  5. 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!

    1. 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.

  6. 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?

    1. Hi Peggy,

      I'm sorry but your first question is not clear. If you could describe it in more detail, maybe with examples, it would help.
      As for the second one, yes, you can create the IF formula to check the date first and then either return the result of your required calculation or keep a cell empty.

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

  8. 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

    1. You can use "IFERROR" condition;
      IFERROR(VLOOKUP formula),value if FALSE

      Exmple: =IFERROR(VLOOKUP(A1,G:H,2,false),0)

  9. 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.

    1. 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.

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 :)