Excel Showing Formula Instead of Result (8 Reasons)

Often we face a common problem that Excel is showing the formula where we need the result.

Instead of this

Excel Showing Formula Instead of Result

Excel is showing this

Excel is showing this

Today we are going to know about the reasons behind this problem.


Practice Workbook

Download the following workbook and exercise.


8 Reasons for Showing Formula Instead of Result in Excel

 1. Using Space Before Equal Sign for Showing Formula Instead of Result

Sometimes we put a space before the equal sign by mistake. There is a rule that all formulas must start with an equal sign and put a space before it, which violates that rule. Here we have a dataset and it’s not showing the result value as we use a space before it.

Using Space Before Equal Sign for Showing Formula Instead of Result

Omitting spaces before the Equal signs of the formulas will solve this problem.


 2. Wrapping the Formula in Quotes

Maximum time online, people indicate the formula by wrapping it in quotes. The formula won’t work then. Quotes can only be used inside the formula if it’s necessary. A dataset is shown below indicating this problem.

Wrapping the Formula in Quotes

So we won’t wrap the formula in a quote.


 3. Missing of Equal Sign

Using an equal sign before the formula is a must thing in Excel. Otherwise, Excel will take the cell as simple text. The resultant dataset will look like this:

Missing of Equal Sign


 4. Keeping ‘Show Formula’ Option Enabled

Sometimes the Show Formulas option from the Formulas ribbon gets enabled because of pressing Ctrl+` from the keyboard. Now the dataset looks like this:

Keeping ‘Show Formula’ Option Enabled

To avoid the problem, simply go to the Formula ribbon and disable the Show Formulas mode.

Keeping ‘Show Formula’ Option Enabled


 5. Formatting Cells as Text

If the cell is formatted into Text, Excel won’t calculate the formula as it treats the formula as text. It’s one of the most common problems in Excel. The dataset looks like this:

Formating Cells as Text

To avoid this problem,

  • Select the cell.
  • Go to the Home tab.
  • Then Number Group > Formatting drop-down > General.

Formatting Cells as Text


 6. Using Apostrophe Before the Formula

By putting an Apostrophe at the beginning of the cell, Excel considers it as a text string and doesn’t show the result of the formula. Here is a dataset with this problem:

Using Apostrophe Before the Formula


 7. Showing Formula Instead of Result by Entering Number with Manual Formats in Excel

If we use any Currency sign or Decimal separator before entering a number in a formula, Excel won’t take it correctly. The formula won’t be applied there and it will be displayed like this:

Showing Formula Instead of Result by Entering Number with Manual Formats in Excel


 8. Deactivated ‘Formula Display’ Option for Showing Formula Instead of Result

Imagine we have a worksheet and it’s not showing the formula values because of the deactivation of the Formula Display Option. It Looks like this:

Deactivated ‘Formula Display’ Option for Showing Formula Instead of Result

We can solve this problem manually in one worksheet but in the case of a lot of worksheets, we can simply follow these steps:

  • At first, select the File tab.

Deactivated ‘Formula Display’ Option for Showing Formula Instead of Result

  • Go to the Options.

Deactivated ‘Formula Display’ Option for Showing Formula Instead of Result

  • Now Click on Advanced.

Deactivated ‘Formula Display’ Option for Showing Formula Instead of Result

  • Then go to the Display options for the portion of the worksheet and select the worksheet name from the drop-down.
  • Make sure that the Show formula in cells instead of their calculated result box is unchecked.

Deactivated ‘Formula Display’ Option for Showing Formula Instead of Result

  • Finally, click OK. We can see that Excel is showing results instead of formulas.


Conclusion

By remembering these reasons, we can fix the problem of showing the formula instead of the result. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.


Related Readings

How to Add Multiple Cells in Excel (6 Methods)

Multiplication Formula in Excel (6 Quick Approaches)

How to Use Format Painter in Excel for Multiple Cells (6 Quick Ways)

Create a Custom Formula in Excel (A Step-by-Step Guideline)

How to Create a Formula in Excel (5 Ways)

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. Here I will post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo