Why Excel Shows Formulas Instead of Results (7 Reasons)

Get FREE Advanced Excel Exercises with Solutions!

In this Excel tutorial, you will learn why Excel sometimes shows formulas instead of results.

Excel can show formulas instead of results for several reasons like: if you put space before the Excel formulas; if you skip the equal sign before the formulas; if you enable “Show Formulas” command in the (Formulas -> Formula Auditing tab); when you keep the output cells in the text format; if you use apostrophe (‘) before Excel formulas; or you have chosen Show formulas in cells instead of their calculated results option here: File ->Options->Advanced.

For example, I have the following dataset and you see Excel is showing formulas in the cells instead of calculated results. Let’s check how we can solve this problem.

Excel is showing this

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

1. Using Space in front of Excel formulas will show only formulas instead of results

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. Not using Equal sign before formula can make this problem

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


3. Enabling ‘Show Formulas’ option will not convert to results

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


4. Cells already in the Text format will show formulas instead of values

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 > go to the editing mood in the cell by F2 key or double-clicking the cell > hit Enter.

Formatting Cells as Text


5. Using an apostrophe before the formula will show the formulas instead of the values

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


6. If “Show formulas in cells instead of their calculated results” is chosen in the File Options

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:

  • 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 Articles


<< Go Back To Show Excel Formulas | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo