SubscribeHow to hide “0″ in chart axis [quick tip]
Posted on December 14th, 2009 in Charts and Graphs , Excel Howtos - 9 comments
Have you ever wondered how you can hide that 0 (zero) at axis bottom? Like this…,
Here is a handy little trick to do just that:
- Select the axis and press CTRL+1 (or right click and select “Format axis”)
- Go to “Number” tab. Select “Custom”
- Specify the custom formatting code as
#,##0;-#,##0;; - Press “Add” if you are using Excel 2007, otherwise press just OK.
- That is all.
The trick uses custom number formatting codes in excel to show blanks whenever axis value is Zero, thus hiding the zeros at the bottom of axis. [even more juicy awesome kickass stuff on custom cell formatting]
Learn more quick excel tips and charting tricks.
PS: I am in Stockholm this week, to meet some customers. Internet access is limited plus I am too busy exploring this beautiful city. So we will have only quick tips this week.
|
Comments
RSS feed for comments on this post. TrackBack URI
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 350+ articles and tutorials on using excel, making better charts. 
Stockholm FTW!
Why would you want to hide the zero? It’s part of the display and should be there for all to see. If it’s not there, what value are we to assume? Zero, of course, but why should the reader have to assume? Full disclosure!!
Bill: Somtimes zeros just get in the way, distrupting the message of the chart. I typically hide the zeros in stacked column charts.
See this:
http://datapigtechnologies.com/blog/index.php/suppressing-zeros-in-chart-labels/
This is a good quick tip.
Mike – That’s a good example of when to hide zero, but the real question is why would anyone still use stacked bars for more than 2 series? There are much more effect ways to show that data. That should have been the 1st response to Susan.
@Blogg reader: Stockholm is a beautiful place, I am enjoying my stay here (except that it has been snowing crazy for the last few days)
@Bill: Depending on your audience and situations “zero” could be chart junk. Then you can hide it using this tip.
@Mike: Thanks for the link
@Tony: Now.. that is another discussion…
Great Charting tip but how would I keep 2 decimal points in the Y axis number ? When I make the mentioned change I get 25 instead of 25.00
Thanks
Mike; Your example is a very different situation. And, Tony’s comment is right on too.
Chandoo, suppressing zero in this example is not a chart junk correction, it’s wrong! Make it a smaller font if need be, but don’t toss it out.
Wes,
use the format #0.00
OMG i’m sitting here at our company in Stockholm and reading this, you’re a life saver!