Easy Guide to Replace Formula with Value in Google Sheets

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:

dataset to use to replace formula with value in google sheets

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.

copied cells have dotted borders

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.

navigating paste special to values only

Our paste values only result:

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…

copying the column with formula

…and paste with Values only over the existing values to replace:F

replace formula with value in google sheets animated

You may have also noticed the keyboard shortcut for Paste Values Only in our images above:


keyboard shortcut for paste values only

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:

  1. The need to present raw values without the risk of changing formulas during copy and paste.
  2. 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.
  3. 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.
  4. 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

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply