Yes. In Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, choose Custom, and enter #,##0.00;(#,##0.00);"-";@ in the Type box. This displays numeric zeros as a dash while leaving the underlying values unchanged.
What the custom format does
An Excel custom number format changes how a value appears in a cell; it does not change the value stored there. With the example above, a positive number appears with two decimal places and a thousands separator, a negative number appears in parentheses, zero appears as a dash, and text is displayed as entered.
The format uses semicolons to separate sections. In a four-section format, the sections are read in this order: positive numbers; negative numbers; zeros; text. Excel applies the relevant section according to the cell content. The third section is the part that tells Excel what to show for a numeric zero.
For instance, the value 0 remains available to formulas and calculations even though the cell displays a dash. A sum that includes the cell still includes zero, and a test such as whether the cell equals zero still evaluates the stored value. The dash is a visual convention, not a replacement value.
How to apply the format in Excel for Windows
- Select the cells or range you want to format. To format an entire column, select its column heading.
- Press Ctrl+1 to open the Format Cells dialog box.
- On the Number tab, select Custom from the Category list.
- In the Type box, enter
#,##0.00;(#,##0.00);"-";@. - Select OK and check a cell containing zero, a positive value, and a negative value.
You can also reach Format Cells from the Home tab: in the Number group, select the small dialog box launcher. The custom format may appear in the Type list after you apply it, so you can reuse it in the current workbook. If the selected cells already have a number format you want to preserve, edit that format’s zero section rather than replacing the whole code.
In Excel for Mac, Command+1 opens Format Cells. The dialog and available custom formats are similar, though the interface can differ slightly by release. Excel for the web can display many custom formats already saved in a workbook, but creating or editing a custom format may require opening the workbook in the desktop app; use the desktop steps when the Custom category or Type box is unavailable.
Understanding the format code
Each semicolon marks the beginning of another section. In #,##0.00;(#,##0.00);"-";@, the first section formats positive numbers, the second formats negative numbers, the third formats zero, and the fourth displays text. The at sign, @, stands for the text entered in the cell. Including it is useful when you want text in a mixed range to remain visible.
The number symbols control how digits appear. The comma in #,##0 requests thousands grouping, while the decimal point followed by two zeros requests two decimal places. A zero placeholder displays a digit even when the value has no digit in that position; a number sign placeholder can suppress insignificant leading or trailing zeros. The parentheses in the negative section are literal display characters around the number.
The quoted hyphen in the zero section is a literal character. Quotation marks tell Excel to display the dash rather than treat it as part of the format syntax. You can substitute another marker, such as "—" for an em dash or "n/a" for a short label. Keep the semicolon separators: removing one can shift which values a section controls.
Choose a format that fits the data
The best code depends on what the rest of the column should look like. A dash-only change is usually easiest if you preserve the existing positive and negative sections and replace only the zero section. These examples illustrate common choices:
0;-0;"-";@displays whole numbers, prefixes negatives with a minus sign, and shows zero as a dash.0.00;-0.00;"-";@displays two decimal places and uses a minus sign for negative values.#,##0.00;(#,##0.00);"-";@uses thousands separators and parentheses for negatives.$#,##0.00;($#,##0.00);"-";@displays a dollar sign, two decimal places, and parenthesized negatives.
For currency, accounting reports, or values in another locale, do not assume the sample is a perfect match. You may want to use the currency symbol and decimal conventions already used by the workbook. A number format can also carry color instructions, such as a color for negative values, but adding color is optional and does not affect the zero-as-dash behavior.
If a cell has a carefully designed format, copy its code into a note or another safe place before editing. You can inspect the format by opening Format Cells and looking at the Type box. Modify just the relevant section, then verify how positive, negative, zero, and text entries appear in representative cells.
What counts as zero, and what does not
The zero section applies to a numeric value that is actually zero. A cell that looks empty because it contains an empty string, a formula returning an empty string, or no entry at all is not the same as a numeric zero. The zero section will not turn every blank-looking cell into a dash. Likewise, a text character such as the number zero typed with a leading apostrophe is text, so it follows the text section rather than the numeric-zero section.
There is another important distinction: a small nonzero number can look like zero because of rounding. Suppose a cell contains 0.004 and the format displays two decimal places. Excel can display 0.00, but the stored value is still positive, so the positive-number section applies. It will not show the dash from the zero section. If you need values within a defined tolerance to count as zero, that is a calculation or data-cleaning decision, not merely a display-format decision; consider the consequences before changing the underlying values.
Similarly, a formula that returns the numeric value 0 is a true zero for this purpose and will display as a dash. If it returns an empty string instead, the result is text and generally displays as blank. This difference can matter in later calculations, charts, and tests, so do not change a formula’s result just to achieve a visual dash unless you also want to change how the workbook treats that result.
Common problems and how to fix them
- The zero still appears as 0: Check that the format has the expected number of semicolons and that the dash is in the third section. Make sure you applied it to the cell showing the zero, not a neighboring range.
- Some values show dashes unexpectedly: Check whether those cells contain actual numeric zeros or formulas returning zero. The custom format does not distinguish a manually entered zero from a formula result of zero.
- A near-zero value shows 0.00 instead of a dash: It may be a small nonzero number rounded for display. Increase the displayed decimal places temporarily or inspect the formula bar to understand the stored value.
- Text or blanks do not show a dash: The zero section is for numeric zero, not empty cells or text. To show a marker for blanks, use a separate approach such as a formula, conditional formatting, or a deliberate data-entry convention.
- The format behaves differently after copying: Pasting values, formats, or entire cells can affect number formats differently. Reopen Format Cells on the destination cells to confirm the applied code.
Also check for conditional formatting. A conditional format can change a cell’s appearance and may make it seem as though the custom number format has not taken effect. Review the rules applying to the range if the visible result differs from the format you expect.
Custom format versus other ways to show a dash
A custom number format is often the simplest option when the goal is purely presentation: keep a numeric zero in the workbook, but display a dash in a report. It preserves the value for calculations, does not require an extra helper column, and can be applied to a whole range. It is especially useful when a worksheet is intended to be read by people who interpret a dash as no amount or no activity.
There are alternatives, but they make different trade-offs. Typing a dash directly into a cell stores text, not numeric zero, so formulas and sorting may treat it differently. A formula can return a dash for zero, but that output is text and may require adjustments in later calculations. Conditional formatting can change font or fill appearance, but it does not normally replace the displayed number with a dash; custom number formatting is designed for that job.
Hiding zeros through Excel’s worksheet display options is another possibility, but it hides them rather than showing a dash. A custom format gives more control over the marker and can be limited to selected cells. Choose based on whether readers should see a dash, see nothing, or work with text instead of a numeric zero.
Frequently Asked Questions
What custom format shows zero as a dash in Excel?
For two decimal places, use #,##0.00;(#,##0.00);"-";@. The third section, between the second and third semicolons, specifies what appears for numeric zero.
Does showing zero as a dash change the cell value?
No. A custom number format changes only the display. The cell still contains numeric zero, so calculations and formulas continue to use zero.
Can I show zero as a dash but keep negative numbers with a minus sign?
Yes. Use a format such as 0;-0;"-";@. Its second section displays negative values with a minus sign, and its third section displays zero as a dash.
Why does a value rounded to 0.00 not show as a dash?
It may be a small positive or negative number rather than an exact zero. Excel selects the format section using the underlying value, not just the rounded digits visible on screen.
Can I use an em dash instead of a hyphen?
Yes. Put the em dash in quotation marks in the zero section, for example 0;-0;"—";@. The choice changes only the character shown, not the stored value.
Related
- Copy Paste in Excel: Keep Column Widths When Pasting
- Copy an Entire Excel Spreadsheet to a New File
- Copy and Paste X Times in Excel: Repeat Cells or Rows
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Data Cleaning in Excel: A Practical ExampleA practical data-cleaning example in Excel is to standardize names and email addresses, check dates and missing values,…
- Excel Invoice Template: Find Free Microsoft OptionsYes. Microsoft offers free invoice templates you can use in Excel, including templates you can find by searching…
- How to Archive All Emails in Your Outlook Inbox at OnceTo archive everything in your Outlook Inbox, open the Inbox, select all its messages, and choose Archive. Outlook…
- How to Edit Mail Merge Fields in Word Documents Step by StepTo edit a mail merge field in Word, select it in your document and use the Mailings tab…
- Excel: How to Center a Print Area on a Printed PageTo center an Excel print area, open Page Layout > Margins > Custom Margins, then, on the Margins…
- Create a Quotation Template in Word: Step by StepCreate a reusable quotation template in Microsoft Word by setting up your business details, customer fields, itemized pricing,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.