Dash for a zero in Excel automatically

Dash for a zero in Excel automatically
Last Updated on 31/03/2024
Reading time: 2 minutes

How to display a dash ( - ) instead of a zero in your Excel cells automatically in your format number

  1. Open the custom number format

    This is necessary to create you own custom format number in Excel

  2. Understand the way to create a custom code

    In Excel, a number can have 4 differents statement

  3. 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.

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

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

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 automatically

Reading time: 2 minutes
Last Updated on 31/03/2024

How to display a dash ( - ) instead of a zero in your Excel cells automatically in your format number

  1. Open the custom number format

    This is necessary to create you own custom format number in Excel

  2. Understand the way to create a custom code

    In Excel, a number can have 4 differents statement

  3. 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.

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

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

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 *