In this article, you will learn about two ways of replacing all formulas with their results in a spreadsheet.
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.