Excel Custom Number Format: Show Zero as a Dash

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, choose Number > Custom, and enter 0;-0;"-" in the Type box. The third section makes an exact numeric zero display as a dash; it does not change the cell’s value.

What the custom format does

An Excel number format controls how a value looks in a cell, not what the cell contains. With 0;-0;"-", a positive whole number displays without a leading plus sign, a negative whole number keeps its minus sign, and zero displays as a hyphen. For example, the underlying values 8, -8, and 0 appear as 8, -8, and -.

The quotation marks tell Excel to display the hyphen as literal text within the format. Using quotes is a clear, reliable way to mark it as a character rather than a number-format instruction. The hyphen is only a visual substitute: the zero remains available to formulas, charts, sorting, and calculations as a number.

This is useful in reports and tables where a row of zeroes creates visual clutter, but where the zero values still matter. It is different from deleting zeroes, entering a text dash in place of a number, or hiding the cells’ contents with a formula.

How Excel’s number-format sections work

Semicolons divide a custom number format into sections. In a format with three sections, Excel uses them for positive numbers, negative numbers, and zero, in that order. That is why 0;-0;"-" assigns the dash to zero without changing the appearance of positive and negative whole numbers.

A format can have up to four sections: positive numbers; negative numbers; zero; and text. If a format has only one section, it applies to numbers generally. With two sections, the first applies to positive numbers and zero, and the second applies to negative numbers. With three sections, zero gets its own third section. A fourth section can specify how text appears.

For instance, #,##0;[Red](#,##0);"-";@ shows positive whole numbers with grouping separators, negative whole numbers in red parentheses, zero as a dash, and text as entered. The @ placeholder represents text in the fourth section. This more complete pattern can be useful in a mixed report, though the simpler three-section version is easier to maintain when you do not need special text handling.

Apply the format in Excel for Windows

  1. Select the cells, column, or range whose zeroes should appear as dashes. You can select nonadjacent ranges by holding Ctrl while selecting them.
  2. Press Ctrl+1 to open Format Cells. Alternatively, on the Home tab, open the Number group’s dialog launcher.
  3. Choose Number > Custom. Click in the Type box, enter 0;-0;"-", and select OK.

For values that need thousands separators, use #,##0;-#,##0;"-". For two decimal places, use 0.00;-0.00;"-". These patterns retain the same positive, negative, and zero section order; they simply change how nonzero values are displayed. If the selected range already has a custom format you want to preserve, note or copy that format before replacing it.

On Excel for Mac, select the cells and use Command+1 to open Format Cells, then choose the custom number-format controls and enter the code. Ribbon labels and dialog layouts can vary somewhat by Mac release. Excel for the web may display formats created in desktop Excel, but the desktop app is the safer choice when you need to create or edit a custom format and do not see a Custom option in the web interface.

Choose a pattern for your data

The right pattern depends on what the other values in the range should look like. The dash is just one section of the format, so decide explicitly how positive and negative values should display as well. Here are common starting points:

  • 0;-0;"-" — whole numbers without grouping separators.
  • #,##0;-#,##0;"-" — whole numbers with grouping separators.
  • 0.00;-0.00;"-" — two decimal places for nonzero values.
  • 0%;-0%;"-" — percentages with no decimal places.
  • $#,##0.00;($#,##0.00);"-" — a dollar sign, two decimal places, and parentheses for negatives.

These are examples, not universal templates. Currency symbols, decimal separators, and grouping conventions depend on the workbook’s needs and regional settings. If you work with a currency other than dollars, or need a particular locale’s conventions, use the appropriate symbol or select a built-in number format first, then adapt its sections. A custom format is often best when you have a specific reporting requirement that the built-in choices do not meet.

What counts as zero, and what does not

The zero section is used for a numeric value equal to zero. A blank cell is not the same thing as a zero, so this format does not turn empty cells into dashes. Text containing the character 0 is also not a numeric zero; it is text and is handled by the text section, if one is specified.

