How to Show Formulas in Excel (4 Easy Ways)

Here is an overview of showing formulas in Excel.

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.

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

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.

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.

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

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.

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.

=FORMULATEXT(E5)

Next, copy the formula by the Fill Handle feature to the lower cells to show all the formulas.

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.

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

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

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.

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

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.

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

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.

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.

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.

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!