If an Excel formula returns 0 when you expect a blank cell, the formula may be calculating an empty reference as zero.
The right fix depends on whether you want to hide zero results, leave the cell empty until inputs are entered, or preserve zeros that carry meaning.
Why does an Excel formula return zero instead of blank?
Excel treats a genuinely empty cell as zero in many arithmetic calculations.
For example, if A2 is empty, the formula =A2 can display 0, and =A2+B2 can also return 0 when both referenced cells are empty.
A formula that points to a blank cell does not make its own result cell blank; it returns a value based on Excel’s calculation rules.
Other common causes include a formula that explicitly returns 0, a lookup returning a zero-valued field, or a cell formatted to display zeros.
Check the formula bar and number format before changing the formula: a displayed zero may be the actual result, or it may be affected by formatting.
Return a blank when the input cell is empty
For a single input in A2, use an IF test to keep the result blank until A2 contains something:
=IF(A2="","",A2*B2)If A2 is empty, the formula returns an empty text string, displayed as a blank.
If A2 contains a value, it calculates the product of A2 and B2.
The formula’s result is technically text when it returns "", not a truly empty cell.
Other formulas may treat that result differently from a cell that has never contained anything.
To require both inputs before calculating, test both cells:
=IF(OR(A2="",B2=""),"",A2*B2)With A2 set to 4 and B2 empty, the result is blank.
If B2 is 3, the result is 12.
This is useful for worksheets where partially completed rows should not show zeroes.
Show blank when a calculation equals zero
If the calculation should run but you do not want a zero displayed, wrap it in IF:
=IF(A2-B2=0,"",A2-B2)For example, if A2 and B2 both contain 15, the formula returns a blank; if A2 is 20 and B2 is 15, it returns 5.
This approach hides every zero result, including zeros produced by valid data.
Use it only when a zero should be visually suppressed.
If a formula already exists, place that formula in both parts of the test.
For instance, change =SUM(A2:B2) to:
=IF(SUM(A2:B2)=0,"",SUM(A2:B2))This works in modern Excel and older desktop versions that support IF.
It repeats the calculation, which can make a long formula harder to maintain.
In Microsoft 365 or Excel 2021 and later, LET can name the calculation once:
=LET(result,SUM(A2:B2),IF(result=0,"",result))Keep zeroes in the formula but hide them with formatting
When zeros are valid values and should remain numeric, change how Excel displays them instead of changing the formula.
Select the relevant cells, open Format Cells, choose Custom, and enter a format such as:
0;-0;;@In a custom number format, the sections separated by semicolons control positive numbers, negative numbers, zero, and text.
The empty third section leaves zero values visually blank.
The cell still contains numeric zero, so totals and comparisons continue to treat it as zero.
This formatting applies only to the selected cells and may not carry over when data is pasted or a different format is applied.
It can also make a meaningful zero easy to overlook.
Use it for presentation-focused reports, and keep the underlying value visible when users need to audit or interpret it.
Fix zeroes returned by lookup formulas
A lookup can return 0 when the matching row has an empty cell in the return column.
For example, a lookup of an empty cell in C2 may produce zero rather than an empty-looking result.
If blank return cells should display blank, first check whether the lookup result is zero:
=IF(XLOOKUP(E2,A2:A10,C2:C10,"")=0,"",XLOOKUP(E2,A2:A10,C2:C10,""))This version is available in Microsoft 365 and Excel 2021 or later, which support XLOOKUP.
It hides any zero returned by the lookup, including a real numeric zero in the matched row.
If that distinction matters, test the matched source cell or use a lookup design that can distinguish an empty return cell from a zero value.
In older Excel versions without XLOOKUP, the same display check can wrap a formula based on INDEX and MATCH, for example:
=IF(INDEX(C2:C10,MATCH(E2,A2:A10,0))=0,"",INDEX(C2:C10,MATCH(E2,A2:A10,0)))Both approaches assume the lookup value exists.
If it might be missing, handle that case separately with IFERROR or a not-found result that suits the worksheet; do not use error handling to conceal unrelated formula problems.
Blank-looking cells, empty strings, and zeroes
A cell that contains a formula returning "" looks empty, but it is not truly empty.
For example, ISBLANK(A2) returns FALSE when A2 contains a formula, even if that formula displays nothing.
A test such as A2="" can identify an empty cell or a formula result of empty text, so choose the check that matches your data.
Also watch for spaces.
A cell containing a space is not blank, and the comparison A2="" will not treat it as empty.
If imported data may contain extra spaces, a check using TRIM can help:
=IF(TRIM(A2)="","",A2*B2)This handles ordinary leading and trailing spaces, but not every invisible character from copied or imported text.
Nonbreaking spaces may require additional cleanup with SUBSTITUTE.
Should you change the formula or the number format?
- Use an
IFinput check when a result should wait until required cells are filled. - Use
IF(calculation=0,"",calculation)when zero results should become blank text. - Use a custom number format when zero should stay numeric but disappear from view.
- Review lookup return cells when zeroes appear only in results pulled from another range.
Before applying a fix across a whole column, check how other formulas use the result.
A displayed blank from "" can behave differently from numeric zero in charts, counts, and later calculations.
A number format preserves the numeric value, while a formula that returns "" changes the result to text for those cases.