How to Make 0 a Dash in Excel

To show zeros as dashes in Excel, select your cells, press Ctrl+1 (Cmd+1 on Mac) to open Format Cells, choose Custom, and enter a four-section number format such as #,##0;-#,##0;"-";@ — the third section ("-") is what converts every 0 into a dash. The fastest no-typing alternative is the built-in Accounting format, which already displays zeros as a dash automatically.

Are you new to Excel and wondering how to make 0 a dash in the spreadsheet? Look no further. This simple but essential task can be completed in just a few clicks. Whether you’re working on financial statements or simply organizing data, using the dash symbol can make your reports clearer and easier to read. In this article, we’ll walk you through the steps to change 0 to a dash in Excel — using custom number formats, the Accounting format, and conditional formatting — so you can present your data in a way that is easy to understand.

Step-by-Step Guide to Making 0 a Dash in Excel

Before we begin, it’s important to note that changing 0 to a dash in Excel can be done in two ways: by formatting cells or by using conditional formatting.

Method 1: Format Cells

This method involves selecting the cells you want to format and applying a custom Number Format. Here’s how:

Select the cells you want to format.
Right-click and choose Format Cells, or press CTRL+1 (Cmd+1 on Mac).
Click on the Number tab.
Under Category, select Custom.
In the Type field, enter the following: #,##0;-#,##0;"-";@
Click OK.
Your 0s will now be displayed as dashes.

Understanding the four-section format code

Excel custom number formats are built from up to four sections separated by semicolons, in this fixed order:

positive ; negative ; zero ; text

So in #,##0;-#,##0;"-";@:

#,##0 formats positive numbers (with a thousands separator).
-#,##0 formats negative numbers.
"-" is the zero section — the quotes tell Excel to literally print a dash for any cell whose value is 0. This single section is what turns 0 into a dash.
@ is the text placeholder, so text entries display unchanged.

If you don’t need thousands separators, the shorter 0;-0;"-" does the same job: it shows positive numbers, negative numbers, and a dash for zero. (When you omit the fourth section, text simply displays as typed.)

Important: the dash only appears because you put "-" in the third section. A format like 0;-0;;@ — with an empty third section — does the opposite: it hides zeros entirely (the cell looks blank). See “Hiding zeros vs. showing a dash” below for the difference.

Method 2: Use Conditional Formatting

This method involves using conditional formatting to change all 0s to dashes in a selected range of cells. Here’s how:

Select the range of cells you want to format.
Click on Conditional Formatting in the Home tab.
Select New Rule.
Select Use a formula to determine which cells to format.
In the box under Format values where this formula is true, enter: =A1=0 (where A1 is the top-left cell of the selected range).
Click on the Format button.
Click on the Number tab.
Under Category, select Custom.
In the Type field, enter the following: "-"
Click OK until all the windows are closed.
Your 0s will now be displayed as dashes in the selected range of cells.

Because the conditional-formatting rule only applies to cells that already equal 0, the format code only needs the literal dash ("-"). This route is useful when you want the dash to appear conditionally (for example, only in a specific table) while leaving the underlying cell format untouched. For more on building rules, see how to use conditional formatting in Excel.

Method 3: Use the Built-in Accounting Format (Zero Already Shows as a Dash)

If you don’t want to type a custom code at all, Excel’s Accounting number format already displays zero values as a dash by default. This is why financial statements built in Excel naturally show dashes wherever a total comes to zero.

Select the cells you want to format.
On the Home tab, open the Number Format drop-down (the box in the Number group) and choose Accounting. On Mac, the same option lives under Home → Number Format, or press Cmd+1 and pick Accounting.
Optionally set the number of decimal places and the currency symbol (choose “None” if you want a plain dash with no currency sign).

Every cell that evaluates to 0 now shows a dash, aligned to where the digits would be. If you’d rather format the numbers as money first, see how to apply the comma-style / accounting number format and how to add a dollar sign in Excel.

Hiding Zeros vs. Showing a Dash

It’s worth being clear about the difference, because “get rid of the zeros” can mean two very different things:

Showing a dash keeps a visible placeholder (-) in the cell. The reader can see that the value is genuinely zero, which is the convention in accounting. Use #,##0;-#,##0;"-";@ (or the Accounting format).
Hiding zeros makes the cell appear completely blank even though it still contains 0. Use the format #,##0;-#,##0; (note the trailing semicolon and the empty third section) to hide zeros for selected cells only.

In both cases the underlying value is still 0 — only the display changes — so formulas, rounding, and totals are unaffected.

Hide zeros across the whole worksheet

If you want to suppress every zero on a sheet without touching individual cell formats, Excel has a worksheet-wide switch:

Windows: File → Options → Advanced → under Display options for this worksheet, uncheck “Show a zero in cells that have a zero value.”
Mac: Excel → Preferences → View → uncheck “Show zero values.”

This blanks out zeros everywhere on the chosen sheet. Note that it hides zeros — it does not replace them with a dash. To get a dash, use a custom format or the Accounting format instead. For related guidance on preserving zero entries (for example in ID numbers), see how to keep zeros in Excel and how to add a leading zero in Excel.

In Conclusion

By using any of these methods, you can easily change 0 to a dash in Excel. This can help enhance the readability of your reports and make it easier for readers to understand. We hope this tutorial has been helpful.

Why Use Dashes in Excel

