In this article, you will see how to show formulas in Google Sheets.

Why will you want to see a formula in your spreadsheet? There are various reasons.

You may have gotten a file from someone else and need to inspect and familiarize yourself with what formulas they’ve used, and how they’ve used them. For this, you will need the spreadsheet to show you the formulas.

Another reason could be that you simply just want to view or edit a formula you wrote. This can be a great aid in reviewing the logic used in your formulas and can help avoid errors.

When you run into any of these situations, how do you view the formulas? Get your copy of the example workbook and read on to find out!

Show Formula in the Formula Bar

This method is the most basic process of all when you want to view a formula in your spreadsheet. You only have to select the cell containing the formula you want to see and then look at the formula bar.

The average of these numbers was calculated and the answer is provided in cell A6. If you want to see the formula used to get the result, just select cell A7 and look in the formula bar.

Now you can see that the AVERAGE function has been used to calculate the average of the cell range A2:A6.

Make sure the formula bar is enabled. Go to the View tab ➜ select Show from the options ➜ make sure the Formula bar option is checked.

Show Formulas from the View Menu

Here, various calculations have been done on the same set of numbers. From their column headers, it’s obvious that different formulas have been applied to get their results.

Trying to view all the formulas from the formula bar will be tedious. There is a better option to display all the formulas at the same time.

Not only will you know what formulas were used, but the formulas will be displayed inside the cell which contains them. It’s a much better alternative when your sheet contains several formulas that you would like to see.

Here’s how you can use the View menu to show formulas.

1. Go to the View menu.
2. Select Show from the options.
3. Select the Formulae option from the submenu.

By default, the Formulae option is deactivated. When you click on Formulae, you will activate the option and it will have a check next to the option in the View menu.

As soon as you do this, all the formulas in the sheet will become visible inside the cells where they are applied.

You must know that activating the Formulae option is a setting that alters the display of formula across the spreadsheet.

Not only will the formulas appear on the particular sheet within which you’re working, but also all the formulas in the spreadsheet will come into view.

Until you deactivate the setting, you will only see only the formulas and not the values they produce.

To stop seeing the formulas, simply follow the steps described earlier and click on Formulae to deactivate it.

Show Formulas with a Keyboard Shortcut

Instead of going through the View menu to activate the Formulae setting, you can use this keyboard shortcut to achieve the same result.

Ctrl + `

The formulas will remain in view until this option is turned off and you can use the same keyboard shortcut again to turn it off!

Show Formulas with Apostrophe

Another method you can use to show formulas in your spreadsheet is to add an apostrophe ' in front of the formula as seen above.

When you do this and you press Enter, it forces the spreadsheet to display the formula instead of the values.

The cell displays the formula just as it will when you use the View menu option, but In the formula bar, you will see the apostrophe displayed.

If you want to stop seeing the formula, simply remove the apostrophe in front of the formula.

Show Formulas with the FORMULATEXT Function

It turns out there is actually a function you can use to show you the formula contained in a cell. Sounds interesting, right?

The FORMULATEXT function can be used to return the formula from any cell as a text string.

= FORMULATEXT ( cell )

• cell is the cell which contains a formula.

The FORMULATEXT function checks for formulas in a cell, and if it finds one, it will display it as text.

= FORMULATEXT ( A7 )
= FORMULATEXT ( C7 )
= FORMULATEXT ( E7 )
= FORMULATEXT ( G7 )

For example, to see the formulas in the above example use the above formulas.

Using the FORMULATEXT function requires that you know the cells where the formula has been applied.

Compared to the View menu option where all formulas in the spreadsheet are shown, the FORMULATEXT function only shows the formula for the cell referenced.

Show Formulas with Apps Script

Viewing formulas on your spreadsheet can be made much simpler with an apps script.

This way you can create a button inside the sheet to toggle on and off the formula view.

Go to the Extensions menu and select Apps Script to open the script editor.

function ShowFormulas(testCheck) {

  var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("WITH APPS SCRIPT")
  var myRange = mySheet.getRange("A9:G9")
  var myData = myRange.getFormulas();

  if (testCheck) {
    var myData = myRange.getFormulas();
  } else {
    var myData = myRange.getValues();
  };

  for (var r = 0; r < myData.length; r++) {
    for (var i = 0; i < myData[r].length; i++) {
      if (testCheck) {
        tempVal = myData[r][i].toString().replace("=", "'=");
      } else {
        tempVal = myData[r][i].toString().replace("'=", "=");
      };
      myData[r][i] = tempVal;
    }
  }
  myRange.setValues(myData);
}

function onEdit(e) {

  var mySheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var myRange = e.range;
  var mySheetName = mySheet.getSheetName();
  var currRow = myRange.getRow();
  var currCol = myRange.getColumn();
  var myCheck = myRange.getValue()

  if (
    mySheetName === 'WITH APPS SCRIPT'
    && currRow === 1
    && currCol === 1
  ) {
    ShowFormulas(myCheck)
  };
};

Copy the script above and click on Save. Then refresh your spreadsheet.

The script allows you to use the checkbox located in cell A1 to turn on and turn off the formula display inside the sheet named WITH APPS SCRIPTS.

When the checkbox is ticked, this triggers the onEdit function in the script to find and display the formulas behind the values in the specified cell ranges.

When the checkbox is unchecked and the value is set to false, this makes the cells display the values instead of the formulas.

The script relies on replacing the = character with '= in order to show the formula, and then replaces '= with = in order to hide the formula.

If you want to make the script adaptable and suitable for your projects, you can change a few parameters.

You can change the sheet name WITH APPS SCRIPT to reflect your sheet name, or you might need to change the cell range A9:G9. This script will only work for items within this range.

Considering that your project will contain data spanning longer ranges, it’ll be handy to use ranges that reflect the data requirement.

With these little alterations to the script, you should be able to apply it to any project on which you’re working.

Conclusion

While the number of methods available for achieving a formula view in the spreadsheets may appear more than necessary, they cover most situations.

If you just want to see the formula in a single cell? Simply select the cell and look at the formula bar!

The View menu or keyboard shortcut options will display all the formulas in your entire spreadsheet, which will be a good option to use when you need to see all formulas.

For situations where you only want to view formulas in a specific range, using the FORMULATEXT or Apps Script method be more suitable.

What method do you use the most when investigating your formula? Let me know in the comments below!