How to Show Dash Instead of Zero in Excel (4 Easy Methods)

Often, we want to show Dash (-) instead of 0 in Excel due to the absence of any data. This article shows all the possible methods to do so. We will show all possible ways to answer the question of how to show dash instead of zero in Excel.

Show Dash Instead of Zero in Excel: 4 Easy Methods

There are 4 ways to show dash instead of zero in Excel. To understand better, we will consider this real-life example below. Here the dataset contains the number of different products sold over 3 days and their Product IDs.

All the methods are described step by step below.

1. Using Format Cells Dialogue Box

This method is the most common way to show a dash instead of a zero in Excel. The steps for this method are below.

Steps:

• First, we need to select the range of cells where we want to show a dash instead of zero. In our case, the cell range is (C6:E10).

• Next, we need to press Ctrl+1. A dialog box named Format Cells will appear.

• Then, we need to go to Accounting in the Format cells panel, select None as Symbol, and press OK.

• In the end, we will see there is Dash (-) instead of zero in the cell range.

2. Defining Custom Number Format

This method is applicable when we are working with a large amount of data. Defining a custom number format will also work on newly inserted data as well. The steps to complete this method are below.

Steps:

• At first, we need to select the cell range like the previous method and then press Ctrl+1 to open the Format Cells panel.

• Next, we will select Custom, and in the Type section, copy and paste the following number format and then press OK.

• As a result, we will see all the zeros have been replaced by a dash.

3. Utilizing IF Formula

We can make a separate column and show dash instead of zero in Excel separately using the IF Formula. For this method, we will follow these steps. In our case, we will show it in the Day 1 column under the New field.

Steps:

• First, we need to select the cell where we will show the updated values. In our case for C6, we will show it in F6.

• Next, select cell F6. Type the following formula in the formula bar.

• Then, we will press Enter and see if there is no change, as we did not have a 0 value in C6. It will show 10 as in C6.

• Furthermore, we will drag the Fill Handle to cell F10 to automatically update the rest of the cells.

• Finally, we will see a dash inserted instead of zero in the F7 cell.

Read More: How to Find and Replace Using Formula in Excel

4. Using Replace Feature

When we are working on a large datasheet, we cannot select cells according to our needs. Find and Replace is a useful feature to find specific values across the worksheet. We will use this feature to show dash instead of zero in Excel. Here are the steps to do so.

Steps:

• In the beginning, we need to press Ctrl+H to trigger the Find and Replace dialog box.

• Then we will give 0 in the Find what box and give Dash (–) in the Replace with box.

• Next, we need to tick the Match entire cell contents box and click on Find All option.

• As a result, it will show all the cells containing 0 value in them. We can check here if all the cells or values contain 0 or not.

• Again, we will click on Replace All option if all the cells found are correct.

• Finally, we will see all the Zeros have been replaced by Dash in the datasheet.

Read More: How to Find and Replace Values in Multiple Excel Files

Things to Remember

• In the first method, the alignment will be changed to Right and show numbers up to 2 decimal places.
• For the other three methods, the alignment will remain the same.
• We can repeat the If formula method for Day 2 and Day 3 fields as well, but we will need to do it in new columns.
• The entire demonstration was done in Microsoft Office 365. So, the user interface may vary for different versions.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

The purpose of the article is to show different methods through which we can show dash instead of zero in Excel. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all your questions.

Related Articles

• How to Find and Replace Text Color in Excel
• How to Find and Replace within Selection in Excel
• How to Find and Replace Multiple Words from a List in Excel
  
• How to Replace Text in Excel Formula
• Replace Text of a Cell Based on Condition in Excel

<< Go Back to Find and Replace | Learn Excel