Zeros clutter a report. A custom number format can hide them entirely or replace them with a clean dash — without deleting the values or affecting the math.
"–" in that section to show a dash instead.
The example
Zeros hidden so only real figures show.
| A | B | |
|---|---|---|
| 1 | Value | Displays |
| 2 | 1,250 | 1,250 |
| 3 | 0 | (blank) |
The formula
An empty third section hides zeros:
How it works
The zero section controls how zeros appear:
- Recall the sections: positive;negative;zero;text.
- Leave the zero section empty (just a trailing semicolon) to hide zeros.
- Or put
"–"(or any text) in the zero section to show a placeholder instead. - To hide zeros across the whole sheet, use File → Options → Advanced → uncheck “Show a zero in cells that have zero value.”
Hide everything with ;;; — three empty sections (;;;) hides positives, negatives, and zeros, leaving the cell visually blank while keeping its value. Handy for hiding helper cells without deleting them.
Try it: interactive demo
Zeros hidden; pick “hide” or “dash”.
Variations
Show a dash
Placeholder for zero:
Hide everything
Blank the cell visually:
Formula alternative
Blank via IF:
Pitfalls & errors
Value still there. Hidden zeros are still zeros — SUM and other formulas still count them. It’s display only.
Sheet-wide vs cell. The Options toggle hides zeros everywhere; the format code hides only formatted cells. Pick the scope you want.
;;; hides all. Three empty sections blank everything — useful, but don’t forget you applied it, or the cell looks empty when it isn’t.
Practice workbook
Frequently asked questions
How do I hide zeros in Excel without deleting them?
How do I show a dash instead of zero?
How do I hide a value entirely?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class