How do you stop formulas from changing in google sheets?

In this article, you will learn about two ways of replacing all formulas with their results in a spreadsheet.

Table of Contents Show

Classic way to replace formulas with values in Google Sheets
You may also be interested in
How do I keep a formula constant in Google Sheets?
How do you keep the same formula when copying in Google Sheets?
How do you keep a cell from changing formulas?

Whether you need to transfer data between sheets or even spreadsheets, keep formulas from recalculating (for example, the RAND function), or simply speed up your spreadsheet performance, having the calculated values instead of their formulas will help.

Today I offer you two options to make this possible: the standard and the fastest.

Classic way to replace formulas with values in Google Sheets

Let's imagine you have a list of web pages and you use a special function to pull the domain names from those long links:

Now you need to switch all formulas to results instead. Here's what you can do:

Highlight all cells you need to modify.
Take all formulas to the clipboard by pressing Ctrl+C on your keyboard.
Then press Ctrl+Shift+V to paste back the values only:

Tip. Ctrl+Shift+V is the Google Sheets shortcut for Paste values only (right-click a cell > Paste special > Paste values only).

If you'd rather avoid stumbling over incorrect buttons, we've got you covered. Our Power Tools – a collection of 30+ add-ons for Google Sheets – contains a perfect assistant.

Run the collection from Add-ons > Power Tools > Start and click the Formulas icon:

Tip. To run the Formulas tool right away, go to Add-ons > Power Tools > Formulas.
Select all cells you want to alter and choose Convert formulas to values:
Hit Run and voila – all formulas are replaced in a click:

Tip. You can repeat this action even quicker from the main Power Tools window.
Once you convert formulas to values, this action will appear in the Recent tools tab at the bottom of the main window. Click there to run the tool again or star it to add it to your Favorite tools for future use:

I highly recommend you to try other add-ons from Power Tools: 5 minutes saved here and 15 there may become a game changer in your work efficiency.

You may also be interested in

Simplified scenario:

Sheet Customer_Orders, has blocks of rows with each row having product code, count ordered, and size. Bunch of other stuff is looked up/calculated on the basis of these three tidbits. By the end of the season this sheet has about 5000 rows.

Sheet Raw_Inventory has start of year in the first 500 rows, and then does a query to Customer_orders. By season end this sheet has about 2000 rows.

Near as I can tell, this query runs every time I change one of the 3 fields in Cust_Orders.

Sheet Inv_Status is a pivot table that runs against Raw_Inventory, and again, I think that every time Raw_Inventory is modified, the pivot table is recalculated. (There are a couple of other pivot tables that use the same data.)

The result is that making a change on Cust_Orders can result in up to 2 minutes while the calculations catch up.

(Hardware: Mac Pro, 24 GB ram, 3.2 GHz, 4 core; Current version of Chrome running under Yosemite)

What I would like to do is one of the following:

Lengthen the time between updates.
Be able to recalculate sheet Raw_Inventory manually.

A partial workaround:

I've created a new sheet that imports raw_Inventory. This copy is used for the pivot table. ImportRange only runs every 30 minutes.

The next step will replace the query with 1 zillion simple assignment statements. I'm hoping that this will replace querying 3000 lines with querying a single line when I make a change in Cust_Orders.

How do I keep a formula constant in Google Sheets?

You can use an absolute reference to keep a row and/or column constant in the formula. An absolute reference is designated in the formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both.

How do you keep the same formula when copying in Google Sheets?

To copy and paste the exact formula without changing the cell references to another place in your sheet, you need to convert the formulas to text and then copy them. To do that, select the range with formulas you want to copy. Then, in the Ribbon, go to Home > Editing > Find & Select > Replace. 3.

How do you keep a cell from changing formulas?

If you want to maintain the original cell reference when you copy it, you "lock" it by putting a dollar sign ($) before the cell and column references. For example, when you copy the formula =$A$2+$B$2 from C2 to D2, the formula stays exactly the same. This is an absolute reference.