While formulas are awesome, there might be some cases when you want to convert formula to values in Google Sheets.
Here are some likely scenarios when this might be needed:
- You want to copy and paste data in different places on the same sheet (or in a different sheet) without having the formula to adjust the references and show different results or errors.
- You have used the RAND or RANDBETWEEN functions to create a list of random numbers and you only want the values and not these cells updating/changing every time there is a change in the sheet
- You want to share your Google Sheet with a colleague or client and don’t want them to mess up the formula (or if you only want to share the values and keep the formula to yourselves)
- The formulas are no longer needed and you want to convert these to values to speed up your Google Sheets document
I am sure there can be many more such situations in which converting a formula to value is the right thing to do.
In this tutorial, I will show you two simple ways to convert formulas to values in Google Sheets.
Copy and Then Paste as Value
This is really as simple as the heading says.
You copy the cells which have the formula that you want to convert and then you paste these as values (instead of formulas).
Suppose you have a student scores dataset as shown below and you want to convert the formula used to get the grades into values.
Below are the steps to copy the cells with formulas and convert these into values.
- Select the cells that have the formula that you want to convert to values (B2:B11 in this example)
- Copy the cells. You can use the keyboard shortcut Control + C, or can right-click and then select Copy.
- With the cells still selected, right-click on any cell and hover the cursor over the Paste Special option.
- In the Paste Special options, click on ‘Paste values only’
The above steps would remove the formula and you will only be left with the values.
Keyboard Shortcuts to Copy and Paste as Values
To copy the cells – CONTROL+ C
To Paste as values – CONTROL + SHIFT + V (hold the Control and Shift keys together and then press the V key)
In case you convert the cells that contain formulas to values and want to revert back, you can use the keyboard shortcut Control + Z. This will bring back the formulas. And if you think you may need to revisit the formulas in the future, it’s recommended to create a backup copy of the Google Sheets document.
You May Also like the following Google Sheets tutorials:
- How to Split Cell in Google Sheets
- How to Separate First and Last Name in Google Sheets
- How to Indent Text in Google Sheets
- How to Delete Empty Rows in Google Sheets
- How to Change Text Case in Google Sheets (Upper, Lower, Proper, or Sentence)
- How to Convert Excel to Google Sheets
- How to Show Formulas in Google Sheets (instead of the value)