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.
- First, open the Format number dialog box
- 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
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.