How to display a dash ( – ) instead of a zero in your Excel cells automatically in your format number
- Open the custom number format
This is necessary to create you own custom format number in Excel
- Understand the way to create a custom code
In Excel, a number can have 4 differents statement
- Change the 3rd argument
To replace dash for a zero automatically, you must customize this argument
Don’t replace your zeros manually with a dash in Excel ⛔
This remark is very important. If you manually replace 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 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.
- First, open the Format number dialog box
- Go to the Custom Category
How to customize the display of the numbers in Excel?
Excel offers 4 displays for numbers, in this order
- Display of positive numbers
- Display of negative numbers
- Number zero
- 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 😉)
Replace the 0 by a dash in Excel
- To change a zero to a dash, manipulate the 3rd argument of the number code.
- Simply insert a dash enclosed in quotation marks as the 3rd parameter.
- This adjustment will replace all zeros with dashes.
#,##0.00; [Red] – #,##0.00 ;”-“
Zeros are now displayed with a dash and the COUNT function returns 8 numeric values for each column 😀
