Excel Number Format: Display Zero Values as Dashes

How-to basics · Updated November 19, 2026

To display numeric zeros as dashes in Excel for Microsoft 365 on Windows, apply a custom number format such as #,##0;-#,##0;"-";@. Select the cells, press Ctrl+1, choose Custom, enter the format in the Type box, and select OK; the cells still contain numeric zero, even though they show a dash.

What a zero-as-dash format does

An Excel number format controls how a value appears in a cell, not the value stored there. A custom format can display positive numbers, negative numbers, zero, and text differently. In #,##0;-#,##0;"-";@, semicolons separate four sections: positive numbers, negative numbers, zero, and text. The first section adds a thousands separator and displays whole numbers; the second keeps a minus sign on negative values; the third displays a literal hyphen; and the fourth uses @ to show text as entered.

The quotation marks around the hyphen tell Excel to treat it as a literal character. You can use a different dash character, such as an en dash, inside the quotation marks if your reporting style calls for it. A simple format with only three sections, #,##0;-#,##0;"-", also works for numbers; the fourth section is only needed if you want to specify how text is displayed.

This is a visual convention often used in reports and financial tables to make zero activity easy to distinguish from a positive amount. It does not mean the cell is empty, and it does not change how Excel calculates with the cell.

Apply the format in Excel for Windows

  1. Select the cells, rows, columns, or range you want to format. To set the format for a whole column, click its column letter.
  2. Press Ctrl+1 to open Format Cells. Alternatively, go to Home and select the Number group dialog box launcher.
  3. On the Number tab, select Custom in the Category list.
  4. Enter #,##0;-#,##0;"-";@ in the Type box.
  5. Select OK and check a positive number, a negative number, zero, and any text in the selected range.

On Excel for Mac, Command+1 opens Format Cells; the dialog also has a Custom category and a Type field. In Excel for the web, the available custom-format controls can differ from the desktop version. If you cannot find a way to enter a custom format there, open the workbook in the desktop app and apply it there. Once applied, the formatting is part of the workbook and is visible in compatible Excel versions.

Choose a format that matches the data

The example above displays whole numbers. If your values need decimal places, include them in the positive and negative sections. For two fixed decimal places, use #,##0.00;-#,##0.00;"-";@. A value such as 1,234.5 will display as 1,234.50, while an exact zero will display as a dash. For percentages, a suitable pattern is 0%;-0%;"-";@; a stored value of 0.25 then displays as 25%.

For a currency table, include the currency symbol and appropriate negative-number style in the first two sections. For example, $#,##0.00;-$#,##0.00;"-";@ displays dollar amounts with two decimal places and a dash for zero. The dollar sign here is a literal symbol, so it may not be the best choice for workbooks shared across regions that use different currencies. Excel’s built-in Currency and Accounting formats can be preferable when you need a particular currency or aligned symbols.

You can also use parentheses for negative numbers, as in #,##0;(#,##0);"-";@. That produces results such as 1,200, (1,200), and – represented by a hyphen for zero. Choose one pattern that is consistent with the rest of the workbook; formatting should make a table easier to interpret, not introduce an unfamiliar convention.

What happens to formulas and calculations

Because the format changes appearance rather than content, a cell containing 0 remains a number equal to zero. SUM, AVERAGE, comparisons, and other calculations continue to use that numeric value. For example, a total that equals zero will show a dash in a cell with this format, but a formula that refers to the total still receives zero. Sorting and filtering also treat it as a numeric zero, not as text containing a dash.

The same distinction matters when a cell contains a formula. If the formula returns the number 0, the zero section displays a dash. If it returns an empty text string, such as a formula result of "", it is text rather than numeric zero and is not turned into a dash by the zero section. A genuinely blank cell also remains blank. This makes number formatting useful when you want to hide zero values without changing formulas or converting the data to text.

