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

Get FREE Advanced Excel Exercises with Solutions!

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.

Easy Methods to Show Dash Instead of Zero in Excel

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.

Easy Methods to Show Dash Instead of Zero in Excel

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

Easy Methods to Show Dash Instead of Zero in Excel

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.
#,##0.00;[Red]-#,##0.00;"-"

Easy Methods to Show Dash Instead of Zero in Excel

  • 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


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.
=IF(C6=0,"-",C6)

Easy Methods to Show Dash Instead of Zero in Excel

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

Easy Methods to Show Dash Instead of Zero in Excel

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

Easy Methods to Show Dash Instead of Zero in Excel

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.

Easy Methods to Show Dash Instead of Zero in Excel

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

Easy Methods to Show Dash Instead of Zero in Excel

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

Easy Methods to Show Dash Instead of Zero in Excel

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

Easy Methods to Show Dash Instead of Zero in Excel

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

Nasir Muhammad Munim

Nasir Muhammad Munim

I am Nasir Muhammad Munim from Dhaka, Bangladesh. I work as an Excel and VBA Content Developer for Exceldemy right now. Electrical and electronic engineering was my major at Islamic University of Technology, where I got my degree. I love creative works and learning new things. And I'm really into online games, especially first-person shooter games.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo