In this tutorial, I will show you how to convert formulas to values in Google Sheets.
Converting Formulas, or formula results into Values in Google Sheets involves selecting the range for the formula, copying or cutting it, then pasting them as Values only.
This method frees up processing bandwidth for Google Sheets, and is especially useful for large Google Sheets files that implement a lot of formulas or computations, making the file work faster.
How to Convert Formulas to Values in Google Sheets:
To convert fomulas to values in Google Sheets copy or cut the formula: CTRL + C or CTRL + X for Windows and CMD + C or CMD + X for macOS
Paste as values only: CTRL + SHIFT + V for Windows and CMD + SHIFT + V for macOS
Steps To Convert Formulas to Values in Google Sheets:
- Select the cell or range where the formula(s) are
- Copy or Cut the range
- Select the cell or range where you want the values to be moved to
- Paste as Values only
Formulas to Values in Google Sheets Video Tutorial
Keyboard Shortcuts to Conver Formulas to Values
Copy or Cut a selection, then paste them as values only in a cell
CTRL + C: Windows keyboard shortcut to copy a selection
CMD + C: macOS keyboard shortcut to copy a selection
CTRL + X: Windows shortcut to cut a selection
CMD + X: macOS shortcut to cut a selection
CTRL + SHIFT + V: Windows keyboard shortcut to paste recent selection as Values only
CMD + SHIFT + V: macOS keyboard shortcut to paste recent selection as Values only
Step By Step Instructions
For this tutorial, I will provide a sample file that will have 1 worksheet. This worksheet consists of a grid of cells, each referencing a relative address. We will be using these samples to paste as Values only around the same worksheet.
You can make a copy of the file by accessing it through this link, then by clicking on “File” from the menu bar, then by clicking on “Make a copy”.
A dialog box should appear. Within it, you can provide a filename for the copy then click “Make a Copy”.
Step 1 – Select the cell or range where the formula(s) are
The first step in our tutorial involves selecting a range to copy or cut, and then paste them as values only.
To select a cell or a range, simple click on the cell, or click and drag across a range, respectively:
To select columns or rows, click on their respective column letters or row numbers:
Lastly, you can select a cell or a range by typing its address on the Name box:
Step 2 – Copy or Cut the range
The next step involves copying or cutting the selected cell or range so you can paste them as Values only.
Once a cell or range has been selected, press either CTRL + C or CTRL + X. The former ensures that the formulas from the original selection are retained, while the latter removes the formulas from the original selection. Additionally, a copied or cut selection will show up with broken selection borders:
Alternatively, you can open the Edit menu through the Menu bar to access the options to copy or cut the selection:
Step 3 – Select the cell or range where you want the values to be moved to
The next step involves, if needed, choosing a cell to move the formulas as Values only to.
After copying or cutting a selected cell or range, your active cell or range still resolves to your previous selection, until you select a new cell or range.
If you wish to paste the Values of the formulas on the same range, you may move on to the next step.
However, If you want to paste your selection elsewhere, though, select a new cell to paste the copied or cut selection to before moving on to the next step.
Step 4 – Paste as Values only
Finally, you can now paste your copied or cut selection as Values only.
To do this, you can press CTRL + SHIFT + V for Windows or CMD + SHIFT + V for macOS.
Or, you can select the “Values only” as your paste option by first opening the Edit menu from the Menu bar, then by highlighting or clicking on the “Paste special” item, then by selecting “Values only” from the options provided.
Before we end, I have a few pointers for you to remember when Converting Formulas to Values in Google Sheets.
First, when pasting a single cell as Values only onto a range, the value of the single cell will be repeated on the destination range.
Next, when pasting a range as Values only onto a cell, the whole range, with respect to its dimensions, will be pasted.
This effectively pastes a range onto a range. This action also overwrites any formulas or values on the destination range that are within the dimensions of the source range.
Formula Conversion Examples
Convert formula from cell A2 as Values only on the same cell:
Convert formulas from range A3:A10 as Values only on the same range:
Convert formulas from range A11:A15 as Values only on cell B11:
Read about how to copy conditional formatting next.
Frequently Asked Questions on How to Convert Formulas to Values in Google Sheets
How do I Convert Formulas to Values in Google Sheets?
Select the cell or range for your formulas then press CTRL + C or CTRL + X on your keyboard. Next select a new cell or range as the destination to paste Values only. Finally press CTRL + SHIFT + V on your keyboard to paste the values only (or results only) of your formulas.
Is there a way to convert a formula into its text form in Google Sheets?
You can use the FORMULATEXT function to extract the formula into its text form. Simply substitute “cell” with the address of the cell where the formula is on this syntax:
=FORMULATEXT(cell)
I converted a formula into a value, but it’s too far back. Is there a way to recover this formula in Google Sheets?
Access an individual cell’s history by selecting it, then right-click it, then choosing “Show edit history”. From the dialog box that appears, you can cycle through the values or formulas pasted onto the cell, with information on when the change has been made.
Conclusion on how to Convert Formulas to Values in Google Sheets
First, select a cell or a range that contains the formula(s) that you want to convert into values.
Next, press CTRL + C or CTRL + X to copy or paste the cell or range.
Finally, press CTRL + SHIFT + V to paste them onto a destination as “Values only”.