Table of Contents
To display a dash instead of zero in Excel, you can use the custom number format feature. This allows you to specify how numbers are displayed in a cell. By setting the format to “0;-0;;@”, any cell with a value of zero will be replaced with a dash. This is a useful way to visually indicate that a value is missing or not applicable. Additionally, you can also use conditional formatting to automatically change the format of cells containing zero to a dash. This can be done by setting a rule that applies the custom number format to cells with a value of zero. Using these methods, you can easily display a dash instead of zero in Excel to improve the clarity and presentation of your data.
Excel: 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:
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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: