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.

Download Practice Workbook

You can download the practice workbook from here.

4 Easy Methods to Show Dash Instead of Zero in Excel

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 dash instead of 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 dash instead of zero. In our case, the cell range is (C6:E10).

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

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

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

Read More: How to Exclude Zero Values with Formula in Excel (3 Easy Ways)

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 go 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.

Read More: How to Use XLOOKUP to Return Blank Instead of 0

Similar Readings

• How to Ignore Blank Cells with Formulas in Excel Chart
• Excel Chart: Ignore Blank Axis Labels (with Easy Steps)
• How to Hide Zero Data Labels in Excel Chart (4 Easy Ways)
• Leave Cell Blank If There Is No Data in Excel (5 Ways)
• How to Hide Zero Values in Excel Pie Chart (3 Simple Methods)

3. Utilizing IF Formula

We can make a separate column and show dash instead of zero in excel there 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:

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

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

• Thirdly, we will press Enter and see 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 till the 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 Use VLOOKUP to Return Blank Instead of 0 (7 Ways)

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 are containing 0 or not.

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

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

Read More: Excel IFERROR Function to Return Blank Instead of 0

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.

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. For any excel related problems, you can visit our website ExcelDemy for all types of excel related problem solutions.

Related Articles

• Ignore Blank Cells in Excel Bar Chart (4 Easy Methods)
• How to Apply VLOOKUP to Return Blank Instead of 0 or NA
• Remove Zeros in Front of a Number in Excel (6 Easy Ways)
• How to Hide Rows with Zero Values in Excel Using Macro (3 Ways)
• Hide Chart Series with No Data in Excel (4 Easy Methods)
• How to Hide Zero Values in Excel Pivot Table (3 Easy Methods)