Skip to content

Excel – How to Return a Blank Cell If Zero

    In this tutorial, I explain three ways to display zeros as blanks. Download the featured file here.

    Method One: Use the IF Function

    Using the IF function we can return an empty text string in place of a zero result.  In the example below the current stock level is calculated by subtracting the Sold value from the Stock Level value.

    Using an IF function we can use a logical test that evaluates whether the current stock level equals zero. Watch this part of the video here.

    =IF(C3-D3=0,””,C3-D3)

    The value if true result is an empty text string “”

    =IF(C3-D3=0,“”,C3-D3)

    Method Two: Use Custom Formatting

    The second method uses custom formatting to display zeros as blanks. Watch this part of the video here. To apply this custom formatting:

    1. Select the cells that you want to apply the formatting to
    2. Use the keyboard shortcut CTRL 1 to open the Format Cells dialog box
    3. Click on the Number tab in the Format Cells dialog box, if it is not already selected
    4. Select Custom in the Category list
    5. In the Type box type the following: 0;-0;;@  To get an explanation of this syntax, please watch the video beginning here.

    Method Three: Worksheet Option

    This method will hide zeros across the whole worksheet. Watch this part of the video here. To use this method:

    1. Select the Ribbon’s File tab
    2. Select Options
    3. In the Excel Options dialog, select Advanced down the left-side
    4. Scroll down to the section named Display options for this worksheet
    5. In the drop-down next to the section name, select your worksheet
    6. Untick the option Show a zero in cells that have zero value
    7. Click on OK.