How to Show Formulas in Excel (4 Easy Ways)

Here is an overview of showing formulas in Excel.

Show Excel Formulas

How to Show Formulas in Excel?

There are a few ways you can show formulas in Excel.

Method 1 – Show Formulas Using Show Formulas Feature from Excel Ribbon

Here, the Full Name column contains formulas that join first and last names. To show the formulas:

  • Go to the Formulas tab and click on the Show Formulas button from the Formula Auditing group.

Applying Show Formulas Feature

  • This command will show all the formulas in your Excel sheet.
  • If you click on this button again, the formula results will appear again.

Formulas Shown in the Data Table

Thus, you can show the formulas in an Excel sheet.

Method 2 – Show Formulas by Enabling Proper Options from Excel Options Features

We can also show formulas in a sheet by enabling the “Show formulas in cells instead of their calculated results” command from the Options feature. The following picture shows the cells containing formula results and sheet names.

Showing Sheet and Cells Containing Formulas

  • Open the Excel Options window from the File tab.
  • Select the Advanced button and scroll to Display options for this worksheet.
  • Select the worksheet in which you want to show formulas from the dropdown menu.
  • Check “Show formulas in cells instead of their calculated results”.
  • Click OK.

Enabling Formulas to Show in Cells Instead of Results

  • You will see the formulas of the corresponding results in the desired sheet.

Formulas Shown in the Data Table (2)

The advantage of this feature is, that when you have a huge number of sheets in the workbook, you don’t have to manually search for the one where you want to show formulas. Search from the drop-down list and apply the feature to show formulas.

Method 3 – Show Formulas Using Keyboard Shortcut

  • Press Ctrl + ` (tilde symbol above the Tab key) and the formulas in the sheet will be displayed. You can toggle back this command by pressing Ctrl + ` again.

Keyboard Shortcut to Show Formulas

Read More: How to Show All Formulas in Excel

Method 3 – Display Formulas Using FORMULATEXT Function

You can also use the FORMULATEXT function to show the formula used in a sheet.

  • Create a new column to store the formulas.
  • Copy the following formula beside the corresponding formula result. Use the cell reference of the cell containing the formula in the FORMULATEXT argument.
  • Next, copy the formula by the Fill Handle feature to the lower cells to show all the formulas.

Using FORMULATEXT Function

Read More: How to Display Cell Formulas in Excel

How to Highlight Formulas in Excel?

  • Use the Find & Select feature to select them, which will highlight the cells with formula. Follow the video below to understand how this feature works.

[Fixed!] What to Do If Excel Is Showing Formulas Not Results?

Fix 1- Removing Spaces or Single Quote Symbol Before Formulas

Sometimes, we may put spaces or a single quote symbol or apostrophe (‘) before a formula. This is the reason we see only the formulas, not the formula results.

Here is an image showing that the presence of space causes formula results not to appear in the cell.

Presence of Space Before Formula

The presence of a single quote before the formula also causes a similar issue.

Presence of Single Quote Before Formula

By removing them, we can get the formula results easily.

Removing Space and Single Quote Before Formula to Fix Formula Result Issue

Thus, you can see the formula results in your Excel sheet.

Fix 2 – Converting Cell Formatting from Text to Other

If your cell is converted to Text before inserting a formula in it, you won’t get the formula result after entering the formula. The following image illustrates this fact.

Cell Formatted to Text Don't Show Formula Result

Convert the cell format to another format and enter the formula again. Here, we set the format to General and got the formula results.

Converting Format to General Shows Formula Result

How to Hide Formulas in Excel?

  • Select the cells with formulas first and right-click on them to open the Context Menu.
  • Select Format Cells. You can also press Ctrl + 1 to open the Format Cells dialog box.

Formatting Cells with Formulas

  • Select the Protection tab of the Format Cells
  • Check the Hidden option and click OK.

Selecting Option to Hide Formula

  • Go to Review and select Protect Sheet. Make sure the cells with formulas are selected.
  • The Protect Sheet window will appear. By default, the first three check boxes in that window are checked. Keep them checked and input a password.
  • Click OK.

Applying Protect Sheet Feature

  • Another dialog box will pop up to confirm the password. After confirming that, you will see that the cells with formulas contain values only. You cannot make any changes or delete those cells.

Formula Hidden from Protected Sheet

  • You can edit the cells if you need by unprotecting the sheet. After protecting the sheet, the Unprotect Sheet feature is automatically added to the Review tab in the same ribbon. Just click on it, provide the password and your sheet becomes editable like before.

How to Print Formulas in Excel?

  • Follow one of the methods that we described previously to show formulas.
  • Go to File and choose Print. You can also open the Print feature by pressing Ctrl + P.
  • Choose your desired formatting of the printing page and click on Print.

Printing Formulas in a Sheet

Read More: How to Show Formulas When Printing in Excel

What Do You Have to Keep in Mind?

  • Do not use the keyboard shortcut Ctrl + ` as a hotkey for other applications.
  • The cell formatting should not be “Text” before inserting formulas in them.

Frequently Asked Questions

1. Will showing formulas affect the functionality or integrity of my Excel workbook?

No, showing formulas does not affect the functionality or integrity of your workbook. It only changes the way formulas are displayed on the screen. The actual calculations and results remain intact.

2. Is there a way to show formulas in Excel temporarily, without permanently changing the display?

Yes, you may use Excel’s Evaluate Formula tool to briefly display a formula’s step-by-step evaluation. This feature can be accessed by heading to the Formulas tab, clicking on Evaluate Formula in the Formula Auditing group, and then following the prompts to move through the calculation procedure.

Show Excel Formulas: Knowledge Hub

<< Go Back To Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF