How To Make Zero Become Dash In Excel

Are you tired of seeing “0” values cluttering up your Excel spreadsheets? Sometimes, a dash can be a more effective way of representing zero, particularly in tables or reports where you want to keep the focus on non-zero values. Fortunately, Excel offers several ways to make zero appear as a dash, including using a custom number format, conditional formatting, and the IF formula.

In this article, we’ll walk you through step-by-step instructions for each method, so you can choose the one that works best for your needs. We’ll also provide sample data sets to help you practice each technique. By the end of this article, you’ll be able to transform all the zeros in your spreadsheets into elegant dashes that will help your data stand out.

Let’s learn this with the help of the following dataset.

Method 1 – Using Accounting Number Format

Step 1 – Select Cells

• Select the cells or range of cells that contain the data you want to format. You can do this by clicking and dragging your cursor over the cells or by clicking the column or row headers to select the entire column or row.

Step 2 – Go To The Home Tab

• Go to the “Home” tab on the ribbon, then click on the Format Cell launcher button in the “Number” group as highlighted in the picture below. Alternatively, you can right-click on the selected cells and choose “Format Cells” from the context menu.

Step 3 – Format Cells Dialog Box

• In the Format Cells dialog box, select the “Accounting” category from the list on the left.
• In the “Symbol” dropdown, choose the currency symbol as None or any other symbol that you want to use, such as “$” for US dollars.
• In the “Decimal places” field, enter “0” to display whole numbers only.
• Click the OK button.

Step 4 – Zero Converted Into Dash

• All the zero values will be converted into dashes.

Method 2 – Using IF Formula

Step 1 – Place Equals To Sign (=)

• Insert a new column to the right of the column that contains the values you want to format, or you can use any existing column and place equals (=) to sign.

Step 2 – Type The Formula

• Enter the following formula: =IF(B2=0,”- “,B1)
• This formula checks if the value in cell A1 is equal to zero. If it is, the formula returns a dash. If it’s not, the formula returns the original value.
• Note that the space after the dash in the formula is optional, and is included here to give some space between the dash and the next cell.

Step 3 – Copy The Formula Down Below

• Copy the formula down to all the cells below.

Step 4 – Convert Formula Into Value

• Select the cells with the formula.
• Right-click on the selection, and choose “Copy” from the context menu.
• Again, right-click on the selected column, and choose “Paste Special” from the context menu.

Step 5 – Paste Special Dialog Box

• In the “Paste Special” dialog box, select “Values” and click “OK.”
• Now, any cell(s) containing zero in the original column will be replaced with a dash in the new column. You can then choose to hide the original column if you only want to display the new formatted column.

Step 6 – Zero Becomes Dash

• Zero values in the dataset converted into dashes.

Method 3 – Using Find & Replace Option

Step 1 – Select Cells

• Select the cells or range of cells that contain the data.

Step 2 – Open Find And Replace Dialog Box

• Click on the “Home” tab on the ribbon.
• In the “Editing” group, you will see a “Find & Select” button. Click on it to open a drop-down menu.
• From the drop-down menu, click on “Replace” to open the Find and Replace dialog box.
• Alternatively, you can use the keyboard shortcut “Ctrl + H” to directly open the Find and Replace dialog box.

Step 3 – Find And Replace Dialog Box

• In the “Find what” field, type “0” (without quotes). This tells Excel to find all occurrences of the number zero in the selected cells.
• In the “Replace with” field, type “-” (without quotes). This tells Excel to replace all occurrences of the number zero with a dash.
• Click on the “Options” button to expand the options.
• Under “Search”, make sure that “Within: Sheet” is selected. This ensures that Excel only searches for zeros within the current sheet.
• Check mark the “Match entire cell contents” option.
• Click on “Replace All” to replace all instances of zero with a dash in the selected cells.
• When the replacement process is complete, click on “Close” to close the Find and Replace dialog box.

Step 4 – Zero Becomes Dashes

• Zero can be replaced with dashes through this way.