Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the 0 vs blank error
Excel & Google SheetsWhat 0 vs blank means
A formula that references an empty cell, or performs arithmetic that results in zero, will display 0 rather than leave the cell visually blank. Zero and blank are different values in spreadsheets — 0 is a number, blank is the absence of a value — and they behave differently in charts, counts, and formatting.
Common causes
- The formula references a cell that is empty, and arithmetic on an empty cell treats it as 0 (e.g., =A2+B2 where A2 is empty returns the value in B2, which may be 0 or blank).
- A SUM, AVERAGE, or other aggregation formula spans rows that do not yet have data, producing 0 for empty input rows.
- A lookup formula (VLOOKUP, INDEX/MATCH) finds a match but the matched cell in the return column is itself empty, returning 0.
- An IF formula returns 0 in its false branch unintentionally (e.g., =IF(condition, value) with no false argument defaults to FALSE, which displays as 0 in a numeric context).
- Cells are formatted to show 0 for false/zero values, but the visual 0 looks like unintended output.
Example fix
Broken
=IF(A2>0,B2/A2)Fixed
=IF(A2>0,B2/A2,"")An IF with only two arguments (condition, true_value) returns FALSE when the condition is false. In a numeric context, FALSE displays as 0. Adding an explicit empty string "" as the third argument returns a blank cell instead.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Wrap the formula in an IF that returns an empty string when the result is zero or blank: =IF(formula=0,"",formula). Be aware that the cell now contains text ("") rather than a number, which affects downstream sums.
- 2For lookups returning 0 from an empty cell: =IF(VLOOKUP(A2,$D:$E,2,0)=0,"",VLOOKUP(A2,$D:$E,2,0)) — or more efficiently: =LET(result,VLOOKUP(A2,$D:$E,2,0),IF(result=0,"",result)) in Excel 365.
- 3To suppress 0 display without changing the value, use a custom number format: select the cells, Ctrl+1, Number tab, Custom, enter 0;-0;;@ — this hides zeros visually but keeps them as numeric zeros for calculations.
- 4In Excel, you can hide all zeros in a sheet: File > Options > Advanced > Display options for this worksheet > uncheck 'Show a zero in cells that have zero value'.
- 5If the 0 comes from an IF missing a false argument — =IF(A2>10, "Yes") — add the false return explicitly: =IF(A2>10, "Yes", "").
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every 0 vs blank and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
If I return "" (empty string) instead of 0, will my SUM still work?
Yes. SUM, SUMIF, and AVERAGE ignore text values including empty strings. However, COUNT counts only numbers, so cells with "" are not counted — use COUNTA if you need to count all non-blank cells including text.
What is the difference between a truly blank cell and one containing ""?
A truly blank cell is empty — ISBLANK() returns TRUE and COUNT ignores it. A cell with "" contains a zero-length text string — ISBLANK() returns FALSE. For most display purposes they look the same, but ISBLANK-based logic, COUNTBLANK, and certain filter behaviours treat them differently.
Can I hide zeros across an entire sheet without changing my formulas?
In Excel: File > Options > Advanced > 'Show a zero in cells that have zero value' — uncheck it for the active sheet. In Google Sheets there is no equivalent sheet-wide setting; use a custom number format (0;-0;;@) on the affected range instead.