Make Google Sheets Show Formulas Instead of Values [Simple]

By

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

Once you learn How to Put a Formula in Google Sheets sometimes you need to view them instead of the results.

Luckily, Google Sheets has a built-in “View” option that toggles all cells to display the formulas in the worksheet or not.

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.

Dataset with formulas in cells

We’re using formulas in cells E2-E4, B5-D5, and E7.

To show the formulas instead of the values, follow these steps:

  1. Open “View” from the menu bar.
  2. Choose “Show formulas” from the option list. This option will appear for any spreadsheet that is using at least one formula.

Click the view tab and then click on show formulas

Now the active spreadsheet will display formulas in cells instead of values. The results look like this:

Cells with formulas showing formulas

If Google Sheets is showing the formula instead of the result or value, 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.

Read also: How to Convert Formula to Value in Google Sheets

How to See Formulas in Google Sheets with a 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.

How to Display Equations on Google Sheets by 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.

Formula visible 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.

  1. Select the cell you want to investigate.
  2. Double click on the cell to turn on the detailed view
  3. Click the “?” icon next to the cell or press the “F9” key to enable the value preview pop-up. Click the “” icon next to the cell

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.

How to Show Equation in Google Sheets 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.

Add an apostrophe before the formula

After you’ve added the apostrophe, the cell will show the formula like this:

Apostrophe shows formula instead of the value

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.

Google Sheets Show Formulas FAQ

How Do You Show Formulas in Google Sheets?

The quickest and easiest way to show formulas in Google Sheets is to use the keyboard shortcut CTRL + ‘

Why Is My Formula Not Showing In Google Sheets?

By default, Google Sheets shows results instead of formulas. You can change this by navigating to View > Show formulas in the menu.

How Do I Turn On Formula Preview in Google Sheets?

You can use the keyboard shortcut CTRL + ‘ to toggle the Google Formula preview on and off.

How Do I Turn On Formula Suggestions in Google Sheets?

To turn on formula suggestions in Google Sheets, navigate to Tools > Enable formula suggestions.

How Do You Fix Formulas in Google Sheets?

You can check for errors in your formulas by using the CTRL + ‘ keyboard shortcut

Can I Show All the Formulas in Google Sheets in Cells Instead of Just the Formula Bar?

Yes, you can show all the formulas in Google Sheets cells by heading to View > Show formulas and clicking to turn the setting on.

How to Show Formula in Google Sheets

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! Now you know the answer to “Can Google Sheets show formulas instead of values” and a few different ways to achieve the goal. Let in know in the comments if you have any questions.

 

Popular Posts