How To Show Formulas Instead of Values in Google Sheets?

Google Spreadsheet software is a great tool where we can create magic with our datasets. We use various formulas and functions to structure, organize, and perform mathematical operations in Google Sheets. But, when we are using functions or formulas in Google Sheets, we just the result not the formula or function used. However, the spreadsheet also allows us to see what formula or function is used in a particular cell with the help of its in-built feature.

In this article, let’s understand how to view formulas instead of values in spreadsheet cells with the help of Google Sheet tips provided on this page. Read on to find more.

Table of Contents

How to Show Formulas Instead of Values in Entire Google Sheets?

Let’s consider the following dataset. Here I have various formulas in each cell of the spreadsheet.

show-formulas-instead-of-values-in-google-sheets

 

Now follow the steps as listed below to show formulas instead of values:

  • Step 1: Open the Google Spreadsheet where you would like to show formulas instead of values.
  • Step 2: Click on the “View” tab in the menubar.

show-formulas-instead-of-values-in-google-sheets

  • Step 3: Now select “Show Formulas” from the drop-down menu.
  • Step 4: This will automatically show formulas instead of values in Google Sheets.

show-formulas-instead-of-values-in-google-sheets

This strategy can help you grasp what’s going on in a spreadsheet that you are unfamiliar with. Looking at someone else’s work is also a great approach to learn how to use formulas and analysis tools.

This view is very useful when analyzing a workbook that appears to be returning incorrect values. This allows you to see all of the formulas at once rather than having to click on each cell to figure out what’s going on.

In order to convert back formulas to values follow the steps as listed below:

  • Step 1: Hit the View tab in Google Sheets menubar.
  • Step 2: Now uncheck “Show Formulas” from the drop-down menu.

This will show values now instead of formulas.

show-formulas-instead-of-values-in-google-sheets

How to Show both Formula and Values at the Same Time?

In Google Sheets, we can see both formulas and values at the same time. To know how to do this, follow the steps as listed below:

  • Step 1: Select the cell where you would like to view the formula.
  • Step 2: Double click the cell to see the detailed functions of the cell.
  • Step 3: Click on the “?” icon next to the cell to bring up the pop-up value preview. Alternatively, you can also “F9” key to enable the pop-up value preview.

show-formulas-instead-of-values-in-google-sheets

These tricks come in handy when trying to understand what’s going on with a particular formula in the cell. So you can fix formulas and see all the information at once.

show-formulas-instead-of-values-in-google-sheets

How to Show Formula by Converting it to Text?

This method can be adopted if you want the values to display as formulas permanently.

In order to compel Sheets to show instead than process a formula, you only need to place the formula in apostrophe before it.

Google Sheets doesn’t include a right-click menu or other formatting choices to control cell formatting, unlike other cell formatting approaches. Changing the formula in the cell is the only option.

Now follow the steps listed below to show formulas by converting them to text.

  • Step 1: Select the cell where you would like to show formulas instead of values.
  • Step 2: Now simply add an apostrophe before the formula.
  • Step 3: Press the “Enter” button. The apostrophe will be hidden in Google Sheets and the only formula will be displayed as shown below.

show-formulas-instead-of-values-in-google-sheets

 

Your formula will be displayed instead of the value when the apostrophe symbol is used. Apostrophes in one cell will replace all cell contents with that cell’s value if you try to move the value across a range. When you add an apostrophe to a calculation, Sheets will not automatically modify the formulas.

How to Show formulas instead of values in Google Sheets using Keyboard Shortcut?

This is one of the easiest methods where you don’t have to execute multiple steps. The steps to achieve “shows formulas instead of values” are given below:

  • Step 1: Open the Google Spreadsheet.
  • Step 2: Now simply use the following keys on your keyboard ” Control,” and “Accent“. ( Ctrl + `)
  • Step 3: Press all the keys in one go. This will show formulas now instead of values in Google Sheets.

show formulas instead of values

Shows Formulas instead of Values by Clicking on Cell

This is one of the normal methods where you can simply show formulas instead of values. Just double click on the cell and the Google Sheets will show formulas instead of values. The limitation of this method is that we have to execute this step at each and every cell.

show-formulas-instead-of-values-in-google-sheets