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.
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.
➤ 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.
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.
➤ 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.
Read More: [Fixed!] Why Is My Excel Formula Not Updating Automatically (8 Solutions)
Similar Readings
- [Fixed!] Excel Not Responding When Deleting Rows (4 Possible Solutions)
- [Fixed!] Excel Keeps Crashing When Opening File (11 Possible Solutions)
- [Fix]: Microsoft Excel Cannot Open or Save Any More Documents Because There Is Not Enough Available Memory
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.
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.
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
- [Fixed!] Excel Not Responding When Running Macro (9 Possible Solutions)
- How to Open Excel in Safe Mode (3 Handy Methods)
- Fix Excel Not Responding and Save Your Work
- [Fixed!] Excel Attachments Not Opening from Outlook (6 Quick Solutions)
- [Fixed!] Excel Freezes When Copying and Pasting (13 Possible Solutions)
- How to Fix Excel Not Responding Without Closing (16 Possible Solutions)
- [Fixed!] Unable to Open Excel Files Directly by Clicking on the File Icon
