Conditional Formatting Tips and Tricks

Dash for 0 in Excel

Reading Time: 2 minutes

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

Dash as Text

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.

  1. First, open the Format number dialog box
  2. Go to the Custom Category
Open Custom format number

How is displayed number in Excel?

Excel offers 4 display for numbers

  • Display of positive numbers
  • Display of negative numbers
  • Number zero
  • Text

Negative numbers

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)

Negative numbers in red with parenthesis

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

Format code to display dash for 0

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

The Dash for 0

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.

Excel starts with your custom parameters

Related posts

Add filter option for all your columns in a pivot table

Frédéric LE GUEN

3D Function

Frédéric LE GUEN

Calculation without equal

Frédéric LE GUEN

Leave a Comment