Excel: How to Display Dash Instead of Zero


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:

Excel display dash instead of zero

How This Formula Works

Recall the formula that we used in the Format Cells box:

#,##0;-#,##0;-

When you use this type of custom format, Excel interprets it as follows:

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

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Sum If Greater Than Zero
Excel: Use VLOOKUP to Return Blank Instead of Zero
Excel: How to Find Last Value in Column Greater than Zero

Featured Posts

Leave a Reply

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