Mehrab Imtiaz
While formulas are the backbone of any professional spreadsheet, I can sometimes be wise to replace formula with raw value in Google Sheets if nothing else but for presentation. And today, we are going to show you just that in this guide.
Let’s get started!
Steps to Replace Formula with Value in Google Sheets
To show our process, we will be using the following dataset:
It’s simple, really. We have three sets of scores with one column holding their average. It is the Average column that contains our formula.
Let’s say that we want to replace the formula in the Average column with just their values. The primary way of doing so is to simply copy and paste as value.
To demonstrate how it works, we have first copied (CTRL+C) cell E3 which contains the first AVERAGE formula.
Now let’s paste it in the cell on the right. But pasting it normally (CTRL+V) won’t do! You’ll simply be duplicating the same cell.
Instead, we will use the Paste Values Only option from Paste Special.
Just right-click on the target cell and navigate to Paste special then Values only.
Our paste values only result:
As you can see, the formula has not been carried over and only the value remains.
Now, to replace all the formulas in the Average column, simply copy the column…
…and paste with Values only over the existing values to replace:F
You may have also noticed the keyboard shortcut for Paste Values Only in our images above:
CTRL+SHIFT+V
This keyboard shortcut is sure to save you a lot of time when looking to replace formulas with values in Google Sheets for multiple instances.
Read More: How to Use Find and Replace in Column in Google Sheets
Scenarios Where You Might Need to Replace a Formula with its Value in Google Sheets
While not many, there are some scenarios where you may want to convert and/or replace formulas with values in Google Sheets. Here are a few:
- The need to present raw values without the risk of changing formulas during copy and paste.
- You are trying to share your dataset with another user, but you don’t want them to view or edit the formulas in your worksheet.
- You simply do not need the formulas in your dataset anymore. This may help speed up Google Sheets operations for spreadsheets that contain huge volumes of data, especially if the spreadsheet contains complex functions like INDIRECT.
- Your spreadsheet contains values from functions that generate random values (RAND or RANDBETWEEN) and you do not want the generated values to change when you update the spreadsheet.
Final Words
That concludes how and why we replace formula with values in Google Sheets. We hope that this simple guide comes in handy for your spreadsheet tasks.
Feel free to leave any queries or advice you might have for us in the comments section below.
Related Articles for Reading
- How to Find and Delete in Google Sheets (An Easy Guide)
- Use FIND Function in Google Sheets (5 Useful Examples)
- How to Find P-Value in Google Sheets (With Quick Steps)
- Find and Replace with Wildcard in Google Sheets
- How to Find the Range in Google Sheets (with Quick Steps)
- Find All Cells With Value in Google Sheets (An Easy Guide)
- How to Find Correlation Coefficient in Google Sheets
- Find Uncertainty of Slope in Google Sheets (3 Quick Steps)
