Table of Contents
You can use the Format Cells feature in Excel with the following custom format code to display all zeros as dashes in a particular range:
#,##0;-#,##0;-
The following example shows how to use this custom format code in practice.
Example: How to Display Dash Instead of Zero in Excel
Suppose we have the following dataset in Excel that shows the total sales made by various employees at some company:
Suppose we would like to display each zero in the Sales column as a dash instead.
To do so, we can highlight the cell range B2:B13, then type Ctrl + 1 to bring up the Format Cells dialog box.
Then we can click Custom from the Category options and then type the following formula into the Type box:
#,##0;-#,##0;-
The following screenshot shows how to do so:
Once we click OK, each zero in the range B2:B13 will now be shown as a dash instead:
How This Formula Works
Recall the formula that we used in the Format Cells box:
#,##0;-#,##0;-
- The first argument specifies how to format positive values
- The second argument specifies how to format negative values
- The third argument specifies how to format values equal to zero
By using a dash ( – ) for the last argument, we specify that all values equal to zero should be displayed as dashes instead.