How-To

How to Convert a Zero to a Dash in Excel

By Adam Davidson

Updated March 24, 2024

Zeroes in Excel are still registered as numbers by functions such as COUNT. Learn how to convert a zero to a dash in Excel.

If you’re anything like me, you’ve played around and experimented with how big an Excel spreadsheet can be. If you can’t be bothered to do all that scrolling, we can help you out: 1,047,576 rows by 16, 384 columns. In other words, a lot of cells. So, if you’ve got a huge amount of data in Excel, it can be hard to tell the wood from the trees. Trying to spot an issue with a formula or a cell that contains zeroes among all the other numbers can be a rain pain.

One way to help is to convert all the zeroes in your spreadsheet into dashes. These make it far easier to see which cells contain non-zero values and which don’t. If you want to add a dash of dashes to your spreadsheet, then here’s how to convert a zero to a dash in Excel.

Why Convert a Zero to a Dash in Excel?

There are a number of reasons why you might want to replace the zeroes in your Excel spreadsheet with dashes. One of the reasons is that it’s hard to distinguish between zero and non-zero values in your spreadsheet at a quick glance. By replacing the zeroes with dashes, you can easily see which cells have non-zero values.

Another reason you might want to convert a zero to a dash in Excel is because zeroes are classed as numbers by functions such as COUNT. If you want to instead count all the cells that contain non-zero values, then converting the zeroes to dashes is a quick and easy way to do so.

How to Convert a Zero to a Dash in Excel Using Find & Replace

There are several ways to convert a zero to a dash in Excel. A quick and easy way to convert the zeroes already in your spreadsheet to dashes is to use Find & Replace. It’s important to note that this method will convert any zeroes currently in your spreadsheet, but if more zeroes are added to your data at a later date, these new values will still show as zeroes.

To convert a zero to a dash in Excel using Find & Replace:

1. Open the spreadsheet containing your data.
2. Highlight all of the data that contains the zeroes you want to convert.
3. In the Editing section of the Home ribbon, click Find & Select.
   
4. Select Replace.
   
5. In the Find What field, enter 0.
   
6. In the Replace With field, enter a dash.
   
7. Ensure that Match Entire Cell Contents is checked. Otherwise, any zeroes within numbers such as 20 or 105 will also be converted to dashes.
   
8. Click Replace All.
9. All of the zeroes in your selected data will be converted to dashes.
10. If the dashes appear to the left-hand side of your cells, select all of the data again and click the Align Right
11. As you can see from the totals at the bottom, functions such as COUNT will not count the dashes in your data.
    

How to Convert a Zero to a Dash Using Accounting Formatting

Another method to convert a zero to a dash in Excel is to use accounting formatting. This can be used to add symbols to your numbers, but if you opt to add no symbol, your non-zero values will remain the same, but all of your zeros will be converted to dashes.

The benefit of this method is that if you apply it to empty cells, when you enter zero in those cells, they will immediately convert into dashes, meaning you only need to do this once. It’s also important to note that with this method, functions such as COUNT will still count the dashes as numbers.

To convert a zero to a dash in Excel using the accounting formatting:

1. Open the spreadsheet containing the data you want to convert.
2. Highlight all of the data you want to convert, as well as any empty cells that you intend to hold data in the future.
3. Right-click anywhere in your selected cells.
4. Select Format Cells.
   
5. Under Category select Accounting.
   
6. Change the value of Decimal Places to 0 (unless you want your non-zero data to have a specific number of decimal places).
   
7. Click the Symbol drop-down and select None.
   
8. Click OK and your zeroes will now be dashes.
9. The COUNT values include both the numbers and dashes.
   

How to Convert a Zero to a Dash Using Custom Formatting

You can also use custom formatting to convert a zero to a dash in Excel. This allows you to specify how Excel formats positive numbers, negative numbers, and zeroes. As with the accounting formatting method, the dashes produced by this method will still be classed as numbers by functions such as COUNT.

To convert a zero to a dash in Excel using custom formatting:

1. Open the spreadsheet you want to edit.
2. Highlight all the data you want to convert along with any empty cells that you wish to apply the same formatting to.
3. Right-click anywhere in your selection.
4. Click Format Cells.
   
5. Under Category select Custom.
   
6. Click the Type drop-down and scroll down until you see the time formatting options. Just beneath, you will see some other formats that look like gibberish.
7. Select the second of these. It should read _(* #,##0_);_(* (#,##0);_(* “-“_);_(@_).
   
8. If you can’t find this option, you can enter the format yourself by typing in the Type field.
9. Click OK and all of your zeroes should convert to dashes, with the other values left as they were.
10. If your numbers contain decimals, change each 0 in the above formatting to 0.00 or however many decimal places you need. The dashes remain unaffected.
11. The COUNT values will still include these dashes in their totals.
    

Troubleshoot Your Excel Spreadsheets

I write a lot about Microsoft Excel, so if you’re still having issues, check out our archive dedicated to the topic. If you are still having issues, drop your question below in the comments, or feel free to create a new help topic in the groovyPost community forum. I monitor the forum daily and will try to help ya out. The forum is a bit easier to use than comments since you can use screenshots and other features.

Good luck!