Perhaps you see so many zeros that it is distracting you from some important analyses. It may even be that your zero values are not accurately reflecting the state of the data where they appear. Excel may display zeros in places where you consider the data to be non-applicable. In such cases, showing zero as a dash (-) can be an effective way to enhance readability and improve the interpretation of your data. This post presents all the ways to show zero as a dash in Excel. Some of these ways simply change the display format while others replace the zero value itself.
Show Zero as Dash with Accounting Format
Showing zero as a dash has been a standard in the accounting industry for quite some time. Because of this , Excel includes its own built-in accounting format that will display zeros as a dash. You can implement the accounting format much like other cell formats. As quick as that, your numbers show all the features of the format! Two-decimal places are enforced on each number along with a thousand separator and a left-justified dollar sign ($). Most importantly, your zeros now show as dashes!
Show Zero as Dash with Custom Format
When you use a custom format you can control how positive and negative values are displayed. But you can also control how to display zero values as well. When using a custom format, it’s sometimes a good idea to first select the format that most closely resembles what you want. You can then modify the format to suit your needs. Here’s how to apply a complete custom format. This will open the Format Cells dialog box which will allow you to adjust a variety of cell formatting options including any number format. 💡 Tip: You can also open the Format Cells menu with the Ctrl + 1 keyboard shortcut. The format string used in the custom number format consists of three parts separated by a ; character.
The first part determines the format to use for positive numbers.The second part determines the format to use for negative numbers.The third part determines the format used for zero values. Because the third part consists of a dash (-), this causes zeros to show as a dash!
The above shows the results. Of note, if you select any of your cells containing a dash, as above, the formula bar still shows the zero value, as it should. The cells will still contain their zero value, but the formatting causes them to display as a dash. 📝 Note: If you select any of your cells containing a dash, the formula bar still shows the zero value. The cells will still contain their zero value, but the formatting causes them to display as a dash.
Show Zero as Dash with IF Function
The IF function offers a quick way to conditionally show a dash by testing the cell for a zero value. The above formula will test if the value in cell C3 is zero and return a dash ("-") if it is. Otherwise, it will return the original value. This will keep the non-zero values as numbers, but the cells with dashes will actually be text values.
Show Zero as Dash with TEXT Function
The TEXT function can show zero as a dash by leveraging the same custom format string used previously in this post. The above formula will format the content of cell C3 based on the supplied format string “#,##0;-#,##0;-”. The results from this formula will be text values, so this may not be the ideal solution depending on what you need to do with the formatted values. You won’t be able to use the results in any further numerical calculations since they are text.
Show Zero as Dash with Find and Replace
Excel has a Find and Replace feature similar to those you’ve probably seen in other software products outside of Excel. You can use this feature to replace zero values with dashes entirely. The results will cause the dashed cells to become text rather than numeric. Follow these steps to replace your zero values from any range. This will replace all instances of zeros (0) with dashes (-) within your selection. This is a key part of the process. Matching the entire cell contents means you won’t inadvertently change a value such as 520 to 52-. Excel will replace all the zero values in your selection with a dash.
Show Zero as Dash in Pivot Tables
A pivot table might show you an empty row if a column combination contains zero data. For example, when you use a pivot table to show yearly sales since 2021 by sales person, any new salesperson hired in 2022 won’t have any sales for 2021. If you’d like to show a dash in 2021 for such a new salesperson, you first need to show empty rows. It’s important to stress that the standard keyboard dash character does not work here, so the em dash character is a similar alternative. As you can see, your new salesperson displays a dash in the year where he had zero sales!
Show Zero as Dash with VBA
VBA is a great way to automate most tasks in Excel. You can use VBA to replace zeros as well in a similar fashion to the Find and Replace feature. But this way you won’t need to input any values to find, or update settings to get the job done. The code will take care of that! You can create a macro that will loop through the selected range and identify any cells with a zero and replace them with a dash. Follow these steps to open the visual basic editor. You might not see the Developer tab, as it’s hidden by default. You can enable the Developer tab in your ribbon if you don’t see it already. You can also use the Alt + F11 keyboard shortcut to directly open the visual basic editor. This will create a new module where you can copy and paste the above code. This code will loop through each cell in the selected range and check if its value is zero. If the value is zero then the cell content is updated to a dash. Note the Len function used to check the length of the cell content. This is necessary because the code treats an empty cell as a zero, so checking the length avoids an empty cell from being updated. Now you can select any range in the workbook and run the VBA code to replace the zeros!
Show Zero as Dash with Office Scripts
VBA isn’t the only way to automate things in Excel. You can also use Office Scripts if you’re using Excel online with a Microsoft 365 business plan. Open your Excel workbook in the web browser and go to the Automate tab then select the New Script option. This will open the Code Editor on the right side of the workbook. You can then copy and paste the above code into the Code Editor and press the Save script button. This code will loop through the selected range, cell by cell. If the cell value is zero, its value is set to a dash. As in VBA, the length of the cell content is checked to ensure that a blank cell does not get erroneously updated. To test your code, select your desired range and press the Run button in the Code Editor. When it finishes, your zeros will be replaced with dashes!
Conclusions
There are many reasons to show zeros as dashes in Excel, varying from plain aesthetics to a reinforcement of how your data should be interpreted. The least invasive option would be to change its display through the use of a custom format or accounting format, with the accounting format particularly suited to currency data. The other methods actually change your underlying cell values, so it’s good to keep in mind that any numerical calculations you have might be affected by those changes. An extra advantage of the VBA and Office Scripts methods is that, once verified, they can be re-applied quickly and reliably as needed. Have you needed to show zero values as dashes? How did you get it done? Let me know in the comments below!
