Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to return blank instead of zero in Excel and Google Sheets
Topic:IF, IFS & nested conditionsExcel & Google Sheets
=IF(A2-B2=0,"",A2-B2)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Value A | Value B | Difference |
| 100 | 100 | |
| 150 | 100 | |
| 75 | 75 | |
| 200 | 120 |
=IF(A2-B2=0,"",A2-B2)→
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
Step by step
- 1Formula-level fix: wrap the calculation in IF and return an empty string for zero: =IF(A2-B2=0,"",A2-B2).
- 2Avoid computing twice in modern Excel with LET: =LET(v,A2-B2,IF(v=0,"",v)) evaluates the calculation once.
- 3Display-level fix: keep the real zeros but hide them with a custom number format — select the cells and apply the format 0;-0;;@ (positive;negative;zero;text — the empty third slot suppresses zeros).
- 4Sheet-level fix in Excel: File → Options → Advanced → untick "Show a zero in cells that have zero value" for the whole worksheet.
- 5The lookup case: VLOOKUP returns 0 when the found cell is empty. Fix with =IF(VLOOKUP(D2,A:B,2,0)="","",VLOOKUP(D2,A:B,2,0)) or =LET(v,VLOOKUP(D2,A:B,2,0),IF(v="","",v)).
Tips
- The empty string "" LOOKS blank but is not a true blank — ISBLANK returns FALSE for it, and downstream arithmetic like ""+1 gives #VALUE!. Guard downstream math with N() or IF tests.
- SUM, AVERAGE and COUNT ignore "" the same way they ignore blank cells, so totals stay correct.
- For charts, "" creates a gap; if you want the line to skip the point entirely in Excel, return NA() instead — the #N/A point is not plotted.
- Prefer the custom number format 0;-0;;@ when other formulas need the numeric zero: the value stays 0 underneath, only the display is blank.
- In Google Sheets the same custom format works via Format → Number → Custom number format.
- Zero-suppression applied to a percentage column: use 0%;-0%;;@ so real percentages keep their format.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Will the blank "" break SUM or AVERAGE?
No. SUM and AVERAGE skip text values, and "" is text — so totals and averages behave exactly as if the cell were empty.
Why does my VLOOKUP return 0 instead of blank?
When the looked-up cell is empty, VLOOKUP converts the blank to 0. Wrap it: =IF(VLOOKUP(D2,A:B,2,0)="","",VLOOKUP(D2,A:B,2,0)) — or use LET to avoid the double lookup.
Is a cell containing "" really blank?
No — it holds a zero-length text string. ISBLANK(A2) is FALSE, COUNTA counts it, and A2+1 errors. If those matters, hide zeros with the number format 0;-0;;@ instead, which keeps the underlying number.
How do I hide zeros for the whole sheet at once?
Excel: File → Options → Advanced → untick "Show a zero in cells that have zero value". Google Sheets has no sheet-wide switch — use the custom number format 0;-0;;@ on the range instead.
What should a chart do with the blank result?
"" plots as a gap in most chart types. In Excel, returning NA() instead makes line charts interpolate or skip the point cleanly; in Sheets, leave "" and enable "Plot null values" options per chart as needed.
More on IF, IFS & nested conditions
See all →How-to
How to hide #N/A and other formula errors
Replace #N/A, #DIV/0!, and other errors with a blank or a friendly message using IFERROR or IFNA. Works in Excel and Google Sheets.
Error fixFix Nested IF
Nested IF formulas not working in Excel and Google Sheets — fix bracket mismatches, wrong evaluation order, overlapping conditions, and the 64-nesting limit.
How-toHow to return a value if a cell contains text
Check if a cell contains specific text with IF + ISNUMBER + SEARCH and return any value you want — plus case-sensitive checks, multiple keywords, and wildcard alternatives.
How-toHow to sum if a cell contains specific text
Use SUMIF with wildcard criteria (*text*) to sum values in a column whenever a related column contains a specific substring.
How-toHow to write a nested IF formula
Chain multiple IF functions inside each other to test several conditions and return different results for each scenario.
How-toHow to write an IF with multiple conditions
Combine IF with AND or OR to test multiple conditions simultaneously and return a result based on whether all or any are true.