Excel Number Format: How to Show Zero as a Dash in Cells

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, choose Custom, and enter #,##0;(#,##0);"-";@. The third section displays numeric zero as a dash; the cell still contains the value 0.

Set up the custom format

Use this format when you want whole numbers with thousands separators, negative numbers in parentheses, and a dash for zero. To apply it in Excel for Microsoft 365 on Windows:

  1. Select the cells, column, or range to format.
  2. Press Ctrl+1 to open Format Cells. You can also use Home > Number group and click the small dialog box launcher.
  3. On the Number tab, select Custom in the Category list.
  4. Enter #,##0;(#,##0);"-";@ in the Type box, then select OK.

In Excel for Mac, select the cells and press Command+1 to open Format Cells, then choose Number > Custom and enter the format. Ribbon layout can vary slightly by release. Excel for the web has fewer custom-format editing options than the desktop app; if you cannot enter the code there, open the workbook in desktop Excel to create it.

The example makes 1,250 appear as 1,250, -1,250 appear as (1,250), and 0 appear as a dash. It changes only how numeric values look in the worksheet, not the values stored in the cells.

How Excel reads the format code

A custom number format can contain up to four sections separated by semicolons. In a four-section format, the sections are for positive numbers, negative numbers, zero, and text, in that order. The example #,##0;(#,##0);"-";@ therefore breaks down as follows:

  • #,##0 displays positive numbers with a thousands separator and no decimal places.
  • (#,##0) displays negative numbers in parentheses.
  • "-" displays the dash when the numeric value is zero. Quotation marks make the hyphen a literal character.
  • @ displays text as entered in cells covered by the format.

You do not have to define all four sections. For example, 0;-0;"-" is a shorter three-section format: it shows positive values without a grouping separator, negative values with a minus sign, and zero as a dash. If you omit the text section, text generally displays as text rather than being converted into a number. Including @ makes the intended text behavior explicit.

Choose a format that fits the data

The example is not the only useful pattern. Decide how you want ordinary and negative values to appear, then put the dash format in the third section. These examples use a literal hyphen for zero:

  • 0;-0;"-";@ shows integers without commas and puts a minus sign before negative values.
  • #,##0.00;(#,##0.00);"-";@ shows two decimal places and puts negative values in parentheses.
  • #,##0.##;(#,##0.##);"-";@ allows up to two decimal places, omitting unnecessary trailing zeros.
  • 0%;(0%);"-";@ shows percentages with no decimal places, using parentheses for negatives.

Consider the level of precision readers need. A format with two decimal places rounds the displayed result to two places, but it does not round the stored number. For instance, a stored value of 3.456 displays as 3.46 under a two-decimal format, while calculations still use the underlying value. For percentage data, remember that Excel stores 0.25 as 25% when displayed as a percentage; zero remains numeric zero and receives the dash section.

Use a dash with currency or accounting data

For currency, include the currency symbol in the positive and negative sections while retaining the zero section. One example for dollars is $#,##0.00;($#,##0.00);"-";@. It displays positive dollar amounts with two decimal places, negative amounts in parentheses, and zero as a dash without a dollar sign on the dash. If your reporting convention requires a currency symbol beside zero, use $#,##0.00;($#,##0.00);"$-";@ instead. Choose a symbol and decimal precision appropriate to the workbook and its audience.

Excel’s built-in Accounting number format commonly displays zero values as a dash and aligns currency symbols and decimal points in a column. It may be the better choice for financial statements where that alignment is useful. To try it, select the cells and use Home > Number Format, then choose Accounting Number Format. The symbol and exact appearance can depend on locale and format settings. If you need a particular treatment of negatives, decimals, or the zero dash, a custom format gives you more control.

A custom dollar-sign code is not a universal currency setting. Workbooks used across regions may use different symbols, decimal separators, and thousands separators. Excel can interpret and display number formats according to regional settings. Check the result on the computer or in the locale where the workbook will be used, especially before publishing a report.

A dash is not the same as a blank

Number formatting affects appearance, not the cell’s contents. If A1 contains 0 and uses a dash-for-zero format, Excel still treats A1 as zero in calculations, sorting, and most numeric tests. A formula such as SUM can include it, and a comparison with zero can still identify it as zero. The dash is a visual convention often used to make tables easier to scan, not a deletion or replacement of data.

This distinction matters when a worksheet uses formulas or exports. A zero formatted as a dash remains a numeric value when copied or used in another calculation. A cell that contains an empty string, such as the result of a formula designed to return no visible text, is different: it is text rather than numeric zero. A truly empty cell is different again. Those differences can affect counts, filters, charts, and downstream calculations.

In particular, changing the format does not make a zero cell blank. If you need to omit zero values from a calculation or report, decide that in the formula or reporting logic rather than relying on the display format. Be cautious about replacing zeros with empty text: that may alter how totals, averages, and other formulas behave.

Common issues and how to fix them

  • The zero still appears as 0: Check that the selected cell is using the custom format and that the dash is in the third section. In a four-section format, the third section is for zero, not negative values. Also check whether the cell contains text such as the character 0 rather than the numeric value 0.
  • A small value displays as a dash: The value may be nonzero but rounded to zero at the displayed precision. For example, a value such as 0.004 shown with two decimal places can appear as 0.00, not as a zero-section dash; formats and rounding can make small amounts difficult to distinguish. Increase displayed precision if the distinction matters.
  • Changing decimals seems to change the number: A number format ordinarily changes only the display. The stored value remains intact unless you use a separate operation or formula to round it. Click the cell and inspect the formula bar to see the underlying value.
  • Some cells ignore the custom format: They may contain text, have a different format applied, or be affected by conditional formatting. Check the cell’s number format and review Home > Conditional Formatting > Manage Rules if the appearance changes unexpectedly.
  • The dash looks different in another workbook or region: A hyphen inside quotes is a literal character, but separators, currency symbols, and other parts of a format can vary with regional conventions. Confirm the code and display in the workbook’s target environment.

Also note that a custom number format can be hidden by other display choices. For example, a narrow column may show number signs instead of the formatted value. Widen the column before diagnosing the format. If the cell is part of a table or a copied range, inspect the actual cell rather than assuming adjacent cells share the same number format.

Alternatives to a custom number format

If you only need a quick visual convention, Excel’s Accounting format may already provide the dash behavior you want. It is convenient for aligned financial columns, but it can impose spacing and symbol choices that are not appropriate for every table. A custom format is preferable when you need to specify the negative-number style, decimal places, or whether the zero dash carries a currency symbol.

Conditional formatting is another option when the appearance should depend on a rule beyond the number itself. However, it is usually unnecessary for the simple requirement of showing numeric zero as a dash: the zero section in a custom number format is simpler and keeps the cell’s numeric value intact. Avoid typing a dash over zeros if the cells need to remain numeric, because that replaces the value with text and can affect calculations.

Frequently Asked Questions

Does showing zero as a dash change formulas or totals?
No. A custom number format changes the displayed appearance only. The cell still contains numeric zero, so calculations continue to use zero.

What is the simplest custom format for a dash at zero?
Use 0;-0;"-" for positive integers, negative integers with a minus sign, and a dash for zero. Add grouping, decimals, parentheses, or a text section if your worksheet needs them.

Can I show zero as a dash but leave negative numbers unchanged?
Yes. Use a two-section format such as 0;"-". With two sections, the first applies to positive numbers and zero, while the second applies to negative numbers, so this pattern would not work as intended. Instead use three sections: 0;-0;"-". The second section explicitly controls negatives, so choose its appearance to match your data.

Why does a cell that looks like zero not show a dash?
It may contain text rather than numeric zero, or its actual value may be a small nonzero amount. It may also have a different number format or a conditional formatting rule affecting its appearance. Check the formula bar, the cell format, and any conditional formatting rules.

Can I display a dash for blank cells too?
A number format’s zero section applies to numeric zero, not to genuinely empty cells. To display a mark for blanks, use a separate formula or a suitable conditional-formatting rule, depending on whether you want to change the cell contents or only its appearance.

Will the dash appear when I print or export the sheet?
It normally appears in printouts and in formats that preserve Excel’s displayed number formatting. Some data exports preserve raw values instead, so a zero may be exported as 0. Check the destination’s behavior when the dash is important to the recipient.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.