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.
- 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
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 😉)
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)
- 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 😀