Show Formula in Excel

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’ll explore further. Follow steps to find that option in excel.

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

Click on Formula 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. Select the formula cells range.

    Show Formula in Excel Step 1

  2. Now go to the formula tab and click on the Show Formulas option.

    Show Formula in Excel Step 1-1

  3. Once you click on that option, excel starts to show the formula instead of the formula results.

    Show Formula in Excel Step 1-2

Shortcut to Show Formula in Excel

shortcut to show formula in excel is Ctrl + `. The key (‘) is located on the keyboard just below the escape 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 show all the formulas at one by changing our default settings of the 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

This seems a lengthy process but very useful if you want to show the formula for multiple sheets. Making one sheet at a time is a lot of time-consuming processes. So we can rely on these settings options.

Print Formulas using Show Formula Option

We can print these formulas instead of the values by 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 shortcut Ctrl + ` ).
formulas - Show Formula

This would show up all the selected cell formulas.

Print formula using show formula - step 2
  • Step 2: Now select the printable region data and press ALT + P + R + S. This would set up the print area.
Print formula using show formula - step 3
  • Step 3: Now press Ctrl + P. This will print all the formulas. Now you can go back and press Ctrl + ` to remove the visible formulas option and make the values visible.
Print (Ctrl + P)

Hide Formulas in Excel

Not only can we show the formula; rather, we can hide those formulas to be visible from the 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 Cells.

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 formula and values after every alternative click.
  • Always study the formula and go back to formula values but never ever lose formulas.
  • Often when we are using shortcuts, we tend to type Ctrl + `. So do not panic; try typing Ctrl +`. This 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 has been a guide to Show Formula in excel. Here we discuss how to Show Formula in Excel and its shortcut along with practical examples. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion