Excel guide · 5 min read
How to hide zero values in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Rep | Jan | Feb | Mar | Q1 total | Mar (blank if 0) |
| 2 | Ana Ruiz | 4200 | 3800 | 0 | 8000 | |
| 3 | Ben Okafor | 0 | 0 | 0 | 0 | |
| 4 | Chloe Martin | 3100 | 0 | 2900 | 6000 | 2900 |
| 5 | Dev Patel | 5600 | 6100 | 5900 | 17600 | 5900 |
| 6 | Elena Novak | 0 | 2400 | 3300 | 5700 | 3300 |
| 7 | Femi Adeyemi | 2800 | 3500 | 0 | 6300 |
Download this sheet as an Excel practice file (.xlsx)
Pick the right method first
The sample is a Q1 sales sheet. Ben joined in March and has no sales yet, and three reps had a zero month. Seven of the 18 monthly cells are 0, and a page full of zeros hides the numbers that matter. There are three ways to clean it up, and they behave differently:
- The sheet option hides every zero on one sheet. Quickest, but it's easy to forget it's on.
- A custom number format hides zeros in the cells you choose, or shows a dash instead.
- An IF formula that returns "" really removes the zero. That changes AVERAGE, COUNT and charts, which is sometimes what you want and often isn't.
Result: 7
7: the zeros you're about to hide. The same count comes back after hiding them with options 1 or 2, because the values are still 0.
Method 1: hide every zero on the sheet
This is a display setting saved with each worksheet, like gridlines. Turning it off on the Q1 sheet doesn't touch the other sheets in the workbook. On a Mac it's Excel > Preferences > View, then clear Zero values under Show in workbook.
- Click the sheet you want to clean up.
- Go to File > Options > Advanced.
- Scroll to Display options for this worksheet and check the sheet name in the drop-down.
- Clear Show a zero in cells that have zero value and click OK.
Method 2: a custom number format (blanks or dashes)
A number format has up to four sections: positive;negative;zero;text. Leave the zero section empty and zeros disappear from the selected cells only. Microsoft's own example is 0;-0;;@. Use #,##0;-#,##0; if you also want thousands separators.
Accountants often prefer a dash, because a blank cell can look like someone forgot to fill it in. Put the dash in the zero section in quotes: #,##0;-#,##0;"-". The TEXT function uses the same codes, which is how you can check one before you apply it.
- Select B2:E7.
- Press Ctrl+1 (Cmd+1 on a Mac) and click Custom at the bottom of the Category list.
- Type 0;-0;;@ (blank) or #,##0;-#,##0;"-" (dash) in the Type box and click OK.
- To undo it, select the cells, press Ctrl+1 and pick General or Number.
Result: -
"-": Ana's zero March shows as a dash. Inside a formula each quote in the code is doubled; in the Format Cells box you type #,##0;-#,##0;"-" as it is.
Method 3: IF returning "", and what it changes
=IF(D2=0,"",D2) in column F shows the March figure, or nothing when it's 0. This is different from methods 1 and 2: the cell now holds empty text, not 0. SUM skips text, so the total is fine. But AVERAGE and COUNT skip it too, so the average jumps.
Result: 4033.3333333
4033.33: the average of the three reps who sold something in March. AVERAGE(D2:D7) on the real values gives 2016.67, because it counts the three zeros.
Which average is right?
It depends on the question. "Average March sales per rep" needs the zeros; "average sale when a rep sold anything" doesn't. If you want the second, say so with AVERAGEIF instead of hiding the zeros and getting it by accident.
Result: 4033.3333333
4033.33, the same as the "" column, but now the rule is visible in the formula.
Do the hidden zeros from methods 1 and 2 still count?
Yes. COUNT sees all six March values, zeros included. More on this in average excluding zeros.
Result: 6
6, while COUNT(F2:F7) gives 3.
The "" trap: #VALUE! in later formulas
Empty text breaks plain arithmetic. Anyone who later adds two cells with + or multiplies one gets an error wherever a zero used to be. SUM, AVERAGE and other range functions skip text, so =SUM(F2:F7) still gives 12100. If other people build on your sheet, a number format (method 2) is the safer choice.
Charts have the same problem. Excel doesn't treat "" as an empty cell, so a line chart plots it as zero instead of leaving a gap. If you want a gap, return NA() instead of "" and check the chart's Select Data > Hidden and Empty Cells settings.
Result: #VALUE!
#VALUE!: both cells hold "" instead of 0, and + can't add text.
Hide zeros in a PivotTable
Two different things look like zeros in a PivotTable. Cells with no data at all (no sales for that rep and month) are blank by default; PivotTable Options > Layout & Format > For empty cells show controls what appears there, so clear it or leave its box empty. Sums that really add up to 0 are numbers, so hide them with a number format on the value field: right-click a value, choose Number Format, then Custom and 0;-0;;@. Setting it through Number Format on the field, rather than formatting the cells directly, keeps it attached to the field as the PivotTable changes.
If zeros appear in a lookup or link instead, such as a formula pointing at an empty cell returning 0, see check if a cell is blank and handle the blank in the formula itself.
Common mistakes
Forgetting the sheet option is off
A colleague types 0 and sees nothing. Add a note on the sheet, or use a dash format so zeros stay visible as zeros.
Using "" and then averaging the column
"" drops out of AVERAGE and COUNT. Average the real values, or use AVERAGEIF with "<>0" on purpose.
Hiding zeros with white font
It prints badly, fails on coloured fills and confuses everyone. Use a number format instead.
Typing ;;; to hide zeros
Three semicolons hide every value in the cell, not just zeros. Use 0;-0;;@ for zeros only.
FAQ
How do I make Excel show a blank instead of 0?
Select the cells, press Ctrl+1, choose Custom and type 0;-0;;@. The cells look blank when they're 0 but still hold the number, so formulas keep working.
How do I show a dash instead of zero in Excel?
Use the custom format #,##0;-#,##0;"-". The third section is the zero section, and the dash in quotes replaces the 0.
How do I hide zeros in Excel for the whole sheet?
File > Options > Advanced > Display options for this worksheet, then clear Show a zero in cells that have zero value. It applies to that one sheet.
Why does my formula show 0 instead of blank?
A formula that points at an empty cell returns 0. Hide it with a 0;-0;;@ format, or wrap it as =IF(A2="","",A2) if you really need empty text.
Do hidden zeros still count in AVERAGE?
Yes, if you hid them with the sheet option or a number format: the value is still 0. Only a formula returning "" removes them from AVERAGE and COUNT.
Functions used
COUNTIFCounts cells that matchTEXTNumber to formatted textIFPicks a result from a testAVERAGEMean of numbersAVERAGEIFAverages cells that matchCOUNTCounts numbers
Related guides
- Average excluding zeros (and blanks) in Excel
- How to show negative numbers in red in Excel
- How to combine text with numbers and dates in Excel
- Excel IF cell is blank formula (="" vs ISBLANK)
- Excel formula returns 0 instead of the value: 7 causes and fixes
- How to calculate service level in Excel
Updated