Format Zero as a Dash in Excel with Custom Formatting

To show numeric zeros as dashes in Excel without changing the underlying values, select the cells, press Ctrl+1, choose Number > Custom, and enter #,##0;(#,##0);"-";@ in the Type box. Select OK to apply it. This format displays positive numbers with commas, negative numbers in parentheses, and zero as a dash; calculations still use the original numbers.

What You’ll Need

A workbook in Microsoft Excel for Microsoft 365 on Windows, and a range of cells containing numeric values or formulas that return numbers.
Optional: a preferred display style for positive and negative numbers, such as decimals, currency symbols, or minus signs. The examples below show how to adapt the zero section to your style.

You do not need a formula, add-in, or macro. A custom number format changes how a value looks in a cell, not the value Excel stores. If you use Excel for Mac, the format code is the same; the shortcut to open Format Cells is usually Command+1 instead of Ctrl+1.

Step-by-Step

Select the cells to format. Click and drag across the cells, or select a whole column if you want the format to apply to future entries in that column. To select multiple separate ranges, hold Ctrl while selecting each range on Windows. Check the selection carefully: applying a format to an entire worksheet can make unrelated values appear differently.
Open the Format Cells dialog. On Windows, press Ctrl+1. You can also right-click the selection and choose Format Cells. On Mac, press Command+1 or use Format > Cells. In Excel for the web, the dialog and available options can differ; look under the Home tab’s Number Format menu for More Number Formats or Custom. For the clearest access to custom codes, use the desktop app if you cannot find the Custom option online.
Choose the Custom category. In the Format Cells dialog, open the Number tab and select Custom in the Category list. The Type box shows the selected cells’ current number format. You can edit that code or replace it. Replacing a code changes the display style for the selected cells, so note the existing style if you may want to restore it.
Enter the format code. To show whole-number zeros as dashes, enter #,##0;(#,##0);"-";@. The sections are separated by semicolons: the first controls positive numbers, the second negative numbers, the third zeros, and the fourth text. The quoted hyphen is a literal dash. The @ keeps text entries visible as text rather than hiding them.
Review the preview and apply it. Select OK. A value of 1,250 will display as 1,250; -1,250 will display as (1,250); and 0 will display as -. A formula that evaluates to zero will also appear as a dash. Click one of the formatted cells and check the formula bar: it should still show the stored value or formula, even when the cell itself displays a dash.
Keep or adjust the number of decimal places. If the cells should show two decimal places, use #,##0.00;(#,##0.00);"-";@. For example, 12.5 displays as 12.50, -12.5 displays as (12.50), and zero remains a dash. Change the number of zeroes after the decimal point to set the precision you want: 0.0 displays one decimal place, while 0.000 displays three.
Adapt the format to your existing style, if needed. Keep the third section as "-" and adjust the positive and negative sections around it. For currency with two decimal places and a dollar symbol, for example, enter $#,##0.00;($#,##0.00);"-";@. For percentages with one decimal place, enter 0.0%;-0.0%;"-";@. In the percentage example, Excel displays 0.125 as 12.5%, while a true zero displays as a dash. Use the currency symbol and decimal style appropriate for your workbook and regional settings.

To apply the same display to another range, select it and repeat the steps. You can also copy a correctly formatted cell and use Paste Special > Formats on the destination range. That copies the formatting without replacing the destination’s values. If the cells already have a required style, inspect the result after applying the format; a custom code can replace their previous display choices.

Common Mistakes

Expecting the dash to replace the stored zero. It does not. The cell contains numeric zero, and formulas such as SUM continue to include it. This is usually useful because it preserves calculations, but it will not help if another system specifically requires a dash as the underlying data.
Formatting text zero instead of numeric zero. A cell containing the text character 0 is not a numeric zero, so the zero section of a number format does not apply. The code’s fourth section displays text as text. Convert text-formatted numbers to numbers if they need to behave numerically.
Confusing zero with blank. A genuinely empty cell stays empty, and a formula returning an empty string usually displays as blank. A formula that returns the number 0 displays as a dash. The format does not make empty cells into dashes.
Using only two sections in a format code. In a two-section code, the sections mean positive numbers and negative numbers; zero uses the positive section. To specify a special display for zero, include a third section, as in the examples.
Removing useful number details. A code with no decimal places rounds the displayed value to a whole number. The underlying value is not rounded, but the cell may give a misleading impression of precision. Choose decimal places that suit the data.

Tips

Keep a consistent pattern across related columns. For instance, use the same decimal precision and negative-number style throughout a report, changing only the zero section to "-".
Use the format bar’s Type box to learn from existing codes. If the current code includes a currency symbol, color, or decimal places, preserve those parts and replace or add the third section for zero. Custom formats can include more than a dash; avoid deleting options you still need.
Use a dash only when readers can tell it means zero. If a dash might mean not applicable or missing data, add a note to the worksheet or use a separate status field so the meaning is clear.
For a one-off visual effect, formatting is generally better than replacing zeros with typed dashes. Typed dashes turn those cells into text and can interfere with totals, sorting, and other calculations.
To remove the custom format later, select the cells and choose Home > Number Format > General. This restores the General display, though it may not restore a previous specialized format such as currency or dates.

Troubleshooting

The cell still shows 0. Confirm that the intended cells are selected and that the format code has three sections before the text section. The third section must be the zero display, so check for the pattern positive;negative;zero;text. Reopen Format Cells with Ctrl+1 and inspect the Type box, then apply the code again.

The code is rejected or looks different. Check that the sections are separated by semicolons and that the dash is enclosed in straight quotation marks. Some regional settings use different symbols for decimal and thousands separators, and Excel may display or interpret format codes according to your locale. If commas cause trouble, try editing an existing format in the Type box and preserve the separators Excel presents in your installation.

Some zeros show as dashes and others do not. The cells may not share the same format, or some values may be text. Select a cell that behaves differently and open Format Cells to compare its code with a working cell. If the value is stored as text, convert it to a number only if appropriate; changing the number format alone will not convert it.

A small number displays as a dash or appears rounded. The format may show too few decimal places. A value such as 0.004 can display as 0 when formatted with no decimals, even though its stored value is not zero. Add decimal places to the positive and negative sections. If the value is a percentage, format it as a percentage with suitable precision rather than treating it as a whole number.

The dash appears, but totals or formulas seem unchanged. That is expected: the value remains zero. Check the formula bar or use the cell in a calculation to confirm its underlying value. If a downstream export or system needs a literal dash rather than a displayed zero, create a separate text output for that purpose and keep the numeric source data intact.

Frequently Asked Questions

Does formatting zero as a dash change the value?
No. It changes only the cell’s display. Excel continues to store zero, so arithmetic and numeric tests still treat the cell as zero.

Will formulas that return zero display as a dash?
Yes, if the formula result is the numeric value 0 and the cell has the custom format. A formula that returns an empty string, such as one designed to display blank, is not a numeric zero and generally stays blank.

Can I show a dash for zero but keep a minus sign for negative numbers?
Yes. Use a negative section with a minus sign, such as #,##0;-#,##0;"-";@. It displays -1,250 as -1,250 and zero as a dash.

Can I use the format for currency or percentages?
Yes. Preserve the desired currency or percentage pattern in the positive and negative sections, then use "-" as the third section. Confirm that the selected cells contain values in the expected units, especially for percentages.

Can I make every zero in the workbook display as a dash at once?
You can select multiple ranges or entire relevant columns and apply the format, but Excel’s custom number format is applied to cells, not universally to all future workbook content. Applying it selectively is safer than changing every cell, because dates, identifiers, and other values may need different formats.

How do I get the actual zero back on screen?
Select the cells and apply a format whose zero section displays a zero, or choose General from Home > Number Format. The stored value was not removed, so no data restoration is needed.

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.