Table of Contents
A formula in Excel can be set up to return a blank cell instead of a zero when the formula result is zero. This can be done by including an IF statement in the formula that checks if the result is equal to zero and, if so, returns a blank cell. This helps to make the data in a spreadsheet clearer by avoiding the clutter of unnecessary zeros and displaying only the results that are relevant.
You can use the following basic syntax to return a blank cell instead of a zero when using Excel formulas:
=IF(B2/C2=0, "", B2/C2)
This particular formula attempts to divide the value in cell B2 by the value in cell C2.
If the result is zero, then Excel returns a blank. Otherwise, Excel returns the result of B2 divided by C2.
The following example shows how to use this formula in practice.
Example: Return Blank Cell Instead of Zero in Excel Formula
Suppose we have the following dataset that shows the total sales and returns for various products:
Suppose we type the following formula into cell D2 to calculate the return rate for product A:
=B2/C2
If we copy and paste this formula down to every cell in column D, we’ll see that some of the results are equal to zero:
To return a blank value instead of a zero, we can type the following formula into cell D2:
=IF(B2/C2=0, "", B2/C2)
We can then copy and paste this formula down to every remaining cell in column D:
Notice that every cell where the formula would return a zero now returns a blank value instead.