Change Dashes To Zeroes In Excel

zero from Holes
You had to see the movie “Holes” to get this. :)

I might be alone in this, but I hate seeing hyphens in lieu of 0′s in tables and charts. Here’s what it looks like:

yucky hyphens in lieu of zeros
Click for larger image.

 

To change these dashes to 0′s select either the column or axis and press Ctrl/Command-1 to open formatting options. The formula, if you will follows this structure:

Positive; Negative; Zero; Text

So just look for “-”?? after the second semicolon and change it to a 0. Easy peezy!

change zero formatting in Excel

You can learn more about custom number formatting from the Microsoft site.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>