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

It is very annoying and confusing when you find that your formula is not working in Excel. In particular, you might find that the formula is shown as text in Excel. Certainly, there are some issues for this to happen. This article emphasizes the issues for which the formula is not working and showing as text in Excel. Also, you’ll get the solution to each of the issues.


Download Practice Workbook

You can download the practice workbook from the link below.


5 Issues and Their Solutions for Formula Not Working and Showing as Text in Excel

There are many scenarios where you may have worked hard to create a formula and then put them into many cells. But, somehow the formula is not working, rather showing as text.

Now, let’s assume a simple scenario where you want to use the formula =D5/C5 in cell E5 and then drag the Fill Handle to calculate the Hourly Pay for a list of people. In this case, cells E5, D5, and C5 are the first cell of the column Hourly Pay, Daily Pay, and Work Hours respectively. But, somehow the formula is not working. Moreover, it is shown as text.

formula not working in excel showing as text dataset

If you want to fix this problem of formula not working and showing as text in excel, you may follow one of the 5 different solutions for 5 different scenarios below.


1. When the Cell Format Is Set as Text Format in Excel

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 and see if the format is Text from the Number Format like the screenshot below. Consequently, if it is so, you may follow the steps below to solve the problem.

Cell Format Is Set as Text Format

Solution:

  • First, go to the Number Format options.
  • Then, click on General.

Cell Format Is Set as Text Format

  • After that, press ENTER.
  • Finally, drag the Fill Handle to the rest of the cells.

Eventually, your output will be like the screenshot below.

Cell Format Is Set as Text Format


2. If There Is No Equal Sign Before Formula in Excel

In the next scenario, you may have forgotten to put an Equal sign, =, before the formula. Now, to check if this is true, click on cell E5 and see whether there is an equal sign in the formula bar before the formula or not. At this point, to fix this type of problem follow the steps below.

No equal sign in excel formula

Solution:

  • First, select cell E5 and go to the formula bar.
  • Next, add an Equal sign (=) before the formula.

No equal sign in excel formula

  • Finally, press ENTER and use the Fill Handle to the rest of the cells.

No equal sign in excel formula

Read More: [Fixed!] Why Formula Is Not Working in Excel (15 Reasons with Solutions)


3. If the Show Formulas Option Is Enabled in Excel

Now, in this scenario, if you are getting your formula as text, you may have the Show Formulas options enabled in your Excel file. At this point, to check, if this is the case, go to the Formulas tab. Then, from Formula Auditing see if the Show Formulas option is enabled like in the screenshot below. Consequently, if it is so, follow the steps below to solve this problem.

Show Formulas Enabled Formula Not Working

Solution:

  • Simply, disable the Show Formulas by clicking on it.
  • Or, you can press CTRL + ` on your keyboard.

Note: The ` button is just below the ESC button on your keyboard.

Finally, the above mentioned steps will fix your problem like the screenshot below.

Show Formulas Enabled Formula Not Working

Read More: [Solved:] Excel Formula Not Working unless Double Click Cell (5 Solutions)


Similar Readings


4. When There Is Unnecessary Space Before Equal Sign in Excel

In this next scenario, you may have put an unnecessary space before the equal sign in the formula. Now, select cell E5 and check if this is true in the formula bar like in the screenshot below. After checking, if you find this case to be true, you may follow the steps below, to solve this problem.

When There Is Unnecessary Space Before Equal Sign

Solution:

  • First, select cell E5 and go to the formula bar.
  • Next, delete the space before =.

When There Is Unnecessary Space Before Equal Sign

  • Then, press ENTER.
  • Finally, use the Fill Handle for the rest of the cells of the column.

When There Is Unnecessary Space Before Equal Sign


5. If the Formula Starts with a Quote in Excel

In this scenario, if you are facing the issue of your formula being shown as text, 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 like the screenshot below.

Consequently, if you find this scenario true, you may follow the steps below to fix this problem.

formula starts with a quote

Solution:

  • First, select cell E5 and go to the formula bar.
  • Next, delete the before the formula.

formula starts with a quote

  • Lastly, press ENTER and drag the Fill Handle to the rest of the cells.

Eventually, you will have your output like the screenshot below.

formula starts with a quote


Conclusion

In this article, I have shown you five different issues, in which your formula is not working. Rather, the formula is shown as text. Moreover, I have provided the solution for each of these scenarios.

Last but not the least, I hope you found what you were looking for from this article. If you have any queries, please drop a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.


Related Articles

Sowmik Chowdhury
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo