Cellwise Start free

Excel guide · 5 min read

How to hide zero values in Excel

Quick answerTo hide every zero on a sheet, go to File > Options > Advanced, scroll to Display options for this worksheet and clear Show a zero in cells that have zero value. To hide zeros in selected cells only, press Ctrl+1, choose Custom and type 0;-0;;@. Both only change what you see: the cells still hold 0, so totals, counts and averages are unchanged.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEF
1RepJanFebMarQ1 totalMar (blank if 0)
2Ana Ruiz4200380008000
3Ben Okafor0000
4Chloe Martin31000290060002900
5Dev Patel560061005900176005900
6Elena Novak02400330057003300
7Femi Adeyemi2800350006300

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.
=COUNTIF(B2:D7,0)

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.

  1. Click the sheet you want to clean up.
  2. Go to File > Options > Advanced.
  3. Scroll to Display options for this worksheet and check the sheet name in the drop-down.
  4. 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.

  1. Select B2:E7.
  2. Press Ctrl+1 (Cmd+1 on a Mac) and click Custom at the bottom of the Category list.
  3. Type 0;-0;;@ (blank) or #,##0;-#,##0;"-" (dash) in the Type box and click OK.
  4. To undo it, select the cells, press Ctrl+1 and pick General or Number.
=TEXT(D2,"#,##0;-#,##0;""-""")

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.

=AVERAGE(F2:F7)

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.

=AVERAGEIF(D2:D7,"<>0")

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.

=COUNT(D2:D7)

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.

=F2+F3

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

Related guides

Updated