[Fixed!] Why Formula Is Not Working in Excel (15 Reasons with 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.


Download Practice Workbook


15 Reasons with Solutions for Formula Not Working in Excel

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.

15 Reasons with Solutions for Why 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.

1. Check Cell Format why formula is not working in excel

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.

Why Formula Is Not Working in Excel

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.

1. Check Cell Format

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

Why Formula Is Not Working in Excel

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


2. Check for Space Before Formula

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

2. Check for Space Before 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.

Why Formula Is Not Working in Excel

  • 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.

2. Check for Space Before Formula

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

2. Check for Space Before Formula

Read More: [Fixed]: Excel Formula Not Showing Correct Result (8 Methods)


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.

3. Check If Formulas Are Enclosed in Double Quotes

Let me show you how you can solve this problem.

Solution:

  • Firstly, Delete the double quotes from the formula.

Why Formula Is Not Working in Excel

  • 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.

Why Formula Is Not Working in Excel


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 the image, shown below the possible reason behind this is enclosing the numbers in double quotes.

4. Check If Numbers Are Enclosed 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.

4. Check If Numbers Are Enclosed in Double Quotes

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

Why Formula Is Not Working in Excel

  • 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.

Why Formula Is Not Working in Excel


5. Check for Circular References

Circular References is 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.

5. Check for Circular References

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.

Why Formula Is Not Working in Excel

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

Why Formula Is Not Working in Excel


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.

Why Formula Is Not Working in Excel

  • 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.

6. Entering Numbers with Formatting

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

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

Why Formula Is Not Working in Excel

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

6. Entering Numbers with Formatting


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.

7. Check If Automatic Option Is Selected in Calculation Option

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.

Why Formula Is Not Working in Excel

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

7. Check If Automatic Option Is Selected in Calculation Option


Similar Readings


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.

8. Check If You Have Deleted Row/ Column/ 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.

9. Check All Opening and Closing 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.

Why Formula Is Not Working in Excel

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

9. Check All Opening and Closing Parenthesis

  • 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.

Why Formula Is Not Working in Excel


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.

=VLOOKUP(G5,B5:E8,3)

Why Formula Is Not Working in Excel

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.
=VLOOKUP(G5,B5:E8,3,FALSE)

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.

Why Formula Is Not Working in Excel

Read More: [Solved]: Excel Array Formula Not Showing Result (4 Suitable Solutions)


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.

11. Check for Incorrect Syntax of 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.

=VLOOKUP(G5,B5:E8,3,TRUE)

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.
=VLOOKUP(G5,B5:E8,3,FALSE)

Why Formula Is Not Working in Excel

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.

12. Check for Absolute Cell Reference

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.

=VLOOKUP(G5,B5:E8,3,FALSE)

Why Formula Is Not Working in Excel

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.
=VLOOKUP(G5,$B$5:$E$8,3,FALSE)

Why Formula Is Not Working in Excel

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.

Why Formula Is Not Working in Excel

  • 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.

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

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.

=C5+D5+'[Biology.xlsx]Biology'!C5

Why Formula Is Not Working in Excel

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.
=C5+D5+'E:\This PC\Documents\[Biology.xlsx]Biology'!C5

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.

Why Formula Is Not Working in Excel

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.

14. Enclosing Workbook or Worksheet in Double Quotes

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.
=C5+D5+Biology!C5

Why Formula Is Not Working in Excel

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.

14. Enclosing Workbook or Worksheet in Double Quotes


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.

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. Also, you can visit ExcelDemy for more articles like this.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo