Excel Formula Returns 0 Instead of a Number? Fix It

Errors & fixes · Updated November 19, 2026

If an Excel formula returns 0 instead of the number you expect, first check whether the cell is merely formatting or rounding the result to zero; then check the formula’s references, input values, and calculation settings. A zero can be a real result, a display issue, or a fallback value that the formula was designed to return, so identify which kind you have before changing the formula.

First determine whether the result is really zero

A cell can display 0 even when its underlying value is a small nonzero number. For example, a result such as 0.004 appears as 0 if the cell’s number format shows no decimal places. The value is still available to other formulas; only its display has been rounded. To check, select the cell and look at the formula bar, or temporarily increase the displayed decimal places.

On Excel for Microsoft 365 on Windows, select the cell and use Home > Number to choose a format with more decimal places. You can also select Home > Increase Decimal. The keyboard shortcut Ctrl+1 (Command+1 on Mac) opens Format Cells, where you can choose a number format and set decimal places. On Excel for the web, use the number-format controls on the Home tab; some desktop dialog options may be arranged differently.

Also check whether the cell uses a custom format that hides values or displays zeros in an unusual way. Choose General temporarily to compare the display. Be cautious with workbook-level display settings such as “precision as displayed”: changing precision can permanently alter stored values, so do not enable it simply to make a displayed result look right.

Check the formula’s cell references

If the underlying value is genuinely zero, inspect the references in the formula bar. A formula may point to a blank cell, the wrong row, or a range that excludes the data you intended to include. This often happens after copying a formula, inserting rows, or selecting a range manually. Click the formula cell and use the colored reference outlines Excel displays while editing to see which cells are included.

For example, with the values 12, 8, and 5 in B2:B4, a sum should return 25. With those values in B2:B4, enter this formula in another cell:

=SUM(B2:B4)

If the formula instead points to B5:B7, it may legitimately return zero when those cells are empty. Compare the intended range with the range shown in the formula. For long formulas, select a reference within the formula bar and press F9 to inspect the selected calculation; press Esc afterward to avoid replacing the formula with the inspected result. On a Mac, the function-key behavior can depend on your keyboard settings, so using the formula bar and selecting references is a dependable alternative.

Relative references change when a formula is copied. If a calculation must always use a particular input cell, lock that reference with dollar signs while editing. For instance, a copied formula that should always use the tax rate in B1 needs an absolute reference to B1; a relative reference may shift to B2 or B3 as it is filled down. Check both the row and column of every important reference.

Look for blank cells and formulas that return empty text

Blank inputs are a frequent source of unexpected zeros. Excel treats an empty cell as zero in many arithmetic operations, and a sum of an empty range returns zero. A cell that looks blank may also contain a formula returning an empty text string. It is not truly empty: functions that count or test cells can treat it differently from a cell that has never contained anything.

For example, with A1 containing a formula that returns an empty text string, a test for an empty-looking cell may need to account for that result. With A1 as the cell being checked, this formula returns “blank” for either a genuinely empty cell or an empty text string:

=IF(A1="","blank","has a value")

This is useful for diagnosing a worksheet, but it does not mean every blank should be converted to zero. Decide what an empty input means in the context of the calculation. In a sales report, it may mean no sale; in a data-entry sheet, it may mean the information has not been entered yet. Those interpretations can require different formulas and clearer labels.

Check whether numbers are stored as text

Values that look numeric may be text, especially after copying data from a website, a CSV file, or another system. Clues include numbers aligned to the left by default, a green error indicator, or a warning icon when you select a cell. Text numbers can behave differently in calculations: SUM, for example, ignores text entries inside a referenced range, which can make a total smaller than expected or even zero.

With the text value 125 in A1, use this formula in another cell to convert a numeric text value into a number:

=VALUE(A1)

The result is the number 125 if Excel can interpret the text using the workbook’s regional number conventions. Decimal and thousands separators vary by locale, so a value containing punctuation from another region may need cleanup before conversion. Another option, when A1 contains a numeric text value Excel can coerce, is to multiply it by 1; do not use coercion blindly if the cell may contain labels or mixed content.

For a quick check, with the suspected value in A1, this formula reports TRUE if Excel recognizes it as a number:

=ISNUMBER(A1)

When imported data contains spaces or nonprinting characters, converting the format to Number alone may not fix it; formatting changes appearance, not the underlying text. Clean or re-import the data, or use an appropriate conversion step, and then verify the result with ISNUMBER.

Make sure Excel has recalculated

Excel normally recalculates dependent formulas automatically. If calculation is set to Manual, a formula can show a previous result—including zero—even after its inputs change. On Windows, check Formulas > Calculation Options and select Automatic. Then use Formulas > Calculate Now to recalculate open workbooks, or Formulas > Calculate Sheet to update the active sheet.

Manual calculation can be intentional in a large workbook with many complex formulas, because recalculating after every edit may take time. If you switch to Automatic, a workbook may take longer to respond while it recalculates. Calculation mode can also affect other open workbooks, so check the setting when results seem stale across more than one file. Excel for Mac has calculation controls under Excel > Settings > Calculation in current versions; the ribbon also offers calculation commands. Excel for the web generally recalculates supported formulas as workbook inputs change.

A displayed zero that does not update is different from a formula whose current inputs genuinely produce zero. After recalculating, inspect the formula and its precedents again rather than assuming the calculation setting is the only cause. If the workbook has external links or data connections, those sources may also need to be refreshed; recalculation does not necessarily update every external source.

Inspect IF and error-handling logic

A formula may explicitly return zero under a condition, even when you expected a calculation. Read each IF test and confirm which result is returned when the test is false. Also look for IFERROR: it can replace an error with zero, concealing problems such as division by zero or an invalid lookup. That makes a sheet look tidy but can hide the reason a result is missing.

With 10 in A1 and a blank cell in B1, this formula returns 0 because dividing by the blank cell produces a division error and IFERROR substitutes zero:

=IFERROR(A1/B1,0)

For troubleshooting, temporarily remove the error-handling wrapper or use a message that makes the problem visible. Once you know which error occurs, correct the input or reference, then decide whether a zero is an appropriate final fallback. A zero can be misleading if it looks like a measured result. Sometimes a blank or a clear note is more honest, depending on how the worksheet will be used.

Also confirm that your conditions match the actual data. A test for a specific text value can fail because of extra spaces, a different spelling, or a number stored as text. A formula that returns zero when a condition is not met may therefore be working exactly as written, even though the condition is not matching the row you expected.

Use a short troubleshooting sequence

Work through the checks in order, changing one thing at a time. This helps you isolate the cause rather than making several edits that obscure it.

  1. Select the result cell and inspect the formula bar. Confirm that you are looking at the intended formula and not a hard-coded zero.
  2. Temporarily change the number format to General or increase decimal places. If the value appears, fix the display format rather than rewriting the calculation.
  3. Trace each reference back to its source cells. Check for an off-by-one range, a blank input, or a reference that shifted when the formula was copied.
  4. Check whether numeric-looking inputs are actually numbers. Use the cell’s warning indicator or an ISNUMBER check where appropriate.
  5. Review IF, IFERROR, and other conditions for an explicit zero result or a hidden error.
  6. Confirm calculation is Automatic, then recalculate and refresh external data if the workbook depends on it.

If the formula is long, break it into smaller checks in unused cells or use Formula Auditing commands on the Formulas tab, such as Trace Precedents. The Evaluate Formula command can step through parts of a formula on supported desktop versions. These tools help reveal whether the issue is in the source data, a condition, or the final calculation.

Prevent zeros that mislead readers

Build worksheets so users can tell a genuine zero from missing information. Label input cells, document what blank means, and avoid hiding all errors behind a zero without considering the consequences. If a zero is meaningful, display it clearly. If the calculation is not ready because required inputs are missing, a blank or explanatory message may better represent that state.

When you copy formulas, test the first and last rows and inspect how references change. Use absolute references for fixed assumptions, such as a rate stored in one cell, and keep data ranges aligned with their labels. For imported data, check a sample of values before building totals. A number format can make data easier to read, but it cannot convert text to numbers or repair a reference.

Finally, distinguish between the calculation and its presentation. Rounding a displayed result is not the same as rounding the stored value. If downstream calculations must use rounded amounts, use an explicit rounding calculation and confirm that this is the intended business rule; otherwise, preserve the underlying precision and adjust only the display.

Frequently Asked Questions

Why does my SUM formula return 0?
Check that the referenced range contains the numbers you expect and that they are stored as numbers rather than text. Also check whether the range points to the correct rows and whether the cells are genuinely empty or contain formulas returning empty text.

Why does Excel show 0 when the result should be a decimal?
The cell may be formatted with no decimal places, causing a small value to display as zero. Increase the decimal places or choose a more suitable number format to see the underlying value.

Can a blank cell make a formula return 0?
Yes. Excel treats blanks as zero in many arithmetic calculations, and a sum over an empty range returns zero. A formula that returns an empty text string can also affect formulas differently from a truly empty cell.

Why does my formula still show 0 after I change the inputs?
Check Formulas > Calculation Options and select Automatic, then recalculate. If calculation is already automatic, verify that the formula refers to the cells you changed and that any external data has been refreshed.

Does IFERROR cause a formula to return zero?
It can. If the formula’s error fallback is zero, an underlying error such as division by zero is displayed as 0 instead of an error message. Inspect the calculation without the fallback to find and fix the underlying problem.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.