To show a blank instead of a zero, use =IF(A2=0,"",A2). The two empty quotes mean “return nothing”, so any cell holding 0 comes back looking empty while everything else passes through unchanged.
Wrap an existing calculation the same way: =IF(B2-C2=0,"",B2-C2). One thing to know before you use it — the result is an empty text string, not a truly empty cell. It looks blank but ISBLANK returns FALSE, COUNTA counts it, and any formula that does maths on it returns #VALUE!.
If you only want to hide zeros on screen, two better options leave the numbers intact: uncheck File → Options → Advanced → Show a zero in cells that have zero value to hide them across the whole sheet, or apply the custom format 0;-0;;@ (Ctrl+1 → Custom) to hide them in selected cells only. Both keep the values real, so your totals still work.
To create an “IF 0 Then Blank” formula in Excel, use the formula: =IF(A1=0, “”, A1). This checks if the value in cell A1 is 0. If true, it returns a blank cell (“”). Otherwise, it returns the value of A1.
Let me break it down:
- Part-1: A logical test that checks if A1=0 (if the value in A1 is 0).
- Part-2: If the logical test returns TRUE, the formula returns a blank value.
- Part-3: If the logical test returns FALSE, the formula returns the value of A1.
In the following example, we have a list of numbers in column A, and you need to write a condition that shows a blank for every zero.
Formula to Check IF 0 (Zero) Then Blank
- First, edit cell B2, enter an equal sign (=), and then enter the IF function to start the formula.
- After that, in the first argument, specify the condition to check values from cell A2 (A2=0).
- Now, in the second argument, specify a blank value (two double quotes, “”).
- And in the third argument, refer back to cell A2.
- In the end, enter the closing parenthesis.
Here’s the formula to check if a cell has a zero value and change it to a blank.
=IF(A2=0,"",A2)
How this Formula Works
Now, let’s try to understand how this formula works.
This formula works in three parts; in the first part, we have a condition that checks if the cell has a zero or not.
IF – We have used the IF function to write a conditional formula.
A2=0 This is the condition where we compare the value in cell A2 with 0. This is the part where we will get the condition’s result as TRUE or FALSE. The condition is TRUE when the value in the cell is 0, and FALSE when the value in the cell is not zero.
“”,A2 – These are the two values that the formula returns: a blank when the condition is met, and the value of A2 when it isn’t.
Note that an empty cell also counts as 0 in this test, so the formula returns a blank for empty cells as well. But a zero stored as text (“0”) isn’t equal to the number 0, so the formula returns it as it is.
Show a Blank When a Calculation Returns Zero
Most of the time, the zero comes from a formula, like B2-C2. In that case, use the calculation in both the condition and the third argument:
=IF(B2-C2=0,"",B2-C2)
In Microsoft 365 and Excel 2021, you can use LET to write the calculation only once:
=LET(x,B2-C2,IF(x=0,"",x))
Hide Zeros without a Formula
If you only want the zeros not to show, and the cells can keep their values, you don’t need IF at all:
- Custom number format: select the cells, press Ctrl + 1, go to Custom, and enter the format 0;-0;;@. The third part is empty, so zeros show as blank cells.
- Whole worksheet: go to File ➜ Options ➜ Advanced, and in “Display options for this worksheet”, untick “Show a zero in cells that have zero value”.
With both these options, the zero is still in the cell, so other formulas that use it still work. That’s the main difference from the IF formula, which returns text (“”).
Frequently Asked Questions
Why do I get #VALUE! after using IF zero then blank?
The blank that IF returns (“”) is text. SUM ignores it, but a formula like =B2+1 on that cell returns #VALUE!. Use SUM(B2,1) instead, or hide the zeros with a number format.
Does the formula also treat empty cells as zero?
Yes. An empty cell is equal to 0 in a comparison, so =IF(A2=0,””,A2) returns a blank for it too.
Can I show a dash instead of a blank?
Yes. Replace the “” in the formula with “-“, or use the custom number format 0;-0;”-“;@ to show a dash for zeros.
=IF(A2=0,””,A2-A1) BUT #VALUE! ERROR SHOW
more parenthies to specify final result of subtraction. savii