How to Replace Zero with Dash in Excel?

When making reports in Excel, it’s important to show information in a way that’s easy to read and understand.

Sometimes, having lots of zeros in the report can make it hard for people to see the other important numbers. Zeros can make the report messy and hard to read.

To fix this problem, many Excel users like to show zeros as dashes (-) instead.

Using dashes helps make the report look cleaner and more organized. This way, it’s easier for people to study and understand the information.

In this article, I’ll show you some simple methods for replacing zero with a dash in Excel.

Method 1 – Apply “Comma” Style to Replace Zero with Dash in Excel

When we have figures in number format (without the currency symbol or any other custom number format), we can simply apply the comma style to replace zeros with dashes in Excel.

Below, I have a table that shows the number of units sold by three salespeople during the last four quarters.

Now I want to replace the zeros with a dash so it would improve the readability of this dataset.

I can do it by following the below steps.

1. Select the numbers in the data table.

2. Click the Home tab and then click on the comma-style icon (which is in the Number group).

As soon as I click on the comma icon, Excel will replace all the zeros with dashes (as shown below). 

I can also use the below Excel shortcut to apply the comma style to selected numbers in our Excel sheet.

ALT + H + K

I have to press each key one after the other.

When I select cells with numbers and click on the comma icon, it will replace the zeros with dashes and, at the same time, add two decimal places to all the numbers.

If I don’t want to show the decimal numbers, I can remove them by clicking the “Decrease Decimal” icon twice. I can find the “Decrease Decimal” icon in the Home tab’s Number group.

Also read: How to Add Comma Between Names in Excel

Method 2 – Apply Comma Style using Cell Styles to Replace Zero with Dash in Excel

In Excel, we can also use the comma style to replace zeros with dashes without adding decimal places to numbers that are not zeroes.

Below, I have a table that shows the number of units sold by three salespeople during the last four quarters.

Now I want to replace the zeros with a dash so it would improve the readability of this dataset.

I have to follow the below steps to apply the comma style using cell styles.

1. Select the numbers in the data table.

2. Click on the Home tab and expand the “Cell Styles” list (which is in the “Styles” group).

3. Go to the “Number Format” section of the “Cell Styles” and Select “Comma [0]”.

I have now replaced all zeros with dashes. Now there are no decimal places for non-zero numbers.

Also read: How to Remove Commas in Excel

Method 3 – Apply Accounting Format to Replace Zero with Dash in Excel

When there are currency signs in the numbers, we cannot use the comma style to replace the zero with a dash.

The reason for this is that the comma style removes the currency symbol.

In such a case, we have to use Excel’s “Accounting Number Format” to replace the zero with a dash.

Below, I have a table that shows the sales made by three sales officers in the first four months of the year.

Now I want to replace the zeros with a dash so it would improve the readability of this dataset.

I can do so by following the below steps.

1. Select all the values in the data table.

2. Go to the “Home” tab and click the “Accounting Number Format” icon (which is in the “Number” group).

As soon as I click on the Accounting number Format icon, Excel will replace all the zeros with dashes (as shown below).

I can also use the below Excel shortcut to apply the Accounting Number Format to selected numbers in our Excel sheet.

ALT + H + A + N + Enter

I have to press each key one after the other.

When I select cells with numbers and click on the Accounting Number Format icon, Excel will replace the zeros with dashes, and at the same time, add two decimal places to all the numbers.

If I don’t want to show the decimal numbers, I can remove them by clicking the “Decrease Decimal” icon twice (which is in the Home tab).

Also read: How to Remove Dashes (-) in Excel?

Method 4 – Apply Accounting Format using Cell Styles to Replace Zero with Dash in Excel

We can replace zeros with dashes in Excel with Accounting Number Format without adding decimal places to non-zeros also.

Below, I have a table that shows the sales made by three sales officers in the first four months of the year.

Now I want to replace the zeros with a dash so it would improve the readability of this dataset.

I have to follow the below steps.

1. Select all the values of the data table.

2. Go to the “Home” tab and expand the “Cell Styles” list (which is in the “Styles” group).

3. Go to the “Number Format” section of the “Cell Styles” and Select “Currency [0]”.

I have now replaced all zeros with dashes. Now there are no decimal places for non-zero values.

Also read: Fill Blank Cells with Dash (-) in Excel

Method 5 – Apply Custom Number Format to Replace Zero with Dash in Excel

We sometimes apply custom number formats to numbers in our Excel files. In this case, we cannot use the above methods to replace zero with a dash.

Then we need to know how to replace zero with a dash in a custom number format.

Below, I have a table that shows three players’ total points across four rounds. A custom number format is applied to these numbers.

Now, I want to display zero points in this data table as a dash.

First, we’ll learn some basics of custom number format.

We can divide our code into four areas when creating custom number formats.

• Section 1 – Number format for the positive numbers
• Section 2 – Number format for the negative numbers
• Section 3 – Number format for zeros
• Section 4 – Format for Text