Copying or exporting data can expose the difference. Copying cells within Excel generally carries their formatting, but copying values alone may not. A CSV file stores tabular text and does not preserve Excel’s custom number formats, so a displayed dash may be exported as the underlying numeric zero instead. If a recipient needs the dash in a plain-text export, check the exported file rather than assuming the display format will travel with it.

Important limits and common surprises

  • Only exact zero uses the zero section. A small nonzero value may round to zero visually but still use the positive or negative section. For example, 0.004 with a two-decimal format can display as 0.00 rather than a dash. The section is selected from the stored value, not merely from the rounded appearance.
  • Zeros are still counted. A dash on screen does not make a zero disappear from calculations, data validation, or other operations that use the underlying value.
  • The format affects only the selected cells. Applying it to one table does not automatically set the same display for every sheet or for new workbooks.
  • Text dashes are different. Typing a hyphen into a cell stores text, which may not behave like a numeric zero in totals, sorting, or later analysis.
  • Charts have their own display choices. A cell’s custom format does not change its underlying value. A chart may still plot a zero as zero; check the chart’s axis or data-label settings if the displayed result matters there.

If the dash appears for a value you expected to show as a number, inspect the stored value and the number of decimal places in the format. If a supposed zero does not show a dash, check whether it is actually a small nonzero value, text, or a formula result of an empty string. The formula bar and the Number section of Format Cells can help distinguish these cases.

Change, remove, or reuse the custom format

To change an existing format, select the affected cells, open Format Cells, choose Custom, and edit the Type entry. If the format is listed in the custom-format list, selecting it lets you inspect or adjust it. To remove the custom appearance, choose a built-in number format such as General or apply another suitable format. General may display numeric values without the separators or fixed decimal places you wanted, so check the result before sharing the workbook.

To reuse the appearance in another part of the workbook, use Format Painter on the Home tab or copy the formatted cells and paste their formatting. Be aware that copying a full cell can also copy its value or formula, depending on the paste method; use Paste Special options when you need formatting only. In a shared template, apply the format to the intended input or reporting range and document the convention if other editors may mistake a dash for missing data.

When a custom number format is not the right choice

A custom format is usually the cleanest option when the goal is simply to show numeric zero as a dash while keeping the value available for calculations. It is less appropriate if the dash must be stored as actual text for a specific downstream system. In that situation, a formula or export process may need to create text deliberately, but doing so changes the data type and can complicate calculations.

Conditional formatting is another tool, but it is generally intended to change appearance based on rules such as thresholds, not to replace a cell’s displayed number with a literal dash. If you need to make values close to zero appear as dashes, decide what threshold counts as zero and handle that rule explicitly. Do not silently treat every rounded-to-zero value as an exact zero unless that matches the reporting requirement.

For a presentation where zeros should be hidden rather than replaced, Excel’s worksheet display settings or a format with an empty zero section may be considered, but those approaches have different effects and can make a table harder to read. A dash is often clearer because it signals a value was recorded and is zero, while a blank can mean no entry, not applicable, or missing information.

Frequently Asked Questions

Can I show zero as a dash without changing the value?
Yes. A custom number format changes the cell’s display only. A numeric zero remains zero for formulas, sorting, and other calculations.

How do I show zeros as dashes but keep blank cells empty?
Apply a custom number format with a zero section such as #,##0;-#,##0;"-";@. Blank cells stay blank; numeric zeros show as dashes. Formula results that are empty text also remain visually empty.

Can negative numbers appear in parentheses while zero appears as a dash?
Yes. Use #,##0;(#,##0);"-";@ for whole numbers. The second section controls negative values and the third controls zero.

Why does a small value show 0.00 instead of a dash?
It is likely not exactly zero. The zero section applies to a stored value of zero; a small positive or negative value uses its respective section even if rounding makes it look like zero.

Will the dash appear in a CSV file?
Not necessarily. CSV does not retain Excel’s cell number formats, so the exported content may contain the underlying zero rather than the displayed dash. Open and inspect the exported file when its appearance or contents matter.

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.