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

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

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

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

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

## Related Articles

<< Go Back to Find and Replace | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!