6 Reasons Why Excel Shows The Formula Instead of Result

Are you wondering why your Excel displays the formula instead of the result? Don’t worry, you’ve come to the right place!

This can be frustrating, especially when working on complex calculations. In this article, I’ll discuss seven reasons why this might happen and how to resolve them.

Let’s get started!

6 Reasons Why Excel Shows the Formulas Instead of Results (And How to Fix Them!)

Before we dive in, it’s essential to have a solid understanding of Excel formulas and functions. You’ll be better equipped to tackle any Excel issues with a strong foundation.

1. Check the Show Formulas Mode

One main reason for displaying formulas instead of results is the “Show Formulas” mode.

Look for the “Formulas” tab on the top ribbon to check if Show Formulas mode is on. If the “Show Formulas” button is highlighted, it is active.

To toggle the mode, click the “Show Formulas” button or use the keyboard shortcut Ctrl + ` (the grave accent key).

When the Show Formulas mode is activated, Excel displays all formulas used in the sheet instead of the calculation results.

You will also notice that the columns in your worksheet will be wider than usual. This helps expose all the underlying formulas.

2. Issues with Cell Formatting

Another common reason formulas show up is that the cell is formatted as text. When this happens, Excel treats formulas like simple text and won’t calculate the result.

For instance, you enter =2+2. Instead of showing 4, the cell still displays =2+2.

It’s because cells formatted as text don’t process the formulas. Instead, they simply store and display the input as-is, preventing calculations.

Here’s the quick fix:

1. Select the problematic cell with the formula.
2. Go to the Home tab.
3. Locate the Number section.
4. Use the Number Format dropdown.
5. Choose the appropriate format, such as Number, Currency, or Accounting.

Alternatively, press Ctrl+1 to open the “Format Cells” dialog and update the “Number” category as appropriate.

Now, Excel should evaluate the formula and display the result correctly!

3. Apostrophe In The Formula

=A1+B1 is entered as ‘=A1+B1. Adding a single apostrophe by accident makes Excel think it’s plain text. So, the formula won’t calculate.

Simply delete the leading apostrophe before the formula and press Enter! Excel will now recognize it as a formula.

4. Spreadsheet Protection

Spreadsheet protection can interfere with formula evaluation. Here are some ways to check for and remove the protection.

To check if your sheet is protected, navigate to the Review tab and see if the Unprotect Sheet option is active. If it is, this means your sheet is protected. To remove the protection, simply click on Unprotect Sheet.

On the other hand, if your workbook is protected instead of a single sheet, you need to check for workbook protection. The process is the same – Go to the Review tab again and check if the Protect Workbook option is active. If it is, click on it to remove the protection.

With protection removed, your formulas should now display the correct results. Remember to lock your sheets or workbooks again after your edits if needed.

5. Dealing with Circular References

A circular reference occurs when a formula in a cell uses its own cell reference to calculate – effectively creating an endless loop. This can prevent Excel from showing the formula result.

Excel provides an error-checking tool to help find and fix circular references in your workbook. Go to the ‘Formulas‘ tab and click ‘Error Checking‘ to access this.

Resolve circular references by modifying your formulas and preventing the endless loop. This will allow Excel to display the result correctly.

6. External References and Links

Sometimes, Excel shows the formula instead of the results due to external references. This issue may occur when your formula tries to reference data from another workbook.

To prevent this, Excel has an ‘Edit Links’ feature. This ensures the data is updated before the calculation takes place. The ‘Edit Links’ option is in the ‘Data’ tab.

Broken links can also display the formula instead of the result. This happens when the external workbook is missing or moved to a new location. Correct the broken link or break the external reference entirely to fix this issue.

This is why I prefer importing data into a consolidated workbook instead of having multiple external references!

Helpful Tools and Features

Formula Debugging

One valuable trick is using the F9 key to debug parts of the formula.

By highlighting a section of your formula and pressing F9, you can see the result of that part. This helps you pinpoint any issues with specific parts of your formula.

Formula Auditing Tools

Excel offers a range of formula auditing tools to help diagnose and resolve formula display and evaluation issues.

For example, the “Trace Precedents” and “Trace Dependents” options visualize cell relationships. These options are under the “Formulas” tab in the “Formula Auditing” group.

Evaluate Formula Dialog

The “Evaluate Formula” dialog in Excel shows you the step-by-step evaluation process for any selected formula.

Similar to the above, go to the “Formulas” tab. Click on “Evaluate Formula,” and the dialog box will open, allowing you to review calculation results and locate any problems.

If the first six fixes didn’t help you, then hopefully, utilizing these tools and features will help you identify and fix any issues when Excel shows the formula instead of the result!

Additional FAQs