How to make zero become dash in excel

Answer

To make zeros display as a dash while keeping nonzero numbers unchanged, use a custom number format or a formula depending on whether you need the cell to remain numeric for calculations.

What is the easiest way to show zeros as a dash without breaking calculations?

The easiest way is to apply a Custom Number Format that displays a dash for zero while leaving positive and negative numbers numeric.

1. Select the cells or range.

2. On Windows: Home → Number group drop-down → More Number Formats → Number tab → Custom.

3. On Mac: Format → Cells → Number tab → Format dropdown → Custom.

4. In the Type box enter a format that sets the zero section to a dash. Examples:

   1. For integers: 0;-0;"-"

   2. For two decimal places: 0.00;-0.00;"-"

   3. For currency with two decimals: $#,#00.00;-$#,#00.00;"-"

5. Click OK.

Explanation of the format sections: the pattern has up to four parts separated by semicolons — positive;negative;zero;text. The third part controls how zero displays. Use a quoted dash "-" so Excel shows a dash for zeros but leaves the cell value numeric.

How do I convert zero to a dash using a formula, and what is the tradeoff?

To convert zero to a dash with a formula, use a conditional formula that returns text when the value is zero. This makes the displayed dash but converts the result to text, which prevents numeric calculations on that cell.

1. In a helper column, use:

   1. =IF(A1=0,"-",A1)

2. Drag or copy down.

Tradeoff: The formula result is text when a dash appears, so further math on that cell will require converting back to numbers.

Can I have Excel show a dash for zero and keep cells usable in charts and calculations?

Yes. Use the custom number format method described above. Cells remain numeric, charts will treat zeros as numbers, and formulas keep working.

Are there built-in formats that show dashes for zero?

Yes. The Accounting number format often shows zeros as a dash for monetary values. Use Number Format → Accounting to test whether the default Accounting style in your Excel version displays a dash. Behavior can vary by Excel theme and regional settings; confirm visually.

What about blank-looking zeros when printing or exporting?

Use the custom format 0;-0;"-" or 0.00;-0.00;"-" before printing or exporting. The display change is applied when printing and to most exports that respect cell formatting.

How to remove zeros entirely instead of showing a dash

To hide zeros entirely with formatting, use a custom format where the zero section is empty:

1. Example: 0;-0;""

2. This shows nothing for zeros while keeping values numeric.

How to apply the custom format quickly to an entire column or table

1. Click the column header to select the entire column.

2. Apply the custom format as described earlier.

3. For Excel tables, select a column within the table and apply the format; new rows inherit the format.

What happens to sorting and filtering when zeros display as dashes?

Sorting and filtering operate on underlying values, not the display. A cell that contains a numeric zero but displays as a dash will be treated as zero in sorts and filters.

How to reverse the dash display and restore normal zero display

1. Select the range.

2. Home → Number group drop-down → General or Number.

3. Or open Format Cells → Number and choose General or an appropriate number format.

Common custom format examples

1. Integers with dash for zero: 0;-0;"-"

2. Two decimals with dash: 0.00;-0.00;"-"

3. Percent with dash: 0.00%;-0.00%;"-"

4. Currency with thousands separator and dash: $#,##0.00;$-#,##0.00;"-"

What should I watch out for when using formulas to show dashes?

Yes. Use caution when you convert numbers to text with formulas because text results break downstream arithmetic. Preserve original numeric columns when you need to calculate.

How to keep both visual dash and numeric values available for calculations

1. Keep the original numeric column.

2. Create a separate display column with the formula =IF(A1=0,"-",A1) for reporting or printing.

3. Use the original column for calculations.

Summary of best practice

1. Use Custom Number Format to display dash for zeros while preserving numeric values.

2. Use formulas only when you need the dash as part of exported text or when formatting cannot be applied.

3. Keep original numeric data if calculations or data exports require numbers.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report