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

We will consider this example below. Here, the dataset contains the number of different products sold over three days and their Product IDs.

Method 1 – Using the Format Cells Dialogue Box

Steps:

• Select the dataset you want to reformat (in the sample, that’s C6:E10).

• Press Ctrl + 1. A dialog box named Format Cells will appear.

• Go to Accounting in the Format cells panel.
• Select None as Symbol and press OK.

• You’ll get a dash (-) instead of a zero throughout the cell range.

Method 2 – Defining a Custom Number Format

Steps:

• Select the cell range and press Ctrl + 1 to open the Format Cells panel.

• Select Custom, and in the Type section, copy and paste the following number format and then press OK.

• We will see all the zeros have been replaced by a dash.

Method 3 – Utilizing the IF Formula

In our case, we will make a helper Day 1 column under the New field.

Steps:

• Select the cell where we will show the updated values. In our case for C6, we will show it in F6.

• Use the following formula in the formula bar.

• Press Enter.

• Drag the Fill Handle to cell F10 to automatically update the rest of the cells.

• We will see a dash inserted instead of zero in the F7 cell.

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

Method 4 – Using the Replace Feature

Steps:

• Press Ctrl + H to open the Find and Replace dialog box.

• Put 0 in the Find what box and a dash (–) in the Replace with box.

• Check Match entire cell contents and click on the Find All option.

• This will show all the cells containing the 0 value.

• Click on Replace All.

• We will see all the zeroes have been replaced by dashes 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 in new columns.

Download the Practice Workbook

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