Dash for a zero in Excel

Dash for a zero in Excel
Last Updated on 14/08/2023
Reading time: 2 minutes

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.

Dash is a text and not a numeric value by default in Excel

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
Open Custom format number

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)

Negative numbers in red with parenthesis

Related articles

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

Format code to display dash for 0

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

The zeros are displayed with a dash in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

Dash for a zero in Excel

Reading time: 2 minutes
Last Updated on 14/08/2023

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.

Dash is a text and not a numeric value by default in Excel

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
Open Custom format number

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)

Negative numbers in red with parenthesis

Related articles

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

Format code to display dash for 0

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

The zeros are displayed with a dash in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *