Google Sheets has countless built-in features that automate and streamline the most common use-cases. However, when you want to do something like show a formula instead of the formula result or value, the automation can make it a little tricky.
Fortunately, Google Sheets has several techniques to force a cell to show the formula information in it instead of the value.
In this tutorial, we will show you a couple of methods you can use to show formulas in Google Sheets (instead of the value) in the entire worksheet or in specific cells.
Show Formulas In the Entire Worksheet
Google Sheets has a built-in “View” option that toggles all cells between showing values and formulas.
It is helpful when you want to switch between the two to check on all formulas, but it doesn’t work for all use-cases.
For our examples, we’ll be using a straight-forward spreadsheet that analyzes the totals of three customers buying three numbered products.
This spreadsheet only uses the =sum() formula for adding cell values.
We’re using formulas in cells E2-E4, B5-D5, and E7.
To show the formulas instead of the values, follow these steps:
- Open “View” from the menu bar.
- Choose “Show formulas” from the option list. This option will appear for any spreadsheet that is using at least one formula.
Now the active spreadsheet will display formulas in cells instead of values. The results look like this:
You can switch the view back to “show values” by selecting the same option from the menu again.
This method can make it easier to understand what’s going on with an unfamiliar spreadsheet. It’s also a neat way to learn how to use formulas and analysis techniques by looking at someone else’s work.
This view is particularly useful when you’re troubleshooting a workbook that’s returning values that appear incorrect. This makes it easy to see all the formulas at once instead of clicking on each cell to investigate what’s going on.
Show Formulas View Keyboard Shortcut
Instead of using the menu, you can toggle the “Show formulas” view with a keyboard shortcut. This keyboard shortcut is universal and works the same on all operating systems.
Activate this shortcut by pressing “Control,” “Plus,” and “Accent” at the same time (Ctrl + `).
Note for unfamiliar users: You can find the accent key at the top left of the keyboard.
Clicking on Cells
If you just need to reference a formula without changing the view for all cells with formulas, just click on the cell. This will display the formula in the formula bar.
Preview the Results and View the Formula Simultaneously for a Selected Cell
This helpful trick gives you full insight into what’s going on with a formula cell. It lets you view the formula, the value, and the cells the formula is looking at all at once.
To enable this view, follow these steps.
- Select the cell you want to investigate.
- Double click on the cell to turn on the detailed view
- Click the “?” icon next to the cell or press the “F9” key to enable the value preview pop-up.
This trick can be very useful when you’re trying to get a deeper look at what’s going on with a formula in a cell. This puts all the information on the screen at once which is helpful when troubleshooting formulas.
Show Formula by Converting it into Text (Apostrophe Method)
If you want Google Sheets to permanently display a formula instead of a value for a specific cell, this trick is for you.
All you need to do to force Sheets to display the formula in a cell instead of processing the formula is to add an apostrophe before the formula.
Unlike other cell formatting methods, Google Sheets doesn’t control it through a right-click menu or other formatting options. You must change the formula itself in the cell.
In the case of our example, we’re changing the formula in cell E7 from “ =sum(E2:E4) ” to “ ‘=sum(E2:E4) “ so it will format to show the formula instead of the value.
Google Sheets will hide the apostrophe from the display and just show the formula.
After you’ve added the apostrophe, the cell will show the formula like this:
Note: You need to add the apostrophe to each cell you want to display the formula instead of the value. If you put the apostrophe in one cell and try to drag the value across a range, Sheets will replace all cell contents with the value of the selected cell. Sheets will not adjust the formulas automatically when you’ve added the apostrophe.
These techniques can assist you in formatting your Google Sheets spreadsheets to show formulas instead of values in different ways.
I hope you found this tutorial useful!
Other tutorials you may like: