How to display a dash ( - ) instead of a zero in your Excel cells?

Don't replace manually your zeros with a dash in Excel ⛔

This remark is very important. If you replace manually each 0 with a dash, you will create confusion between the text and numeric values ​​in the same column ☹️

• A dash is considered as text
• 0 is a number

For example, in this document, because of the dashes, Excel considers that not all the cells have a numeric value.

The COUNT function counts the number of numeric values. This is why here, you can see that the dashes are not included in the result.

How to display a dash automatically instead of a zero in Excel?

To display a dash instead of the number 0, you have to customize the format of your numbers.

1. First, open the Format number dialog box
2. Go to the Custom Category

How to customize the display of the numbers in Excel?

Excel offers 4 displays for numbers, in this order

1. Display of positive numbers
2. Display of negative numbers
3. Number zero
4. Text

Positive number;Negative number;0;Text

You also need to specify how the number will be displayed.

• Use 0 to display all the 0 (important to keep the leading 0)
• Use # to display the numbers except the 0 if not necessary

For instance, if you have the number 123

• The code #### (4 #) will display 123 in your cell
• The code 0000 (4 zeros) displays the value 0123
• And with the code ###0.00 the display will be 123.00 (no leading 0 but 0 for the decimals 😉)

Negative numbers

The display of negative numbers can be represented in red with the following code

#,##0.00; [Red] - #,##0.00

Or between parenthesis the code

#, ##0.00; [Red](#,##0.00)

Related articles

• Highlight the min and max values automatically
• Display negative number in parenthesis
• Quizzes for Excel
• Exercises Online for Excel
• Start Excel with your custom format number

Replace the 0 by a dash in Excel

To change a zero to a dash, you have to play on the 3rd argument of the code of a number.

So, by adding just a dash between quotation marks in the 3rd parameter, all your 0s will be replaced by dashes.

#,##0.00; [Red] - #,##0.00 ;"-"

Zeros are now displayed with a dash and the COUNT function returns 8 numeric values ​​for each column 😀