Formulas always help make data processing easier and faster. As helpful as they are, formulas can sometimes come in the way of what we are trying to achieve.
So, it could become necessary at times to convert formula results to values. In essence, we are only replacing the formulas with the values obtained as a result of them.
In this tutorial we will show you two ways to convert formula to value in Google Sheets:
- Using the ‘Paste as Values’ menu
- Using a keyboard shortcut
This Article Covers:
Why Convert Formula to Value?
Before discussing how to convert formula to value, it is important to understand why we would need to do so in the first place. There are numerous cases where it might be helpful to convert the formula to value. Here are just a few:
- When you copy a formula from one cell to another, it often results in an error (or a result that is unexpected). This is because the formula recalculates the value after adjusting the cell references in it. To avoid errors like these, it helps to first convert your formula to its resultant value before copying and pasting it somewhere else.
- When you use functions like RAND or RANDBETWEEN, you will notice that they self-refresh every time there’s a change in the sheet. You might just want to stick to one value. So to avoid the randomly generated cell values from auto-refreshing every time, you could just convert the formula to value.
- You might need to share a spreadsheet with someone but might not want to disclose the formulas you used. In such cases too, it helps to convert the formulas to value.
- When working with large spreadsheets, having too many formulas might slow down the processing. If you don’t need the formulas anymore, converting them to value can help speed up the spreadsheet processing.
There may be a number of other reasons too besides the ones listed above, that might require you to convert formulas to values.
How to Convert Formula to Value
Let us take a look at two easy and quick ways to convert formula to value in Google Sheets. Both methods involve simply copying the formula and replacing them with their resultant values.
To demonstrate both methods, we are going to use the following dataset:
As you can see, the above dataset consists of a set of formulas in Column B that squares the values in Column A.
Using Paste as Values to Convert Formula to Value
This method involves just a few clicks of your mouse. Here are the steps you need to follow, in order to convert the formulas in column B to values:
- Select the cells containing the formulas that you want to convert. In our example, select cells B2 to B10.
- Copy the cells, by using the keyboard shortcut CTRL+C (if you are on a Mac, then press Cmd+C).
- Right-click on your selection and hover over the ‘Paste Special’ option from the context menu that appears.
- This will display a new context sub-menu, with all your paste options.
- Select the ‘Paste values only’ option from this submenu.
This will cause all your formulas to get replaced with just their returned values.
Using a Keyboard Shortcut to Convert Formula to Value
A quicker way to convert formula to value is to use a keyboard shortcut. Just like we use the shortcut CTRL+C to copy values and CTRL+V to paste values, we can use CTRL+SHIFT+V to paste only the value returned by a formula.
Here are the steps you need to follow, in order to convert formula to value using this method:
- Select the cells containing the formulas that you want to convert. In our example, select cells B2 to B10.
- Copy the cells, by using the keyboard shortcut CTRL+C (if you are on a Mac, then press Cmd+C).
- From your keyboard, press the keyboard shortcut CTRL+SHIFT+V (hold down the SHIFT and CTRL keys and then press the V key while the former two keys remain pressed). If you’re on a Mac you can press the Cmd key instead of CTRL.
This will cause all your formulas to get replaced with just their returned values.
In this tutorial, we showed you two easy ways to convert formulas to value in Google Sheets. One method involved using the context menu and the other involved using a keyboard shortcut. We hope you found the tutorial helpful and easy to follow.
