Excel Formula Returning 0 Instead of a Value? How to Fix It

Errors & fixes · Updated November 19, 2026

If an Excel formula returns 0 instead of the value you expect, first check whether it is referencing a blank cell, calculating a genuine zero, or displaying a nonzero result with a format that hides it. Then check the formula’s conditions, the data types in its inputs, and Excel’s calculation settings; these checks usually reveal whether the problem is the result or only how it appears.

Start by checking the result and the formula

Select the cell showing zero and look at both the formula bar and the formula itself. The cell may contain a valid formula whose result is zero, or it may contain a nonzero result that a number format or conditional format makes look like zero. Click the cell and inspect the formula bar to confirm that you are looking at the expected formula, not a nearby cell or a pasted value.

Next, follow the formula’s references. Click a referenced cell, or use Excel’s auditing tools: on Windows, go to Formulas > Formula Auditing > Trace Precedents to show cells feeding the selected formula. Formulas > Evaluate Formula lets you step through parts of a formula and see intermediate results. If the formula refers to a different sheet, confirm that the sheet name and cell address are correct.

A quick diagnostic is to temporarily simplify the formula or inspect its inputs in separate cells. Do not replace a formula with a guessed value until you know whether the issue is incorrect logic, missing data, formatting, or a calculation that has not updated.

A blank reference can evaluate to zero

One of the most common causes is a direct reference to an empty cell. In a worksheet with a blank A1, the formula =A1 returns 0. Excel treats a blank cell referenced in many arithmetic or direct-reference contexts as zero. This does not mean A1 contains a typed zero; it means the formula’s result is numeric zero.

This often happens in templates where a formula is filled down before data is entered. For example, a row may calculate a price from quantity and unit cost even though the quantity is still blank. If you want such rows to remain visually empty until an input is supplied, add a condition. With quantity in A2 and unit price in B2, enter this formula in C2; if A2 is blank, it returns an empty text string, otherwise it multiplies the inputs:

=IF(A2="","",A2*B2)

That formula is useful for presentation, but an empty string is not the same as a truly empty cell. Other formulas may treat it as text, and functions such as COUNTA can count a formula cell even when it displays nothing. If a zero is meaningful in your worksheet, keep the zero rather than suppressing it; hiding it can make a real result harder to spot.

Check whether the number format is hiding the value

A cell can contain a nonzero value while its number format displays 0. For instance, a value such as 0.4 displayed with zero decimal places rounds to 0 on screen. Select the cell and increase the displayed decimal places using Home > Number, or open Format Cells to inspect its category and custom format. On Windows, Ctrl+1 opens Format Cells; on Mac, use Command+1.

A custom number format can also hide zero values or display values in an unexpected way. To test for a display issue, change the selected cell to General or a suitable standard number format. If the value changes on screen, the formula may have been correct all along. If the result is still zero, continue checking the formula and its inputs.

Excel also has a worksheet setting that controls whether zero values appear in cells. In Excel for Microsoft 365 on Windows, check File > Options > Advanced, then look under Display options for this worksheet for “Show a zero in cells that have zero value.” A cleared setting hides actual zero results on that worksheet. Mac and Excel for the web have different settings interfaces, so look for the worksheet’s zero-display option in the version you use.

Finally, inspect conditional formatting. A rule might apply a font color that blends into the cell background or otherwise make a value difficult to read. On Windows, open Home > Conditional Formatting > Manage Rules and review rules applying to the selected cell.

Review IF conditions and lookup formulas

A formula may be designed to return zero when a condition is not met. With an amount in B2 and a status in C2, this formula returns the amount only when C2 is exactly “Paid”; for any other status, it returns 0:

=IF(C2="Paid",B2,0)

Check the condition and the value assigned to its false branch. A misspelled status, an extra space, or a different label such as “Complete” will make the test false. If zero is not the fallback you want, change the formula’s logic deliberately—for example, return an empty string for an unfinished row, while remembering that an empty string is text rather than a truly blank cell.

Lookup formulas can also show zero because the returned cell is blank, or because an error has been converted to zero. With the lookup value in E2, keys in A2:A10, and return values in B2:B10, this formula replaces any VLOOKUP error with 0:

=IFERROR(VLOOKUP(E2,A2:B10,2,FALSE),0)

