[Fixed!] Formula Not Working in Excel and Showing as Text

Get FREE Advanced Excel Exercises with Solutions!

If your formula is not working in Excel and is showing values as text and you are looking for solutions then this article is for you. Here, we will demonstrate 6 issues that occur and solutions to them.

In the following image, you can see that our formula is not working and showing as text. So let’s dive with us to find the reasons and solutions.

Overview of Formula Not Working in Excel Showing as Text


Formula Not Working in Excel and Showing as Text: 6 Reasons and Solutions

In the following dataset, you can see that we have the Name, Weekly Salary, Total Working Hour, and Salary Per Hour columns. You can see that the formula in the Salary Per Hour column is not working in Excel showing text. Next, in this article, we will show 6 reasons and solutions for this.

Here, we used Excel 365. You can use any available Excel version.

Dataset for formula not working in Excel showing as text


Problem 1: Cell Format Is Set to Excel Text Format

In this scenario, if you are getting your formula as text, you may have the cell formats in Text.

  • Now, to check this scenario, click on cell E5 >> go to the Home

Therefore, you can see that the Number format is set to Text format.

Cell is Formatted as Text therefore formula is not working Excel showing as text

Solution: Change Cell Format to General

  • First, click on cell E5 >> go to the Home tab >> go to the Number Format
  • Then, click on General.

 Selecting General Number Format

  • After that, press ENTER.

Thus, you will see that the formula is now working.

  • Finally, drag the Fill Handle tool to the rest of the cells.

Result After Using General Number Format


Problem 2: Equal Sign (=) Is Not Put Before Formula in Excel

You might not put an equal sign (=) before a formula. Thus the formula is not working and showing as text.

In the following image, you can see that there is no equal sign before the formula, and hence the formula is not working.

No Equal Sign Before Formula therefore formula is not working Excel showing as text

Solution: Insert a Leading Equal Sign Before the Formula

  • Click on cell E5 >> add a leading equal sign.

Putting Equal Sign Before Formula

  • Then, when you press ENTER, the formula works perfectly.
  • Do the same for other cells as well by using the Fill Handle tool.

Putting Equal Sign in All Cells

Read More: [Fixed]: Excel Formula Not Showing Correct Result


Problem 3: “Show Formulas” Option Is Enabled in Excel

When your Show Formulas mode is enabled, your formula will not work and show as text in Excel.

  • To check this, go to the Formulas tab >> from the Formula Auditing group, you will see Show Formulas mode is turned on.

Show Formulas Mode is Turned On therefore formula is not working Excel showing as text

Solution: Disable Show Formulas Option

  • Simply click on Show Formulas to turn this off.

Therefore, you will see that the formula is now working properly.

Show Formulas is Turned Off


Problem 4: There Is Unnecessary Space Before Equal Sign in Excel

When your formula will have unnecessary leading spaces, the formula will not work in Excel showing as text.

  • To check this, click on cell E5>> go to the Formula Bar.

In the Formula Bar, you will see that the formula has a leading space.

Space Before Formula therefore formula is not working Excel showing as text

Solution: Delete the Spaces

  • Now, click on cell E5 >> delete the spaces before the formula.

Thus, the formula is now working properly.

  • Drag down the formula with the Fill Handle tool for other cells as well.

Space is Removed Before Formula


Problem 5: Formulas Have Leading Quote

Another reason your formula not working and showing as text would be you may have put a quote () at the beginning of the formula in the formula bar.

  • Now, to check if this case is true, select cell E5 and observe the Formula Bar.

You can see that the formula has a leading quote.

Formula has Leading Quote

Solution: Delete the Quote

  • Now, click on cell E5 >> go to the Formula Bar.
  • Then, delete the leading quote.

Thus, now the formula works perfectly.

  • Drag down the formula with the Fill Handle tool to the rest of the cells.

Leading Quote is Removed from Formula


Problem 6: Formula Wrapped in Quotes

If your formula is wrapped in quotes then your formula will not work in Excel and show as Text.

In the following image, you can see that our formulas are warped in quotes. Therefore, the formulas are not working.

Formulas Wrapped in Double Quotes

Solution: Remove Double Quotes

  • Now, to remove double quotes, click on cell E5 >> delete the double quotes.
  • Drag down the formula with the Fill Handle tool.

Thus, you can see the formulas now have no double quotes and they are working properly.

Double Quotes Removed


General Tips to Check for When Excel Formulas Are Not Working

If your Excel formula is not working properly and returning an error, go through the following solutions to get rid of this situation.

1. Matching All Parentheses in Excel Formula

The arguments for Excel functions are surrounded by a pair of parentheses. In formulas where you have several functions, you may require to write numerous pairs of parentheses, one inside the other.

In such a case, you must match the opening and closing parentheses. Otherwise, the formula will return an error.

2. Argument of Excel Function Must be Provided Carefully

The required arguments of an Excel function must be fulfilled. Otherwise, Excel will flaunt an error.

Some Excel functions have optional arguments. These optional arguments are surrounded by square brackets[]. This optional argument can be ignored based on situations.

If you do not enter all the required arguments,  you will get a “You’ve entered too few arguments for this function” error message.

Also, if you enter more than the required arguments, Excel will display a “You’ve entered too many arguments for this function” alert.

3. Avoid to Use More than Sixty Four Functions in Nested Excel Formula

  • In Excel versions from 2007 to 2016, it is recommended to avoid using more than sixty-four nested functions in a formula
  • For Excel versions before 2007, it is advised to not use more than seven nested functions in a formula.

Why are Excel Formulas Not Updating Automatically?

The Excel formula gets updated if you modify or change the cell values. If, however, the formula does not update automatically, then this is a problem.

This happens when the Manual calculation option is set instead of the Automatic calculation option.

When the formula is not updating, you have to check the Calculation Option.

  • To do so, go to the Formulas tab >> click on Calculation Option.

There you can see the Manual calculation option is marked.

Manual Calculation Option

  • Now, click on Automatic.

This will update your formula.

Selecting Automatic Calculation Option

In the following video, you can see that when we change the value of D5, the value of cell E5 gets changed.

Thus the formula now updates.

Read More: [Solved]: Excel Formulas Not Updating Until Save


Download Practice Workbook

You can download the practice workbook from the link below.


Conclusion

This article is for those who are looking for solutions when formula not working in Excel showing as text. In this article, we described 6 such problems and solutions for you. We hope your problem will be solved using any of these solutions.

In addition, we describe 3 tips that you can use when the Excel formula is returning an error. This will be helpful for you whenever you get an error message after typing a formula.

Moreover, we describe the solution when an Excel formula is not updating.

We hope this article is useful for you. Thank you for going through the article. If you have any queries or suggestions, please let us know in the comment section.


Related Articles


<< Go Back To Formulas not Working in Excel | 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.
Sowmik Chowdhury
Sowmik Chowdhury

Sowmik Chowdhuri, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a crucial Excel & VBA Content Developer at ExcelDemy. His profound passion for research and innovation seamlessly aligns with his unwavering dedication to Excel. In this role, Sowmik not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, highlighting his steadfast commitment to consistently deliver content of exceptional quality and value. Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo