Convert formulas to values in Google Sheets
In this article, you will learn about two ways of replacing all formulas with their results in a spreadsheet.
There are some cases when you may need to have the calculated values instead of their formulas:
- To transfer data between sheets or even spreadsheets quickly.
- To keep formulas from recalculating if cells they refer to change.
- To save the results of the RAND function.
- To speed up your spreadsheet performance especially if there are lots of complex formulas.
- To prevent others from peeping at your calculations when you share or send your spreadsheets (for example, to not give away any extra charges or sales you may offer as a seller).
Thus, today I offer you two options for making this possible.
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 value only.
The quickest way to convert formulas to values in your spreadsheet
If you'd rather avoid stumbling over incorrect buttons, we've got you covered. Our Power Tools - a collection of 20+ 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:
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.
4 Responses to "Convert formulas to values in Google Sheets"
Hi. Is there a way to convert formulas to values automatically when data is entered for the first time rather than having to manually copy and paste or run power tool? Thanks in advance.
Hi Claire,
I'm afraid our tools cannot be run automatically. You need to start Power Tools whenever you want to convert formulas to values.
However, there's a way to do that faster.
Once you go through Convert formulas to values, return to the main Power Tools window where there are Recent and Favorite tools at the bottom. Recent tools will feature the last action - Convert formula to value.
Hover the mouse over it and when the star icon appears, click on it. The operation will be added to the Favorite tools tab.
The next time you run the add-on, just initiate the conversion in one click from that tab.
Hope this will ease your task.
Hi Claire,
I have a VLOOKUP that returns a value from another spreadsheet (not tab) if a matching value to the VLOOKUP exists. Pretty standard.
However; if no match exists the result is the cell gets filled in with #N/A
This pretty much ruins my ability to perform any more calculations on that column.
So, do you know of a way to change #N/A to a number?
If not, can we stop the result from displaying #N/A?
If not, is there a way to create and if statement using VLOOKUP as the logical_expression in an if statement and as the value if true and using "0" if false?
I love the powertools! great for repeating tasks and quick hits.
Rich
Hi Richard,
You will find ways of replacing VLOOKUP errors with your own text/number in this article:
https://www.ablebits.com/office-addins-blog/2018/05/17/fix-vlookup-errors-google-sheets/#hide-errors
Thank you for your feedback on Power Tools! :) We'd appreciate if you could leave it in the store as well:
https://gsuite.google.com/marketplace/app/power_tools/1058867473888