How to hide zeros in Excel

Hiding zero (0) values in Excel can be especially helpful for de-cluttering your workbooks and in most cases, make them easier to read. In this guide, we’re going to show you how to hide zeros in Excel worksheets, following three different methods.

How to Hide Zeros in Excel from an Entire Sheet

This option is one of the easiest ways to hide the zero (0) values on an entire sheet. All you need to do is disable it from the corresponding checkbox in Excel Options. Here is how you can find this option:

1. Click File in the Ribbon
2. Click on Options to activate the Excel Options dialog
3. Activate the Advanced tab from the left pane
4. Scroll down to the section Display options for this worksheet:
5. Select the worksheet where you want to hide the zero values
6. Disable the Show a zero in cells that have zero value checkbox
7. Click OK to save your settings

That's all! Remember that this option applies only selected sheet. If you want to hide the zero values from the entire workbook, you need to disable this option for all worksheets.

Using Number Formatting

The next method involves using custom number formatting. So, you can set cell-specific rules rather than in the entire worksheet. Aside from Excel's built-in number format options like Currency, Date or Percentage, you can set your own custom number formatting. Let's see how you can apply a custom format.

1. Select the cells you want to hide if their values are zero.
2. Press Ctrl + 1 key combination to open the Format Cells dialog
3. Make sure the Number tab is active
4. Select Custom from the list
5. Enter 0;-0;;@ in the Type field
6. Click OK to apply your settings

An important note in here: The custom format replaces any existing number formatting. For example, if you set a field set to Currency, the cell will display $1,500.00 for the value of 1500. If you set 0;-0;;@ as the number formatting, the cell will display 1500 as 1500, without Currency formatting. To overcome this issue, you need to set currency formatting manually. For example, the following code both displays numbers as Currency and hides zeros:

For more information about custom number formats, please see: Number Formatting in Excel – All You Need to Know

Using Conditional Formatting

Conditional Formatting can be used to hide zeros in Excel with the use of cell based rules. Conditional Formatting feature applies pre-defined formatting options to the cells or ranges when a specific condition is met. The condition in our case would be whether a cell value is equal to zero.

1. Start by selecting the cells you want to apply hiding the zeros rule
2. Activate the Home tab in the Ribbon
3. Click Conditional Formatting
4. Click New Rule to open the conditional formatting dialog
5. Select the Format only cells that contain option in Select a Rule Type box
6. Change the selection in the second dropdown from between to equal to
7. Enter 0 (zero) to the input on the right
8. Click the Format button to open the Format Cells dialog
9. Set font color to the color that matches the worksheet background
10. Click OK button to apply the conditional formatting.

Your conditional formatting dialog should look like this: