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:
- Select the cells that you want to apply the formatting to
- Use the keyboard shortcut CTRL 1 to open the Format Cells dialog box
- Click on the Number tab in the Format Cells dialog box, if it is not already selected
- Select Custom in the Category list
- 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:
- Select the Ribbon’s File tab
- Select Options
- In the Excel Options dialog, select Advanced down the left-side
- Scroll down to the section named Display options for this worksheet
- In the drop-down next to the section name, select your worksheet
- Untick the option Show a zero in cells that have zero value
- Click on OK.