Do you have unwanted zero values in your data and instead want to display them as a dash?

Perhaps you see so many zeros that it is distracting you from some important analyses. It may even be that your zero values are not accurately reflecting the state of the data where they appear.

Excel may display zeros in places where you consider the data to be non-applicable.

In such cases, showing zero as a dash (-) can be an effective way to enhance readability and improve the interpretation of your data.

This post presents all the ways to show zero as a dash in Excel. Some of these ways simply change the display format while others replace the zero value itself.

Show Zero as Dash with Accounting Format

Showing zero as a dash has been a standard in the accounting industry for quite some time. Because of this , Excel includes its own built-in accounting format that will display zeros as a dash.

You can implement the accounting format much like other cell formats.

1. First select your numeric data.

2. Go to the Home tab.
3. Select Accounting from the Number Format dropdown.

As quick as that, your numbers show all the features of the format!

Two-decimal places are enforced on each number along with a thousand separator and a left-justified dollar sign ($). Most importantly, your zeros now show as dashes!

Show Zero as Dash with Custom Format

When you use a custom format you can control how positive and negative values are displayed. But you can also control how to display zero values as well.

When using a custom format, it’s sometimes a good idea to first select the format that most closely resembles what you want. You can then modify the format to suit your needs.

Here’s how to apply a complete custom format.

1. Select the range of values to which you want to apply a custom format.

2. Go to the Home tab in the ribbon.
3. Click on the Launch icon in the lower right or the Number section.

This will open the Format Cells dialog box which will allow you to adjust a variety of cell formatting options including any number format.

💡 Tip: You can also open the Format Cells menu with the Ctrl + 1 keyboard shortcut.

4. Go to the Number tab in the Format Cells menu.
5. Select the Custom option in the Category section.
6. Enter the #,##0;-#,##0;- format string into the Type field.
7. Press the OK button.

The format string used in the custom number format consists of three parts separated by a ; character.

• The first part determines the format to use for positive numbers.
• The second part determines the format to use for negative numbers.
• The third part determines the format used for zero values. Because the third part consists of a dash (-), this causes zeros to show as a dash!

The above shows the results.

Of note, if you select any of your cells containing a dash, as above, the formula bar still shows the zero value, as it should. The cells will still contain their zero value, but the formatting causes them to display as a dash.

📝 Note: If you select any of your cells containing a dash, the formula bar still shows the zero value. The cells will still contain their zero value, but the formatting causes them to display as a dash.

Show Zero as Dash with IF Function

The IF function offers a quick way to conditionally show a dash by testing the cell for a zero value.

= IF ( C3=0, "-", C3 )

The above formula will test if the value in cell C3 is zero and return a dash ("-") if it is. Otherwise, it will return the original value.

This will keep the non-zero values as numbers, but the cells with dashes will actually be text values.

Show Zero as Dash with TEXT Function

The TEXT function can show zero as a dash by leveraging the same custom format string used previously in this post.

= TEXT ( C3, "#,##0;-#,##0;-" )

The above formula will format the content of cell C3 based on the supplied format string "#,##0;-#,##0;-".

The results from this formula will be text values, so this may not be the ideal solution depending on what you need to do with the formatted values.

You won’t be able to use the results in any further numerical calculations since they are text.

Show Zero as Dash with Find and Replace

Excel has a Find and Replace feature similar to those you’ve probably seen in other software products outside of Excel.

You can use this feature to replace zero values with dashes entirely. The results will cause the dashed cells to become text rather than numeric.

Follow these steps to replace your zero values from any range.

1. Select the cells that you wish to search.
2. Press Ctrl + H to open the Find and Replace menu.

3. Add 0 to the Find what input.
4. Add - to the Replace with input.

This will replace all instances of zeros (0) with dashes (-) within your selection.

5. Check the option to Match entire cell contents.

This is a key part of the process. Matching the entire cell contents means you won’t inadvertently change a value such as 520 to 52-.

6. Press the Replace All button.

Excel will replace all the zero values in your selection with a dash.

Show Zero as Dash in Pivot Tables

