Hiding Zeros in Cells or Worksheets

Excel - Formatting and Reports

As you would expect in Excel there are a couple of ways of hiding zeros. In fact, there’s a bunch of ways to hide zeros. Use the one that’s best for your circumstance.

To hide zeros throughout a worksheet in Excel 2007/2010 (which usually isn’t a good idea),

1. Choose File, Options to display the Excel Options dialog box.

2. Select the Advanced options.

3. In the Display Options for this Worksheet deselect the Show a Zero in Cells that Zero Value.

This hides the display of all zeros in the worksheet.

To hide zeros using a custom format, use the custom format,

#,##0.00;#,##0.00;

In this case there is no custom format in the third format location, so the zero values are hidden.

To conditionally hide zeros using a formula, use the IF function to select what you show or hide,

=if(sum($C$15:$C$30)=0,””,sum($C$15:$C$30)=0)

Share the power...