Why Formula Is Not Working in Excel (15 Reasons & Solutions)

While working with Excel Sometimes you will face situations when formulas will not work properly. There can be many reasons why the formula is not working. This article’s main focus is to explain why formula is not working in Excel and how you can make them work properly.

Why Formula Is Not Working in Excel (15 Reasons with Solutions)

Here, I have taken a dataset that contains students’ Name and their numbers in Math and English. I will be using this dataset to show you why formula is not working in Excel. I am going to explain 15 possible reasons why the formula is not working in Excel.

1. Check Cell Format

If you type the formula and see results like the picture shown below. Which means you are seeing the formula as text. The possible reason why the formula is not working is maybe you have secreted the cell format as Text.

To check this, go to the Number Format of the Home ribbon and check if Text is selected as the following picture.

Now, let me show you how you can solve this problem.

Solution:

• Firstly, select the cell that is showing this error. Here, I selected cell E5.
• Secondly, go to Number Format on the Home ribbon and select General.
• Now, press ENTER.

This will solve the problem and give you the correct result for the selected cell.

• After that, drag the Fill Handle and you will get your desired results in all the other cells.

Now, you have your formula working and you are getting your desired results.

2. Check for Space Before Formula

In this 2nd case, the cells are showing text like in the previous case. The possible reason why formula is not working can be unnecessary space before the formula.

To check it select the cell where this problem is showing and check if there is any space before the formula.

Let’s see how you can solve this problem.

Solution:

• Firstly, select the cell where this problem is showing. Here, I selected cell E5.
• Secondly, Delete the space before the formula.

• After that, press ENTER and you will get the correct result in that cell.

• Finally, drag the Fill Handle to copy this formula to all the other cells.

Now, you have solved your problem and got your desired results in all the cells.

3. Check If Formulas Are Enclosed in Double Quotes

The next reason why formula is not working in Excel is enclosing the formula in double-quotes. If you accidentally or intentionally enclose a formula in double quotes, it won’t work. So, when you are copying a formula from somewhere, keep it in mind.

Let me show you how you can solve this problem.

Solution:

• Firstly, Delete the double quotes from the formula.

• Secondly, press ENTER and you will get the correct result for that cell.

• Finally, drag the Fill Handle to copy the formula to all the other cells.

Now, you have the correct result for all the cells and your formula is working properly.

4. Check If Numbers Are Enclosed in Double Quotes

In this case, we will see another reason why formula is not working in Excel. If you are getting a result like an image, shown below the possible reason behind this is enclosing the numbers in double-quotes.

To begin with, select the cell and check the Formula Bar to see if this is the problem.

Now, I am going to show you how you can solve this problem.

Solution: 

• Firstly, select the cell where the error is showing. Here, I selected cell E5.
• Secondly, remove the double quotes from the numbers.

• After that, press ENTER and you will get the correct result for the cell. 

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

Now, you have all the correct answers and your formula is working properly.

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

5. Check for Circular References

Circular References are another reason why formula is not working in Excel. If you are seeing a result like the following image then maybe Circular References is the reason behind this.

Let’s see how you can check the Circular References.

• First, go to the Formulas tab.
• Second, go to Formula Auditing.
• Next, click Error Checking.
• After that, put your cursor over Circular References. And, that will show you the cell that is causing this problem. Here, cell E10 is causing the Circular References.

Let’s see how you can solve this problem.

Solution:

• Firstly, select the cell where this problem is showing. Here, I selected cell E10.
• After that, remove the cell from the formula that is causing the Circular references. Here, E10 is causing the problem so I removed it from the formula.

• Finally, press ENTER and you will see that your formula is working.

6. Entering Numbers with Formatting

In this scenario, we are considering a case where you want to get a sign in the result and you are formatting the number accordingly but not getting the result you want.
For example, here I want a currency ($) sign in the result but formatting the number is not giving me the result I want.

You can solve this problem by following these simple steps.

Solution:

• Firstly, select the cell where you want the currency ($) sign.
• Secondly, Delete the currency ($) sign from the formula.

• Finally, press ENTER and you will get the result for the cell.

• Next, select the cell again. Here, I selected cell E5.
• After that, go to Number Format on the Home ribbon and select Currency.

Now, you will get the currency ($) sign in your desired cell.

• Finally, drag the Fill Handle to get the Currency formal in other cells.

And, now you will get your desired sign in the results.

7. Check If Automatic Option Is Selected in Calculation Option

In this case, when you enter the formula at first you will see that it works properly Like in the picture shown below.

But, when you drag the Fill Handle to copy the formula to the other cells you will see that the formula is not working as it was supposed to. The possible reason behind this is Manual is selected in the Calculation Options. When Manual is selected Excel does not calculate until you force it to.

Let’s see how you can solve this problem.

Solution:

• Firstly, go to the Formulas tab.
• Secondly, go to the Calculations Option.
• After that, select Automatic.

Finally, you will see that your formula is working and you are getting the correct results.

Read More: [Fixed!] SUM Formula Not Working in Excel

8. Check If You Have Deleted Row/ Column/ Cell

Now, we will learn about another reason why formula is not working in Excel. If you see an error like the following image, the possible reason behind this can be a deleted Row, Column, or Cell.

To check this you can select the cell that is showing the error. Here, I selected cell D5. And then check the formula and see if there is any missing value.

Solution:

• To solve this problem you have to be careful while deleting a Column or a Row.

9. Check All Opening and Closing Parenthesis

Not using opening and closing parenthesis properly is another reason why formula is not working in Excel.

For example, in the following image, you can see that I wanted to calculate the Total Percentage but we are not getting the correct results. The reason behind this is not using any parenthesis.

You can easily solve this problem by placing some parenthesis by following the BODMAS rule. Let’s see how you can solve this problem for this example.

Solution:

• Firstly, select the cell where the problem is showing. Here, I selected cell E5.
• Secondly, use parenthesis accordingly by following the BODMAS rule. Make sure to use the opening and closing parenthesis properly.

• After that, press ENTER and you will get the correct result.

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

Now, you will see that your formula is working and you are getting all the correct results.

10. Check If All Required Arguments Are Entered Properly

The next reason why formula is not working in Excel is not entering the arguments of a function properly.

For example, in the following image, you can see that there is an error showing. The possible reason behind this may be not entering the arguments properly.

To check this, first select the cell where the error is showing and check the formula bar to see if all the arguments are entered correctly. Here, I selected cell H5. 

You can see that here I used a VLOOKUP function. I wrote the formula as shown below.

Here, I took G5 as lookup_value, B5:E8 as table_array, and 3 as col_index_number. But, I left the range_lookup blank. As a result, the formula is not working.

You can easily solve this problem by entering all the arguments properly. Now, let’s see how it is done.

Solution:

• At first, select the cell where the error is showing. Here, I selected cell H5.
• After that, in that cell write the formula correctly with all the arguments. Here, I wrote the following formula.

Here, I have selected G5 as lookup_value, B5:E8 as table_array, 3 as col_index_number, and FALSE as range_lookup. Now, I have entered all the arguments properly.

• Finally, press ENTER and you will see that the formula is working properly and giving you the correct answer.

Read More: [Solved]: Excel Array Formula Not Showing Result

11. Check for Incorrect Syntax of Function

In this case, you will see a result like the previous case. The reason why formula is not working can be the incorrect use of syntax of the function.

To check this select the cell where the error is showing. Here, I selected cell H5. You can see that I wrote the following formula in that cell.

Here, I have selected G5 as lookup_value, B5:E8 as table_array, 3 as col_index_number, and TRUE as range_lookup. The problem with this formula is I selected TRUE as range_lookup while I want an exact match for the lookup_value. TRUE is for approximate match and FALSE is for an exact match.
You can solve this problem by following some easy steps.

Solution:

• Firstly, select the cell where you want the correct result. Here, I selected cell H5.
• Secondly, in that cell write the formula properly will all the correct syntax. Here, I wrote the following formula.

Here, I have selected G5 as lookup_value, B5:E8 as table_array, 3 as col_index_number, and FALSE as range_lookup. Now, I have entered all the syntax correctly.

• Finally, press ENTER and you will see that the formula is working and giving you the correct result.

12. Check for Absolute Cell Reference

