Show Formula in Excel

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

How to Show Formula in Excel?

In Excel, we have an option to show those formulas to study the relationship of the formula. There is also a keyboard shortcut to show formulas in Excel, which we will explore further. Follow the steps to find that option in Excel.

You can download this Show Formulas Excel Template here – Show Formulas Excel Template

Click on the “Formulas” tab, then “Formula Auditing” and “Show Formulas.”

Show Formula in Excel 1

Once we click on those options, all the formulas in the Excel sheet will be visible instead of the values of the formula.

If you again click on that option, Excel will start to show the values instead of the formula itself.

Below is an example of that showing formulas.

Below are the steps of showing formulas:

  1. First, select the formula cell range.


    Show Formula in Excel Step 1

  2. Now, go to the “Formulas” tab and click on the “Show Formulas” option.


    Show Formula in Excel Step 1-1

  3. Once you click on that option, Excel shows the formula instead of the formula results.


    Show Formula in Excel Step 1-2

Shortcut to Show Formula in Excel

The shortcut to show the formula in Excel is “Ctrl + `.” The key (‘) is on the keyboard below the “Esc” button.

Show Formula in Excel Step 1-3

How to Enable Show Formula in Workbook Option?

Showing formulas is not a workbook-level option. But, we can offer all the formulas at once by changing our default settings of Excel.

Follow the below steps to enable this option.

  • Step 1: Go to the “File” option in Excel.
Show Formula in Excel Step 2
  • Step 2: Now, click on “Options.”
Show Formula in Excel Step 2-1
  • Step 3: Go to the “Advanced” option.
Show Formula in Excel Step 2-2
  • Step 4: Scroll down and find “Show formulas in cells instead of their calculated results.”
Excel Options

It seems a long process but is very useful if you want to show multiple sheets’ formulas. However, making one sheet at a time is a very time-consuming process. So, we can rely on these settings options.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

We can print these formulas instead of the values using this formula option.

  • Step 1: Select the formula cells (if you need particular cells to be displayed, then select those cells only) and click on this formula option. (We can use the shortcut Ctrl + ` ).
formulas - Show Formula

It would show up all the selected cell formulas.

Print formula using show formula - step 2
  • Step 2: Select the printable region data and press the “ALT + P + R + S” shortcut keys. It would set up the print area.
Print formula using show formula - step 3
  • Step 3: Now, press the “Ctrl + P” keys. It will print all the formulas. Now, you can return and press “Ctrl + `” to remove the visible formulas option and make the values visible.
Print (Ctrl + P)

Hide Formulas in Excel

We cannot only show the formula but also hide those formulas to be visible to other users by protecting the sheet.

  • Step 1: Select the formula cells.
Hide formula using Show formula step 1

Step 2: Right-click and select “Format CellsFormat CellsFormatting cells is an important technique to master because it makes any data presentable, crisp, and in the user's preferred format. The formatting of the cell depends upon the nature of the data present.read more.”

Right-click and select Format Cells
  • Step 3: Select the “Protection” tab and check the “Hidden” box.
Select Protection tab & check Hidden box
  • Step 4: Now, click on the “Review” tab and select “Protect Sheet.”
Review tab and select Protect Sheet
  • Step 5: Type the password and protect the sheet.
Protect Sheet

Things to Remember

  • Since “Ctrl + `” is a toggle option, we can show formulas and values after every alternative click.
  • We must always study the formula and return to formula values but never lose formulas.
  • We often type “Ctrl + `” when using shortcuts. So, do not panic; try typing “Ctrl +`.It will give back your values.
  • If the format of the cell is “TEXT,” then it may show the formula in Excel rather than the values of the formula. Apply “General” formatting to show the values of the formula.

Recommended Articles

This article has been a guide to Show Formula in Excel. Here, we discuss how to show the formula in Excel and its shortcut, along with practical examples. You may learn more about Excel from the following articles: –