Be careful with values that are small but not zero. A value such as 0.004 is not equal to zero. If a format rounds it to two decimal places, it may look like 0.00, but it can still use the positive-number section and display as 0.00 rather than a dash. Number formatting rounds the display; it does not round or replace the stored value. If your business rule says small values should count as zero, decide that rule explicitly rather than assuming a display format will apply it.

Likewise, a formula that returns the number 0 is treated as zero and displays as a dash. A formula that returns an empty text string is not the same as a numeric zero. These distinctions matter when reviewing formulas or using functions that count numeric values.

Number format versus other ways to show a dash

A custom number format is usually the simplest choice when you want zeroes to remain numeric. It leaves the underlying data intact, so a total can still include zeroes normally and a later calculation can use the cells as numbers. It also avoids changing the source formulas simply to alter the appearance of a report.

Excel’s built-in Accounting format is another option. It commonly displays zero values as dashes and aligns currency symbols and decimal points in a column. Choose it when that accounting layout suits the report; use a custom format when you need more control over decimals, symbols, negative numbers, or alignment. Built-in format details can vary with the selected currency and regional settings.

You could instead use a formula that returns a dash for a zero, but that result is text, not a numeric zero. That can complicate sums and other calculations if you use the displayed-result column as data. Conditional formatting can change a cell’s appearance based on a rule, but it is generally more involved for this simple display task and is not needed just to replace zero with a dash.

Common problems and how to fix them

  • Zero still appears as 0: Check that the format has three sections and that the third is the quoted dash, as in 0;-0;"-". A two-section format has no dedicated zero section.
  • Negative numbers look wrong: The second section controls negative values. Include the minus sign, parentheses, or other negative style you want; for example, 0;(0);"-" uses parentheses instead of a minus sign.
  • Decimals disappear or round: The zero placeholders determine the displayed decimal places. Use 0.00;-0.00;"-" to show two places for nonzero values. This affects display only.
  • A small amount looks like zero but not a dash: It may be a nonzero value rounded on screen. Check the formula bar or increase the displayed decimal places before deciding that the stored value is exactly zero.
  • The format seems to vanish elsewhere: Custom number formats are Excel formatting, not part of the underlying value. Another application or a plain-text export may not retain the format definition, so verify the output if a report will be shared outside Excel.

When entering a format, use the semicolons to separate sections and straight quotation marks around the literal dash. If Excel rejects the code or displays it differently, check the workbook’s locale and the exact characters entered. Curly quotation marks copied from a word processor are not the same as straight quotation marks.

When a dash can be misleading

A dash can mean “zero” in one report and “not applicable,” “missing,” or “not reported” in another. Since this format makes a numeric zero look like a dash, readers may not know which meaning applies unless the table explains it. Add a note such as “Dash indicates zero” when the distinction affects interpretation.

Also consider the audience and the document’s later uses. Someone reading a printed report sees a dash, while a person inspecting the cell or using it in a calculation sees the underlying number. A chart, another spreadsheet program, or a data export may handle formatting differently. Keep numeric source data intact when possible, and label the presentation clearly rather than asking readers to infer what a dash means.

Frequently Asked Questions

Does the dash replace zero in the cell?
No. The cell still contains numeric zero. The custom format changes only its appearance, so calculations continue to treat it as zero.

What is the shortest custom format for zero as a dash?
Use 0;-0;"-" for positive whole numbers, negative whole numbers, and a dash for zero. Add grouping separators or decimal places if your data needs them.

Will blank cells also show a dash?
No. A blank cell is not a numeric zero, so the zero section does not apply. Text and blank cells are distinct from numeric values.

Can I show zero as a dash but keep decimals on other values?
Yes. For two decimal places, use 0.00;-0.00;"-". A nonzero value that rounds to 0.00 may still display as 0.00 because its actual value is not zero.

Can I use this format in Excel for Mac?
Yes. Open Format Cells with Command+1, choose the custom number-format controls, and enter the same format code. The appearance of the dialog can differ by version.

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.