Show Formula in Excel

Last Updated :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

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.

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

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:

First, select the formula cell range.
Now, go to the "Formulas" tab and click on the "Show Formulas" option.
Once you click on that option, Excel shows the formula instead of the formula results.

How to Show Formula in Excel?

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.

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.

Step 2: Now, click on "Options."

Step 3: Go to the "Advanced" option.

Step 4: Scroll down and find "Show formulas in cells instead of their calculated results."

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.

Print Formulas using Show Formula Option

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 + ` ).

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.

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.

Step 2: 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."

Step 5: Type the password and protect the 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.