[Fix:] Excel Formula Not Working Returns 0

Get FREE Advanced Excel Exercises with Solutions!

If your Excel formula not working returns 0, this article will help you to solve the problem. Here, we’ll describe 4 reasons that are responsible for this problem and the required solutions to fix those problems.


How to Solve Problems When Excel Formula Not Working and Returns 0: 4 Methods

The following Employee List table shows the ID, Name, and Salary columns. When we calculate the Total Salary of the Salary column in cell D11, we see 0 instead of the required result. Here, we will show you 4 reasons that are responsible for this problem and also we will describe solutions. Here, we used Excel 365. You can use any available Excel version.

Excel Formula Not Working Returns 0


Method-1: Number Stored as Text Thus Formula Not Working Returns 0

In the following D11 cell, we write the following formula using the SUM function to get the sum of the Salary column.

=SUM(D5:D9)

However, in cell D11 we see 0 instead of getting a result. This is because the cells of the Salary column are in Text format, therefore, the formula does not work.

Excel Formula Not Working Returns 0

➤ To see that the cells of the Salary column are in Text format, first of all, we will select the cells of the Salary column from D5 to D9.

➤ After that, we will go to the Home tab > click on the Number option.

We will see from the Number Format box that the format is set to Text.

Now, we want to set the Salary column cells format to Number.

➤ To do so, at first, we will select the Salary column cells from D5 to D9.

We will see a yellow color sign box at the top left of the D5 cell.

➤ After that, we will click on that box.

➤ Afterward, we will select Convert to Number.

Finally, we can see the Total Salary in cell D11.

Excel Formula Not Working Returns 0


Method-2: Manual Calculation Mode is Turned On and Formula Not Working

In the following cell D11 when we write the following formula with the SUM function, we will see that the result shows as 0.

=SUM(D5:D9)

Here, the Manual calculation mode is turned on, therefore, the result is calculated as 0.

Excel Formula Not Working Returns 0

➤ To check whether the Manual calculation mode is turned on or not, first, we will go to the Formulas tab.

➤ After that, we will select the Calculation Options.

We will see the marked Manual option. To get the result in cell D11, we have to unmark the Manual option and we have to mark the Automatic option.

➤ Here, we unmarked the Manual option, and we mark the Automatic option.

Finally, we go back to cell D11, and we can see the result in cell D11.

Excel Formula Not Working Returns 0

Read More: [Fixed!] Why Is My Excel Formula Not Updating Automatically


Method-3: Text Format in Formula Cell

In the following cell D11, we used the SUM function to get the Total Salary of the Salary column. However, the result in cell D11 is 0.

Here, the reason is that cell D11 where we write the formula is formatted as Text.

We can check that there is a Text format in cell D11.

➤ First of all, we will click on cell D11.

➤ Afterward, we will go to the Home tab > click on the Number option.

We can see in the Number Format box that there is Text format in cell D11.

Now, we want to change the format of cell D11 from Text to Currency.

➤ First of all, from the Number option, we will click on the down arrow from the Number Format box, and mark that box with red color.

➤ After that, we will choose Currency.

Finally, in cell D11 we can see the calculated sum.

Excel Formula Not Working Returns 0


Method-4: Formula Returns 0 for Circular References

Here, we can see that the Total Salary in cell D11 shows $0, and in the Formula Bar, we can see the following formula.

=SUM(D5:D11)

Here, the formula adds from cell D5 to D11, however, D11 is the cell where we write the formula. Hence, the cell D11 becomes a circular reference, Therefore, the result becomes $0.

We can solve the problem easily by the following procedures.

➤ First of all, in cell D11, we will write the following formula.

=SUM(D5:D9)

Here, the formula adds up from cell D5 to D9, therefore, there is no circular reference.

➤ After that, press ENTER.

Finally, we can see the Total Salary in cell D11.

Excel Formula Not Working Returns 0


Download Workbook


Conclusion

Here, we tried to show you 4 solutions when the Excel formula not working returns 0. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, feel free to contact us in the comment section.


<< Go Back to Excel Formula Errors | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo