Excel Formula Not Calculating? Fix Zero Results

Errors & fixes · Updated November 19, 2026

If an Excel formula shows zero, first check Formulas > Calculation Options > Automatic, then confirm the formula points to the cells you expect and that those cells contain numeric values. A displayed zero can mean Excel calculated a genuine zero, has not recalculated, or is hiding a nonzero result through formatting.

Start by identifying what the zero means

Before changing the formula, determine whether the cell’s stored result is actually zero. Click the result cell and inspect the formula bar: it shows the formula, while the grid shows its calculated result. If the formula bar contains a formula but the grid displays 0, the cause is usually calculation settings, the formula’s logic or references, the underlying data, or number formatting.

For a simple check, compare the result with the inputs and calculate the expected answer independently. For example, if you are adding three entries, check whether any are blank, stored as text, or outside the referenced range. A formula can be syntactically valid and still return zero because its inputs make zero the correct answer.

With values 12, 8, and 5 in B2:B4, the following formula returns 25 because 12 + 8 + 5 = 25: =SUM(B2:B4). If the result is 0 instead, inspect the cells and calculation mode before replacing the formula.

Check Excel’s calculation mode

Excel can calculate workbooks automatically or wait for a manual recalculation. In Microsoft 365 for Windows, select Formulas > Calculation Options > Automatic. If the workbook is set to Manual, formulas may retain old results after you change an input. That can make a formula appear broken even though its expression is correct.

After switching to Automatic, force a recalculation from Formulas > Calculate Now. To recalculate only the active worksheet, use Formulas > Calculate Sheet. Then change a referenced input slightly and see whether the result updates. If it does, the workbook was likely using manual calculation or had a stale result.

Calculation mode is a workbook-level consideration, and opening a workbook that was saved with manual calculation can surprise people. If several workbooks are open, check the setting again when results behave inconsistently. In Excel for Mac, look under Excel > Preferences > Calculation (the wording can vary by release) and select automatic calculation. Excel for the web generally recalculates formulas automatically, but a workbook’s formulas and data connections can still need attention.

Verify cell references and input values

A formula can return zero because it references the wrong row, column, or range. Click the formula cell and review each colored reference Excel highlights. Confirm that the range includes the data you intended and that copying the formula down did not shift a reference that should remain fixed. A range ending at row 20, for example, will not include a new value entered in row 21.

Also check whether the inputs are numbers or text that looks like numbers. Values imported from a website or a text file can contain leading apostrophes, spaces, or other characters. SUM commonly ignores text entries in a referenced range, so a column that looks numeric may add up to zero if its entries are stored as text. Select an input cell and inspect its alignment and the formula bar; formatting alone does not prove that its contents are numeric.

With 0 in A2 and 40 in B2, the formula =A2*B2 returns 0, because zero multiplied by 40 is zero. This is a useful example of a formula that is calculating correctly even if the result is unexpected. If A2 should contain a positive quantity, correct the source value rather than changing the multiplication formula.

To repair text-formatted numbers, try selecting the affected cells and choosing the warning icon’s Convert to Number option when Excel offers it. If there is no warning, use Data > Text to Columns and complete the wizard with the appropriate defaults, or re-enter a sample value as a number and check whether the calculation changes. Make a copy of important data before bulk conversions.

Make sure Excel is treating the entry as a formula

If a cell contains a formula as text, it will not calculate. The cell may show the formula itself rather than a result, but a text-formatted entry can sometimes contribute to confusing results elsewhere. Look for a leading apostrophe, such as an apostrophe before the equals sign, or a cell format set to Text before the formula was entered.

Select the cell, change its number format to General using Home > Number Format, then press F2 and Enter on Windows to re-enter the formula. On a Mac, the equivalent edit-and-confirm keys can depend on keyboard settings; double-click the cell, then press Return. Simply changing Text to General may not convert an existing formula-like string, so re-entering it matters.

Another possibility is that Excel is displaying formulas in the worksheet instead of their results. Check Formulas > Show Formulas; if the command is enabled, select it to turn formula display off. This setting changes what appears in the grid, not the underlying calculations. It is different from a formula that genuinely returns zero.

Look for number formats that hide or round results

A cell can contain a nonzero value while its displayed result looks like zero. For instance, a value such as 0.004 formatted to show two decimal places appears as 0.00. Increase the displayed decimal places with Home > Increase Decimal, or open Format Cells and inspect the Number settings. The stored value has not changed merely because the display rounds it.

Custom number formats can also hide zeros or show a dash in their place. Select the cell and choose Home > Number Format > General as a diagnostic. If the underlying value then appears, the formula may be fine and the format is responsible. A custom format can have separate sections for positive numbers, negative numbers, zeros, and text, so unusual display behavior may be intentional.

Excel also has a worksheet option for displaying zero values. In Windows, go to File > Options > Advanced, find Display options for this worksheet, and check Show a zero in cells that have zero value. The option applies to the selected worksheet. If it is off, genuine zeros can appear blank; it does not turn a nonzero result into zero.

Inspect IF, IFERROR, and other formula logic

Some formulas deliberately return zero when a condition is met or an error occurs. A common example is IFERROR, which substitutes a chosen result when the calculation produces an error. With 12 in A2 and 0 in B2, =IFERROR(A2/B2,0) returns 0: division by zero would normally produce an error, and IFERROR replaces it with the specified zero. That may conceal the underlying issue, so inspect the inputs and decide whether a visible error or a clearer message would be more useful.

IF formulas can have the same effect when their false-result argument is zero. Check the test condition and both outcomes, not just the part that performs the arithmetic. A condition may be comparing text with a number, testing the wrong cell, or treating a blank as a match. Step through the logic by selecting portions of the formula in the formula bar and using Excel’s Evaluate Formula command under Formulas > Evaluate Formula.

Dates and times are numeric values in Excel, but they can display unexpectedly when the cell format does not match the result. A time calculation that yields less than one day may look like a small decimal under General formatting; a date difference may be zero if both dates are the same calendar day. Check both the input values and the result format before concluding that the formula failed.

Use a consistent troubleshooting sequence

When the reason is not obvious, work through the checks in an order that separates calculation problems from formula problems. Change one thing at a time, so you can tell which check made a difference.

  1. Inspect the formula bar. Confirm the cell contains the intended formula, not a typed zero, text, or an old expression.
  2. Check calculation mode. Set calculation to Automatic and recalculate the sheet.
  3. Trace the inputs. Verify each referenced cell, range boundary, and copied reference.
  4. Check source data. Confirm the values are numeric where necessary and are not blank, text, or zero unexpectedly.
  5. Review formula logic. Look for IF conditions, IFERROR fallbacks, and references that can legitimately return zero.
  6. Test the display. Temporarily use General format, increase decimal places, and check the worksheet’s zero-display option.
  7. Use Evaluate Formula. Step through nested calculations to find the first part that produces an unexpected value.

If only one formula is wrong, inspect its references and logic first. If many unrelated formulas across the workbook stop updating at once, calculation mode is a stronger suspect. If formulas update but show blank cells or dashes, investigate formatting and worksheet display settings.

Prevent the same zero from returning

Keep source data consistent: use actual numbers for quantities and amounts, avoid mixing text labels into numeric columns, and make formula ranges large enough for expected new rows. Excel Tables can help formulas and ranges expand as rows are added, although the best setup depends on how the workbook is organized. Label inputs clearly and keep calculation cells separate from raw imported data.

Use error-handling formulas intentionally. Returning zero can be appropriate when zero is a meaningful result, but it can make missing or invalid inputs look like valid data. For a report, a message or a visible error may be more informative than a silent zero. Before changing a formula, determine whether the zero is mathematically correct, a fallback, stale, or just a display effect.

If the workbook contains linked data or queries, distinguish formula recalculation from refreshing the source. Recalculation updates formulas using values already in the workbook; it does not necessarily fetch new information from an external source. Refresh the relevant connection or query using its available refresh command, and check for prompts or permissions if the source cannot be reached.

Frequently Asked Questions

Why is my Excel SUM formula returning 0?
Check that the referenced range includes the values and that those values are numbers rather than text. Also verify that calculation is set to Automatic and that the cells are not genuinely blank or zero. SUM can ignore text values in a range.

Why does my formula show 0 instead of a blank?
The formula may explicitly return zero, or its arithmetic may evaluate to zero. Check its conditions and fallback values, then inspect the source cells. If you want a blank for a particular condition, the formula logic must specify that outcome; formatting alone does not change the underlying result.

Why does an Excel formula show zero until I press Enter?
This can happen when Excel has not recalculated automatically, or when the formula was entered as text and needs to be re-entered. Set calculation to Automatic, check the cell’s format, and edit and confirm the formula. If it happens throughout the workbook, investigate calculation mode first.

Why is a small formula result displayed as 0?
The cell may be rounding the displayed value to zero decimal places or using a custom format that hides small values. Increase the displayed decimal places or temporarily set the cell to General. The displayed number of decimal places is separate from the stored result.

How do I show zero values in Excel?
On Windows, open File > Options > Advanced, choose the relevant sheet under Display options for this worksheet, and enable Show a zero in cells that have zero value. Also check whether the cell uses a custom number format that suppresses zero display.

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.