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.


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.

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

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


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.
#,##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.


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

Easy Methods to Show Dash Instead of Zero in Excel

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

Easy Methods to Show Dash Instead of Zero in Excel

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

Easy Methods to Show Dash Instead of Zero in Excel

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.

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 contain 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

  • Finally, 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: 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


<< Go Back to Find and Replace | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nasir Muhammad Munim
Nasir Muhammad Munim

Nasir Muhammad Munim has been an Excel and VBA Content Developer for over a year in Exceldemy and published more than 30 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Electrical and Electronic Engineering from the Islamic University of Technology. Apart from creating Excel tutorials, he is interested in developing PostgreSQL, MySQL, and Android applications. He is fascinated by CAD-based designing systems and building... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo