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

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.

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.

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.

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.

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.

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.

Solution: Insert a Leading Equal Sign Before the Formula

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

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

Now, click on Automatic.

This will update your formula.

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.

Download Practice Workbook

You can download the practice workbook from the link below.

Formula Not Working Showing as Text.xlsx

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.

<< Go Back To Formulas not Working in Excel | Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!