This post is going to show you all the ways you can convert formulas to values in Google Sheets.

Using a formula is the easiest and most efficient to calculate anything when working in a spreadsheet. However, there are times when you just want to keep the values derived from those formulas.

For instance, when you use a formula to generate random numbers you might want to remove the formula, or else the random numbers will change every time the spreadsheet refreshes.

Spreadsheet optimization is another reason you may want to remove formulas, especially when you don’t need the value to recalculate anymore.

More formulas will affect the time your spreadsheet takes to carry out calculations, so less can be better for speed.

How do you convert formulas to values? There are various methods and they will all be explored. Get your copy of the workbook to follow along!

Convert Formulas to Values using the Edit Menu

The above example has an accounting format created from the numbers in column A by using the TEXT function. This will provide a perfect illustration of what converting formulas to values can achieve.

= TEXT ( A1, "$* #,##.00?" )

Copy and paste the above syntax to an empty cell to apply custom accounting format to the numbers.

Because the TEXT function was used to apply the accounting format, the spreadsheet now reads the resulting values as text.

If the values are left as they are, you will not be able to do any meaningful calculation with the numbers, for instance. You will have to extract the values from the formula.

This is how to go about it using the Edit menu

1. Select all the cells with formulas which you would like to convert to values.

2. Click on the Edit menu.
3. Select Copy from the options.

After you click on Copy, the edges of the selected cells will change from solid to dashed lines. This indicates the range has been copied to your clipboard.

4. Go to the Edit menu then select Paste Special from the options.
5. Select Values only from the options.

In the formula bar, you will see that the numbers are no longer generated by the TEXT function. The formula has been removed and the numbers are now just raw values with accounting format.

The formulas have been converted to values!

Convert Formula to Values with a Right Click

The options used in the Edit menu can also be accessed by using the right-click button on your mouse.

First, you must select the cells with the formulas, then follow these steps.

1. Place your mouse cursor anywhere inside the selected cells and right-click. Then select Copy. The edges of the selected cell will change from solid lines to dashed lines.

2. Place the arrow inside the selected cells and right-click. Select Paste special from the options.
3. Select Values only.

You will get the same result as earlier.

Convert Formulas to Values with a Keyboard Shortcut

You can cut out all the mouse clicks involved with converting formulas to values by using a simple keyboard shortcut.

You have to select the cells containing the formulas you want to convert. Then, follow these steps.

1. Press Ctrl + C to copy the cells.
2. Witht the same range of cells selected, press Ctrl + Shift + V on your keyboard.

With these two easy steps, you will have converted formulas to values.

Convert Formulas to Values with Apps Scripts

You can use an Apps Script to automate the processes of copying and pasting formulas to values.

Go to the Extensions menu and select Apps Script from the options.

function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom')
      .addItem('Paste Values', 'pasteValues')
      .addToUi();

};

function pasteValues() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveRange().copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

Copy and paste the above code into your Apps Script editor window. Ensure you hit Save.

The first part of the script creates a custom menu in your spreadsheet user interface called Custom Menu.

When you click on the Custom menu, you get the Paste Values menu. The second half of the script copies the selected cell or range of cells and pastes its values.

To use the script, select the cell or range of cells containing the formulas you want to convert then go to the Custom menu and click on the Paste Values option.

Conclusion

Even though it seems simple, knowing how to convert formulas to values is a useful skill to add to your arsenal of spreadsheet knowledge.

You will find that you need to use it more times than you might think!

Which of the method above will use most frequently? Let me know in the comments section below!