Sowmik Chowdhury
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.
Download Practice Workbook
You can download the practice workbook from the link below.
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 Formas 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.
Read More: [Solved:] Excel Formula Not Working unless Double Click Cell (5 Solutions)
Similar Readings
- [Solved]: Excel Formulas Not Updating Until Save (6 Possible Solutions)
- [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)
- [Fixed!] Formula Result Showing 0 in Excel (3 Solutions)
- [Fixed]: Excel Formula Not Showing Correct Result (8 Methods)
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.
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. You can visit our website Exceldemy for various Excel-related articles.