We have to insert a semicolon (;) to separate each section of the number code.  

If we don’t specify a format for sections 2, 3, or 4, Excel will use the number format code from section 1 for the remaining sections.

We have to add a semicolon if we want to skip defining the number format for any section.

Now you have some understanding of custom number format. So, let’s learn how to replace zero with dah in custom number formats.

I can do so by following the below steps.

1. Select the numbers in our data table.

2. Press “Control + 1” to open the “Format Cells” dialog box. Then, select the “Custom” number category.

Then, we can see the existing number format for the selected numbers.

In this example, the applied number code is:

#,##0.000_);[Red](#,##0.000)

In the above format code, number formats for the positive numbers and negative numbers are given.

3. Add a semicolon and a dash (;-) to the end of the number format code to format zeros as dashes.

4. Then, click the “OK” button in the Format Cells dialog box.

I have now replaced all zeros with dashes without changing the number format of the other numbers.

So, we can use this method to convert zeros to dashes in any custom number format.

Also read: Fill Blank Cells with 0 in Excel

Method 6 – Use Excel Find & Replace Feature to Replace Zero with Dash in Excel

Sometimes we have different number formats in the same table.

In such cases, if we want to replace zeros with dashes, we have to carefully identify the number formatting of each cell/column/row and apply the format one by one.

I know that it is not a simple task when we have a large dataset.

In such a case, Excel’s Find and Replace functionality can be useful. Then, we can simply replace zero with a dash without having to worry about number formatting.

Below, I have a table that shows the volume and value of two products for three branches.

Volumes are given as numbers in millions, and values are given as dollar values in millions.

In the above table, I have to replace all zero values with dashes.

I have to follow the below steps for that.

1. Select all the numbers in our data table.

2. Press “Control + H” to open the “Find and Replace” dialog box.
3. Then, enter “0” in the “Find what:” box and enter “-” in the “Replace with;” box.

4. Check the “Match entire cell contents” box.

5. Click the “Replace All” button.

As soon as I click on the “Replace All” button, Excel will replace all the zeros with dashes (as shown below).

Also read: How to Replace Asterisks in Excel

Method 7 – Use a VBA Code to Replace Zero with Dash in Excel

To replace zeros with dashes in Excel, we can even use a VBA code.

Below, I have a table that shows the volume and value of two products for three branches. Volumes are given as numbers in millions, and values are given as dollar values in millions.

In the table above, I have to replace zero values with dashes.

I can use the below VBA code to add a dash in place of any zero values.

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub ReplaceZeroswithDashes()
Dim selectedRange As Range
Dim currentCell As Range
' Assign the selected range of cells to the variable
Set selectedRange = Selection
' Iterate through each cell within the selected range
For Each currentCell In selectedRange
    ' If the value of the current cell is zero
    If currentCell.Value = 0 Then
        ' Change the cell value to a dash
        currentCell.Value = “ - “
        ' Set the horizontal alignment of the cell content to center
        currentCell.HorizontalAlignment = xlCenter
    End If
Next currentCell
End Sub

I can follow the below steps to apply the above VBA code.

1. Press ALT + F11 to open the VBA editor (or click on the Developer tab and then click on the Visual Basic icon).
2. Expand the Insert menu and select “Module”.

3. Copy and paste the above VBA code.

4. Then, I can select cells with the numbers and run the macro to replace zeros with dashes.

As soon as I run the VBA code, Excel will replace all zero values with dashes in the selected cells.

I have to save my Excel workbook as an Excel Macro-Enabled Workbook (*.xlsm)  to save the VBA code.

Also read: How to Add Leading Zeros in Excel?

Method 8 – How to Replace Zero with Dash in Excel Pivot Table

When we are working on Excel, most of the time we work with Excel Pivot Tables. So, it is important to know how to replace zero with dashes in Excel Pivot Tables.

Below, I have a Pivot Table that shows the marks received by three students for language tests.

Now I need to add dashes in place of the zeros.

I can do it by following the below steps.

1. Right Click on any value cell and select Pivot Table Options from the menu.

2. Go to the “Layout and Format” tab of the Pivot Table options.

3. Check the box “For empty cells show” option in the Format area and enter an em dash (—) in the empty box.

To enter an em dash hold down the ALT key and press 0151. This dash is longer than the usual dash.

Entering a dash in the empty box is not working in this scenario.

4. Finally, click the “OK” button.

Now I replaced all blank cells with an em-dash.

So, now we know how to replace zero with a dash in Excel. Based on the number format in our data set, we can choose the most suitable method.

Other Excel articles you may also like:

• COUNTIF Greater Than Zero in Excel
• Fill Blank Cells with Value above in Excel?
• How to Remove Leading Spaces in Excel?
• Remove Parentheses (Brackets) in Excel
• How to Remove Negative/Minus Sign in Excel