A pivot table might show you an empty row if a column combination contains zero data.

For example, when you use a pivot table to show yearly sales since 2021 by sales person, any new salesperson hired in 2022 won’t have any sales for 2021.

If you’d like to show a dash in 2021 for such a new salesperson, you first need to show empty rows.

1. Begin by selecting anywhere in your pivot table to bring up the PivotTable Fields pane.

2. In the PivotTable Fields pane, select your bottom-most field in the Rows area, then select Field Settings.

3. In the Layout & Print tab, check Show items with no data.
4. Click the OK button.

5. Now show the dash by first going to the PivotTable Analyze ribbon tab.
6. Select the Options.

7. Go to the Layout & Format tab of the PivotTable Options menu.
8. Check the For empty cells show option and paste the following em dash character (–) into the field.

It’s important to stress that the standard keyboard dash character does not work here, so the em dash character is a similar alternative.

9. Click on the OK button.

As you can see, your new salesperson displays a dash in the year where he had zero sales!

Show Zero as Dash with VBA

VBA is a great way to automate most tasks in Excel.

You can use VBA to replace zeros as well in a similar fashion to the Find and Replace feature.

But this way you won’t need to input any values to find, or update settings to get the job done. The code will take care of that!

You can create a macro that will loop through the selected range and identify any cells with a zero and replace them with a dash.

Follow these steps to open the visual basic editor.

1. Go to the Developer tab.
2. Select the Visual Basic command to open the editor.

You might not see the Developer tab, as it’s hidden by default. You can enable the Developer tab in your ribbon if you don’t see it already. You can also use the Alt + F11 keyboard shortcut to directly open the visual basic editor.

3. Go to the Insert menu of the visual basic editor.
4. Select the Module option.

Sub ReplaceZeros()
Dim selectedRange As Range
Set selectedRange = Application.Selection

For Each i In selectedRange.Cells
    If i.Value = 0 And Len(i.Value) > 0 Then
        i.Value = "-"
    End If
Next i
End Sub

This will create a new module where you can copy and paste the above code.

This code will loop through each cell in the selected range and check if its value is zero. If the value is zero then the cell content is updated to a dash.

Note the Len function used to check the length of the cell content. This is necessary because the code treats an empty cell as a zero, so checking the length avoids an empty cell from being updated.

Now you can select any range in the workbook and run the VBA code to replace the zeros!

Show Zero as Dash with Office Scripts

VBA isn’t the only way to automate things in Excel. You can also use Office Scripts if you’re using Excel online with a Microsoft 365 business plan.

Open your Excel workbook in the web browser and go to the Automate tab then select the New Script option.

function main(workbook: ExcelScript.Workbook) {
    let range = workbook.getSelectedRange();
    let rowCount = range.getRowCount();
    let colCount = range.getColumnCount();

    for (let row = 0; row < rowCount; row++) {
        for (let col = 0; col < colCount; col++) {
            let currCell = range.getCell(row, col);
            let currValue = currCell.getValue();
          if (currValue == 0 && currValue.toString().length > 0) {
                currCell.setValue("-");
            };
        };
    };
}

This will open the Code Editor on the right side of the workbook. You can then copy and paste the above code into the Code Editor and press the Save script button.

This code will loop through the selected range, cell by cell. If the cell value is zero, its value is set to a dash.

As in VBA, the length of the cell content is checked to ensure that a blank cell does not get erroneously updated.

To test your code, select your desired range and press the Run button in the Code Editor. When it finishes, your zeros will be replaced with dashes!

Conclusions

There are many reasons to show zeros as dashes in Excel, varying from plain aesthetics to a reinforcement of how your data should be interpreted.

The least invasive option would be to change its display through the use of a custom format or accounting format, with the accounting format particularly suited to currency data.

The other methods actually change your underlying cell values, so it’s good to keep in mind that any numerical calculations you have might be affected by those changes.

An extra advantage of the VBA and Office Scripts methods is that, once verified, they can be re-applied quickly and reliably as needed.

Have you needed to show zero values as dashes? How did you get it done? Let me know in the comments below!