How to Hide Zeros in Excel: Whole Sheet, Some Cells, or a Formula
There are three ways to hide zeros. Choose based on where you want them hidden and whether you'll calculate with the cells later.
2026-11-10
Key points
- 1For some cells, a number format is easiest
- 2For the whole sheet, use Excel Options
- 3A formula that returns
""produces text
Comparing the three methods
| Item | Apr | May |
| Notebook | 120 | 0 |
| Pen | 0 | 80 |
| File | 0 | 0 |
| Item | Apr | May |
| Notebook | 120 | |
| Pen | 80 | |
| File |
| Method | Where zeros disappear | Cell value | Best for |
|---|---|---|---|
| Number format | Selected cells | Still 0 | Hiding zeros in part of a table |
| Excel Options | Whole sheet | Still 0 | Hiding zeros on the whole sheet |
| IF function | Formula cells | Empty text | Blanking formula results |
Hide with a number format
- 1
Select the cells where zeros should disappear
- 2
Open Format Cells with Ctrl + 1 (⌘ + 1 on a Mac)
- 3
On the Number tab, choose Custom
- 4
Enter
#,##0;-#,##0;in Type and click OKLeaving the third (zero) section empty hides only zeros.
The cell value is still 0, so you can keep using it in calculations. For columns that also contain text, use #,##0;-#,##0;;@.
Hide on the whole sheet
- 1
Open File → Options
- 2
Choose Advanced
- 3
Under "Display options for this worksheet", pick the sheet
- 4
Clear "Show a zero in cells that have zero value" and click OK
Note
On a Mac, go to Excel → Preferences → View and clear Zero values. The setting applies per sheet.
Return a blank with IF
Return a blank when the result is 0.
| Goal | Formula |
|---|---|
| Blank if the difference is 0 | =IF(A2-B2=0,"",A2-B2) |
| Blank if the referenced cell is empty | =IF(A2="","",A2) |
Referring to an empty cell with =A2 shows 0. Use the second formula in that case.
Note
"" is empty text. Adding that cell with + gives a #VALUE! error. Use SUM, which ignores text, for totals.
Show 0 as "-"
Where a blank would look like missing data, show 0 as "-" instead. Use the custom format #,##0;-#,##0;"-".
FAQ
QWhat happens to charts when I hide zeros?
If you hide zeros with a format or an option, the value is still 0, so charts still plot it as 0.
QMy problem is the opposite: leading zeros disappear.
To enter values like 001, use the custom format 000, or set the format to Text before typing.
Related guides and practice
Readable Spreadsheet Drill →
36 exercises that fix hard-to-read tables in a virtual Excel
Building a table →
8 steps from data entry to finishing touches
Merge Cells →
Merging and unmerging, and centering without merging
Conditional Formatting →
Colors based on values: whole rows, multiple conditions, weekends
Custom Number Formats →
Thousands, units, red negatives, dates with weekdays
Border →
Choosing lines, double lines under totals, removing stubborn lines