Hide Zero Values in Excel: Worksheet Options, Custom Formats, and Charts
To improve report readability, you need to apply hiding zero values in Excel appropriately for each situation. Here are safe methods for hiding zeros across an entire worksheet, hiding them only in a range with custom formatting, using conditional formatting, and excluding zeros from charts and statistics.
Quick Fix — Hide Zeros Throughout the Worksheet
- File → Options → Advanced → Display options (for this worksheet)
- Clear Show a zero in cells that have zero value
Hide Zeros Only in a Range — Custom Format
0;-0;;@Leave the third section (zero) blank to hide zeros. For example, you can extend it as #,#00.0;-#,#00.0;;@.
Use Conditional Formatting to Dim Only Zeros
Use a cell value = 0 condition to set the font color to match the background, or apply the ;;; format.
Make Only Formula Results of Zero Appear Blank
=IF(value=0, NA(), value)This is useful for removing the zero line from a chart. Alternatively, keep numeric zeros in the calculation area and hide them with formatting only in the display area.
PivotTables, Charts, and Statistics
- Charts:
#N/Ais not plotted, while 0 is plotted. - Average:
=AVERAGEIF(D2:D100,"<>0")
Method Comparison
| Method | Scope | Advantage | Consideration |
|---|---|---|---|
| Worksheet option | Entire worksheet | Very fast | Affects the entire worksheet |
| Custom format | Selected range | Safe for calculations | Be careful when copying formats |
| Conditional formatting | Selected range | Visibility control | Rule conflicts |
| Use #N/A | Charts | Hides data points | Shows errors in tables |
Pingback: MIN Zero Values in Excel: Why Stock Formulas Return 0