How To Show Formulas In Excel

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.

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:

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.

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

You can display formulas in Excel in one or some selected cells only. If you want to display formulas instead of values in just one cell, select the cell and go through the home tab > Find & select > Replace (Ctrl + H). In the Replace window, enter = in the “Find what” field, and enter ‘= in the “Replace with” field and click on the Replace button.

If you want to display formulas in multiple cells, select all the cells and go through the home tab > Find & select > Replace (Ctrl + H). In the Replace window, enter = in the “Find what” field and then enter ‘= in the “Replace with” field, finally click on the Replace All button. Now all the formulas in all the selected cells will be displayed.

How to Print Formulas in Excel

If you need to print your Excel spreadsheet which contains the formulas instead of the values, you just need to follow one of the mentioned methods. Since the formulas are visible, you can also check them in your printed spreadsheet. To print a spreadsheet, go to the File option and click Print.

This tutorial taught us how to show formulas in excel and found out every process behind the results.