The next reason why formula is not working in Excel is not Using the absolute cell reference properly.

For example, if you see a result like the following image, some results are showing properly and some results are showing errors. Then, not using absolute cell reference can be the reason behind this.

To check this select the cell and look at the formula bar to see the formula. Here, I have selected cell H5. And in cell H5 I wrote the following formula.

Here, I have selected G5 as lookup_value, B5:E8 as table_array, 3 as col_index_number, and FALSE as range_lookup. There is nothing wrong with this formula if you are writing this for that particular cell. But, when you will drag the Fill Handle to copy the formula to the other cells the cells in the formula will change accordingly. This will cause this type of error. You can easily solve this problem by fixing the required cells with the use of absolute cell reference.

Solution:

• Firstly, select the cell where you want to write the correct formula. Here, I selected cell H5.
• Secondly, in that cell write the formula with the required absolute cell reference. Here, I wrote the following formula.

Here, I have selected G5 as lookup_value, B5:E8 as table_array, 3 as col_index_number, and FALSE as range_lookup. I used absolute cell reference for the table_array so that the selected range remains fixed while using Autofill.

• Now, press ENTER to get the correct result in the cell.

• Finally, drag the Fill Handle to copy the formula to all the other cells.

Now, you can see that your formula is working properly and you have the correct results in all the cells.

13. Check If Full Path to a Closed Workbook Is Included

The next reason why formula is not working in Excel is not including the full path to a closed workbook.

If you are seeing results like the following image then this might be the reason.

To check this select the cell and check the formula bar to see the formula. Here, I selected cell E5 and found the following formula.

Here, I summed cells C5, D5, and C5 from another workbook named Biology.xlsx from the worksheet named Biology. The problem with this formula is I did not use the full path of the workbook that’s why I am getting an error.
Let’s see how you can solve this problem.

Solution:

• Firstly, select the cell where the error is showing. Here, I selected cell E5.
• Secondly, in the selected cell write the formula with the full path of the workbook. Here, I wrote the following formula.

Here, I summed cells C5, D5, and C5 from another workbook named Biology.xlsx from the worksheet named Biology. In this formula I used the whole part of the workbook which is E:\This PC\Documents\[Biology.xlsx]Biology.

• After that, press ENTER and you will get the correct result.
• Finally, drag the Fill Handle to copy the formula to all the other cells.

Now, you will see that your formula is working properly.

14. Enclosing Workbook or Worksheet in Double Quotes

In this case, the reason why formula is not working in Excel is enclosing the formula in double-quotes. If you accidentally or intentionally enclose the worksheet name in a double quote in the formula then the formula will show an error like the image below.

You can easily solve this problem by following a few simple steps.

Solution:

• Firstly, select OK to close the error message.

• Secondly, select the cell where the error is showing. Here, I selected cell E5.
• After that, write the formula without enclosing the worksheet name in double quotes. Here, I wrote the following formula.

Here, I summed cells C5, D5, and C5 from the worksheet named Biology.

• Now, press ENTER and you will get the correct result.

• Finally, drag the Fill Handle to copy the formula to all the other cells.

Now, you have all the right results and your formula is working properly.

15. Nesting Functions More Than Capacity

Another reason why formula is not working in Excel is nesting functions more than capacity. Sometimes, you need to nest functions to get your desired results. But if you nest more than capacity then the formula won’t work.

Solution:

• If you are using Excel 2003 or lower, you can use up to 7 nested functions.
• And if you are using Excel 2007 or any newer version then you can nest up to 64 nested functions.

Download Practice Workbook

Conclusion

To conclude, in this article I tried to explain 15 reasons why formula is not working in Excel. Hopefully, it was helpful for you. If you gave any questions or any suggestions, feel free to let me know in the comment section below.

Knowledge Hub

• How to Refresh Formulas in Excel
• [Fixed!] Formula Not Working and Showing as Text in Excel
• Excel Formulas Not Calculating Automatically
• [Fixed!] Excel Formulas Not Working on Another Computer
• [Solved:] Excel Formula Not Working unless Double Click Cell
• [Solved]: Excel Formulas Not Updating Until Save

<< Go Back To Excel Formulas | Learn Excel