That may conceal a missing match or another lookup problem. Temporarily remove IFERROR or change its fallback to a clear message while diagnosing the issue. If the lookup succeeds but the matching return cell is blank, the result may still appear as zero. Verify that the matching row is the one you intended and that the return cell contains the expected value.

Check for numbers stored as text and mismatched data

Excel distinguishes numeric values from text that looks like a number. A cell containing the text “125” may look like the number 125, but calculations and functions can treat it differently. One clue is a green triangle or a warning icon indicating that a number is stored as text. Text can also arrive through copied reports, CSV imports, or data entry with a leading apostrophe.

For example, SUM adds numeric cells in a range but ignores text entries in cells. With values intended to be in B2:B4, =SUM(B2:B4) adds numeric values in that range; if every entry is stored as text, the result is 0. Check the cells’ alignment and warnings, and compare their contents with a known numeric value. Formatting text as Number alone may not convert it; use the warning menu’s conversion option when available, or re-import/clean the source data.

Text mismatches also affect criteria and lookups. “Paid” and “Paid ” (with a trailing space) are not identical strings for an exact match. A formula with A2:A6 as the status range and “Paid” as the criterion returns a count of exact matches:

=COUNTIF(A2:A6,"Paid")

If the cells contain extra text after the word, a wildcard criterion can match entries that begin with Paid. With the same range layout, this formula counts values starting with “Paid”:

=COUNTIF(A2:A6,"Paid*")

Use that only if matching additional text is appropriate; it could count labels such as “Paid late” as well. For reliable calculations, clean inconsistent spaces and standardize labels at the source rather than broadening a match without checking what it includes.

Make sure Excel has recalculated

If inputs changed but a formula still shows an old result, Excel may be using manual calculation. On Windows, check Formulas > Calculation Options and select Automatic if that is appropriate for the workbook. In manual mode, formulas update only when recalculation is triggered. The setting can matter in large workbooks, where manual calculation is sometimes chosen to avoid repeated delays, so consider the effect before changing it.

Use Formulas > Calculate Now to recalculate the open workbook, or Formulas > Calculate Sheet to update the active sheet. Menu names and placement can differ in Excel for Mac and Excel for the web. If only some results are stale, also check whether formulas depend on cells or external data that have not updated; recalculation cannot correct missing or outdated source information.

A practical troubleshooting order

Work through these checks in order, changing one thing at a time so that the cause stays clear:

  • Confirm the selected cell contains the expected formula and inspect its referenced cells.
  • Check whether a blank input is being treated as zero or whether an IF condition intentionally returns zero.
  • Change the display temporarily to General and inspect decimal places, custom formats, worksheet zero display, and conditional formatting.
  • Check whether numeric-looking inputs are stored as text, or whether lookup labels contain spaces or other differences.
  • Inspect error-handling functions such as IFERROR that may be hiding a failed lookup or calculation.
  • Confirm Excel is recalculating and that all source data is current.

When the cause is unclear, make a copy of the workbook or test a simplified formula in an unused cell. That keeps the original calculation intact while you compare intermediate results. Once the formula works, test it with a normal case, a blank input, and an edge case such as a missing lookup match.

Frequently Asked Questions

Why does a formula that references a blank cell show 0?
A direct reference to a blank cell can evaluate to numeric zero. If you want the result to look blank until an input is entered, use a condition that returns an empty string, while remembering that an empty string is not the same as a genuinely empty cell.

Why does SUM return 0 when the cells contain numbers?
The entries may be numbers stored as text, which SUM ignores in worksheet ranges. Check for green-triangle warnings and convert the values to numeric data. Also confirm that the formula refers to the cells you intended and that the range is not empty.

Why does my formula show 0 even though the referenced cell has a value?
Check the reference and formula logic first, then inspect number formatting and conditional formatting. A formula may also be returning zero from an IF fallback or an IFERROR fallback, even if the underlying lookup or condition is not working as expected.

Why is Excel not updating my formula result?
The workbook may be set to manual calculation, or the formula may depend on data that has not refreshed. Check Formulas > Calculation Options on Windows and recalculate the workbook or sheet. Recalculation will not fix an incorrect reference or missing source data.

How can I show a blank instead of zero?
If zero means “no result yet” in your worksheet, add a condition that returns an empty string when the relevant input is blank. Alternatively, adjust number formatting or the worksheet’s zero-display setting, but hiding zero values also hides legitimate zeros and can make the data ambiguous.

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.