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 a blank value.
In the following example, we have a list of numbers in column A, and you must write a condition.
Formula to Check IF 0 (Zero) Then Blank
- First, edit cell B2, enter the (=), 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 zero.
- And in the third argument, refer to cell A2 back.
- In the end, enter the closing parentheses.
Here’s the formula to check if a cell has a zero value and change it to a zero.
=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 where we have a condition that checks if the cell has a value) 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. If the condition is TRUE (that means the value in the cell is 0), and if the condition is FALSE (that means the value in the cell is not zero).
“”,A2 – These are the two returning values which the formula returns, when conditions is met or doesn’t met.
=IF(A2=0,””,A2-A1) BUT #VALUE! ERROR SHOW
more parenthies to specify final result of subtraction. savii