[Fix:] Excel Formula Not Working Returns 0

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.

Download Workbook

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

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

Read More: What to Do When Excel Is Not Responding (10 Handy Tricks)


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, at 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 (8 Solutions)


Similar Readings


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 the cell D11 where we write the formula is formatted as Text.

We can check that there is 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 we 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

Read More: [Fixed!] Excel File Not Opening on Double Click (8 Possible Solutions)


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


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 know us in the comment section.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo