Quick guide
Here’s an easy way to convert formulas to values in Google Sheets:
1. Select the cells containing the formulas
2. Use keyboard shortcut Ctrl+C to copy them
3. Press Ctrl+Shift+V to paste only the values
This will convert the formulas to their values.
(If you use a Mac, use Cmd instead of Ctrl)
There are instances when we want to copy only the values of the formulas or stop volatile formulas such as the TODAY function and the RAND function from automatically updating the values.
So we might want to convert the formulas to values in Google Sheets. Here in this tutorial, we are going to learn some of the cool and simple methods to convert formulas with their values in Google Sheets.
Method 1: Using keyboard shortcuts to convert formulas to values in Google Sheets
This is the most simple as well as the easiest way to convert formulas to values in Google Sheets.
Here’s the step-by-step guide on using keyboard shortcuts to change formulas to values in Google Sheets:
Step 1: Select the cells containing the values you want to convert
You can either use a mouse to select the cells or select them by using the Shift key and the arrow keys.
To select using the Shift key and the arrow keys, select the first cell containing the values. Then pressing the Shift key, use the down arrow key to select the cells below (or the right arrow if the data are arranged in rows).
Step 2: Copy them by pressing Ctrl+C. For Mac users, the shortcut is Cmd+C
Step 3: Paste the values in a new cell range or in the same range by pressing Ctrl+Shift+V. (Cmd+Shift+V for Mac)
This will convert the formulas to values in Google Sheets and paste only the formulas values and not the formulas themselves.
Method 2: Convert formulas to values in Google Sheets using a mouse
This method works the same way as the keyboard shortcut method. The only difference is that we use a mouse or trackpad to copy and paste.
In this example, I have a list of randomly generated salaries (not normally how salaries are given). We want to convert these into values so that they do not change each time an update is made.
Step 1: Select the cells containing the formula values.
Step 2: Right-click anywhere in the selected range.
Step 3: From the list of options, click Copy.
Step 4: Select any cell you want to place the value, right-click→Paste special→Values only.
This will copy and paste only the formula values while ignoring the formulas.
You can also paste the values into the same cells from which you copied. After copying the values, right-click anywhere in the selected range and paste using the Paste special option. The formula will then be replaced with their values.
As we can see, the formulas have been changed to their values.
Method 3: Using the TO_TEXT function to convert formulas to values in Google Sheets
The TO_TEXT function converts any numerical value into a text string. So, if we want to convert formulas to values in Google Sheets, we can use the TO_TEXT function to do so.
We should note, however, that the values converted using the TO_TEXT function will not work with mathematical formulas such as sum and average.
To convert formulas to values in Google Sheets using the TO_TEXT function, all we have to do is enter the function in an empty cell and the cell that has the formula we want to convert to value as the argument.
To convert the randomly generated salaries into values using the TO_TEXT function, use the following formula.
=TO_TEXT(B2)Here, B2 is the cell with the random number that we want to convert to a fixed value.
On pressing the Enter key, we get the text value in the cell with the formula left behind.
To convert all the other formula values to their text values, we can simply drag the TO_TEXT formula cell to the last cell.
So that is our guide on how we convert formulas to values in Google Sheets.
Conclusion
There are a few ways to convert formulas to values in Google Sheets, three of which we have learned. Converting the formulas to values comes in handy for different reasons.
If you want to learn more cool and handy formulas and tips on Google Sheets, you can check our blog. You’ll surely have much to learn
See also
Here are some more articles that you might find interesting:
https://blog.tryamigo.com/translate-function-in-google-sheets/
https://blog.tryamigo.com/how-to-fix-formula-parse-errors-in-google-sheets/
https://blog.tryamigo.com/how-to-split-text-to-columns-in-google-sheets/