This article will explain how to replace the 0 with a dash in your cells.

Difference between a number and text

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

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

In this example, we see that the number of numeric values ​​(returns with the COUNT function) doesn't include the dashes. Here, each dash as a Text format.

How to display a dash automatically instead of 0?

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 is displayed number in Excel?

Excel offers 4 display for numbers

• Display of positive numbers
• Display of negative numbers
• Number zero
• Text

Negative numbers

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

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

Or between parenthesis with the code

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

Change the display of 0

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 ;"-"

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

Start Excel with your custom parameters

You can save your customs format number in a template and load then when you open a new workbook. The method is explain in this article.