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.
24 Responses
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!!
For the same reason I might wear a bright red tie. Because I think it looks better.
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!
p/s: i want hide # % on chart
@Phung
Where is your # % ?
If it is in a label or a axis use a custom Number Format and put a Ctrl J in front of the #
Thank you so much for this!!!!
BOOM! finally the answer I was looking for… I want to hide the zeros because my chart is being used on template that I use for estimating construction jobs. Sometimes there are fields associated to costs that are just not a part of the job, so rather then have very random looking zeros, i’d rather they just disappear. Good Solution, Thanks!
Chandoo you have the best Excel Knowledge Repository =). Please keep this always online. Thank you and congrats.
Thanks so much, that was a clear easy solution for the problem .
Hi Chandoo do you know the format code to do the same but with text
I mean if there’s no text in the cell don’t show in chart
please help me
Thanks
Hi,
I do not want excel to graph the zero values. It should ignore them completely & not appear in graph at all. Any way to do it.
Do not want a line chart . I want a stacked chart for it.
Thanks
What about to hide zero if the value of the axis are in decimal? I tried this method but it doesn’t work.
I want to customize chart axis min & max values based on formula.
Can you please check posibility?
This is currently not possible.
@Pravin
Chandoo’s answer should be:
“This is currently not possible unless you use VBA”
I made it and love it. Thank you !!!