See all How-To Articles

How to Hide / Suppress Zeros in Excel & Google Sheets

In this tutorial, you will learn how to hide zeros in Excel and Google Sheets.

 

hide zeroes initial data

 

There are several ways to hide zero values in Excel:

  • Hide zeros in the whole workbook using Excel Options.
  • Hide zeros using a custom number format.
  • Hide zeros with conditional formatting.

Hide Zeros – Excel Options

In the Excel Options, you can disable showing zeros for zero value for a worksheet or for a whole workbook. In this case, cells with zero values will be empty. Say you have the following data set with zero values in several cells.

 

hide zeroes initial data

 

To make zero cells empty, follow these steps:

1. In the Ribbon go to File > Options.

 

hide zeroes excel options 1

 

2. In the Excel Options window, go to Advanced, scroll down to the Display options, uncheck Show a zero in cells that have zero value, and click OK.

 

hide zeroes excel options 2

 

As a result, all cells with zero values are now empty.

 

hide zeroes final data

 

Hide Zeros With a Custom Number Format

Another option to suppress zeros is to use a custom number format.

1. Select a range of cells with zero values, go to the Home tab, and click on the Number format icon in the right lower corner of the Number group.

 

hide zeroes custom number format

 

2. In the Format Cells window, (1) go to the Custom category, (2) enter 0;0;;@ for Type, and (3) press OK.
If you have values with currencies, enter the currency first ($0;0;;@). Use this format for Columns D and F in our case.

 

hide zeroes custom number format 2

 

The result is the same as above with (Excel Options): All zeros are suppressed and displayed as empty cells.

 

hide zeroes final data

 

Hide Zeros With Conditional Formatting

A third option to hide zeros in a worksheet is to use conditional formatting. As a prerequisite for this method, all cells with zero values must have the same background color, so you can set the conditional formatting font color to the background color (in this case, white).

1. Select a range of cells that you want to format (e.g., D2:F10), and in the Ribbon, go to Home > Conditional Formatting > Highlight Cells Rules > Equal To.

 

hide zeroes conditional formatting

 

2. In the pop-up window, enter 0 in the left box, and choose Custom Format from the drop-down menu on the right.

 

hide zeroes conditional formatting 2

 

3. In the Format Cells window, click on the Color drop-down menu, choose white, and press OK.

 

hide zeroes conditional formatting 3

 

The result is again the same: All zero values are suppressed.

 

hide zeroes final data

 

Hide Zeros With a Custom Number Format in Google Sheets

To hide zeros by setting and applying a custom number format in Google Sheets, follow these steps:

1. Select a range of cells with zero values (D2:F10), and go to Format > Number > More Formats > Custom number format.

 

google sheets hide zeroes custom number format

 

2. In the Custom number formats window, enter 0;0;;@, and press Apply.
For values with currencies, enter the currency first ($0;0;;@). Use this for Columns D and F in this example.

 

google sheets hide zeroes custom number format 2

 

The result is the same as in Excel: All zeros are suppressed and displayed as empty cells.

 

google sheets hide zeroes custom number format final data

 

Hide Zeros With Conditional Formatting in Google Sheets

Just like in Excel, the background cell color needs to be uniform to create conditional formatting that suppresses zero values in Google Sheets.

1. Select a range of cells that you want to format (D2:F10) and in the menu, go to Format > Conditional Formatting.

 

google sheets hide zeroes conditional formatting

 

2. In the window on the right side, (1) choose Is equal to under format rule and (2) enter 0. For formatting style, (3) click on the Font color icon, (4) choose the color (white), and (5) click Done.

 

google sheets hide zeroes conditional formatting 1

 

You get the same effect again.

 

google sheets hide zeroes custom number format final data