How To Show Formulas In Excel

3.7 min read|Last Updated: January 8th, 2024|Categories: excel|
table of content

In this article, we will discuss how to display formulas in Excel with some simple methods. When we are working on an Excel spreadsheet and formulas, sometimes we need to know which formulas are used in each cell. Excel will automatically hide formulas and just show the results. Here we learn how to show formulas in Excel instead of the values.

How to Display Formulas in Excel

There are many methods to display formulas in Excel. We will show you how to check the formulas with the help of some simple and quick ways.

1. Show Formulas Option on the Excel Ribbon

You can show formulas using the Excel ribbon. To do this, you can choose the “Show Formulas” item. First, select the cell which contains the result, then go through the Formulas tab within the Formula Auditing range and choose the “Show Formulas” item. This option will display the formula instantly.

show formula option on the Excel Ribbon in formula tab to show formulas in excel

2. Check the “Show Formulas in Cells Instead of Their Calculated Results” Option.

This is another simple way that helps us discover what the formula each cell contains. If you are using Excel 2010, Excel 2013 and Excel 2016, You just need to click on “File” and select “options.” In the advanced option, find “Display options for this worksheet.” Then check the “Show formulas in cells instead of their calculated results” box and click “OK.”

Now all formulas will appear within their cells instead of the results.



3. Using Shortcut to Display Formulas

If you are looking for the most simple method of converting results to formulas, the shortcut is the best. Select the cell which contains the result of the formula and press “Ctrl + `” to make the formula visible. You can find “`” which is called “grave accent” key in the top left corner on your keyboard, which you see in the image below:

using grave accent key on keyboard to show formula in excel

You can switch to results again by pressing “Ctrl + `” one more time.

If you wish to display formulas in Excel, follow one of the above methods, and consider some points:

  • Note that you need to repeat each of these methods on every single sheet to show formulas.
  • If you follow the instructions above, but the formulas will not appear, it means that the worksheet has been protected.

In that case you need to unprotect the sheet and here is how it’s done.

Click on the “Review” tab and press the “Unprotect Sheet” option. Then enter the sheet’s password. Now all hidden formulas are displayed.

unprotect sheet option to show formulas in excel

If you need the formulas to be displayed after protecting the sheet, go to the “Format Cells” option by pressing “Ctrl + 1”, and in the “Protection” tab, uncheck the “Hidden” checkbox. Now all formulas on this sheet are displayed after password protecting the sheet.



How to Display Formulas in Excel in Selected Cells Only