When working with financial reports or any other type of document that involves numbers, using dashes instead of zeroes can have a few advantages. For example, a dash can make it easier to spot missing data in a table. It can also help to avoid confusion when dealing with large amounts of data, where multiple zeroes in a row can be difficult to read.

Customizing the Dash Symbol

Did you know that you can customize the dash symbol to fit your preferred style? Excel allows you to use different characters instead of the standard dash. For example, you can use a longer dash (—) or a shorter one (-). You can even use symbols like brackets, asterisks, and more.

Customizing with Format Cells

To customize the dash symbol using Format Cells:

Select the cells you want to format.
Right-click and choose Format Cells, or press CTRL+1.
Click on the Number tab.
Under Category, select Custom.
In the Type field, enter a custom format code using the desired character. For example, to use an asterisk as the dash symbol, enter: 0;-0;;*
Click OK.

Customizing with Conditional Formatting

To customize the dash symbol using Conditional Formatting:

Select the range of cells you want to format.
Click on Conditional Formatting in the Home tab.
Select New Rule.
Select Use a formula to determine which cells to format.
In the box under Format values where this formula is true, enter: =A1=0 (where A1 is the top-left cell of the selected range).
Click on the Format button.
Click on the Number tab.
Under Category, select Custom.
In the Type field, enter a custom format code using the desired character. For example, to use an asterisk as the dash symbol, enter: *;;*
Click OK until all the windows are closed.

In Summary

Changing 0 to a dash in Excel can be a simple but effective way to make your documents easier to read. Whether you use the standard dash or a customized one, this formatting trick can save you time and enhance the presentation of your work.

FAQ about Making 0 a Dash in Excel

Here are a few frequently asked questions about changing 0 to a dash in Excel:

What is the difference between a dash and a zero?

In Excel, dashes are used to indicate missing or incomplete data, while zeroes represent actual data values that happen to be zero. Using dashes can make it easier to spot and interpret missing data in a table or report.

Why can’t I simply replace all zeroes with dashes?

You technically can do this, but it would replace any actual zeroes in your data as well. Additionally, other users who view your file may not be aware of the substitution and misunderstand the data.

Are there any other characters I can use instead of the dash?

Yes! There are many characters you can use instead of the dash, including asterisks, brackets, and other specialized symbols. Refer to Excel’s formatting guidelines for a complete list of available characters.

Can I use the dash-and-double-dash convention?

Yes, you can use the dash-and-double-dash convention to represent different types of missing data. For example, you could use a single dash for missing data and a double dash for data that is intentionally blank. Customize the format code according to your needs.

How can I remove the dash formatting from a cell?

To remove the dash formatting from a cell and return it to a regular number format, select the cell and press “CTRL + 1” to open the Format Cells dialogue. Then, under Category, select the Number format that you want to use and click OK.

Troubleshooting

My zeros disappeared instead of turning into a dash. Your custom code probably has an empty third section (e.g. 0;-0;;@), which hides zeros. Put a quoted dash in the third section instead: #,##0;-#,##0;"-";@.
The dash shows but my positive/negative numbers changed format. The format code controls all four sections at once. If you only typed "-" in Format Cells (rather than via conditional formatting), it overrides the other sections. Use the full four-section code, or apply the dash through conditional formatting so the base format is preserved.
Cells still show 0, not a dash. Make sure you applied the format to the right cells and that the value is a true 0 — a cell containing text like “0” or a number stored as text won’t trigger the zero section.
The whole sheet is blank where I expected dashes. You likely turned off “Show a zero in cells that have a zero value” in Options. That hides zeros sheet-wide; re-enable it and use a custom format for dashes instead.
Negative results look wrong. Custom formats display the value as-is. If you need to flip the sign, see how to make a negative number positive in Excel.

Frequently Asked Questions

What is the exact custom format to show 0 as a dash?

Use #,##0;-#,##0;"-";@ in Format Cells → Custom. The four sections are positive, negative, zero, and text; the quoted dash in the third (zero) section is what prints a dash for any value of 0. A shorter version is 0;-0;"-".

How do I show zeros as dashes on a Mac?

The steps are the same. Select the cells, press Cmd+1 to open Format Cells, choose Custom, and enter #,##0;-#,##0;"-";@. Or apply the Accounting format from the Home tab, which shows zeros as dashes automatically.

What’s the difference between hiding a zero and showing a dash?

Hiding a zero (#,##0;-#,##0; with an empty third section, or the worksheet option in File → Options → Advanced) leaves the cell looking blank. Showing a dash ("-" in the third section, or the Accounting format) keeps a visible - placeholder so readers know the value is genuinely zero.

Does the Accounting format show 0 as a dash automatically?

Yes. Excel’s built-in Accounting number format displays any zero value as a right-aligned dash by default, which is why it’s the standard choice for financial reports. You can set the currency symbol to “None” if you want a plain dash with no currency sign.

Will replacing 0 with a dash change my formulas or totals?

No. Custom number formats and the Accounting format only change how the value is displayed — the underlying number is still 0. Any SUM, average, or other formula referencing the cell continues to treat it as zero.

How do I hide every zero on the whole sheet at once?

On Windows go to File → Options → Advanced and uncheck “Show a zero in cells that have a zero value.” On Mac go to Excel → Preferences → View and uncheck “Show zero values.” This blanks out all zeros on the sheet, but it hides them rather than showing a dash — use a custom format for dashes.