How to hide “0” in chart axis [quick tip]

Posted on December 14th, 2009 in Charts and Graphs , Excel Howtos - 19 comments

Have you ever wondered how you can hide that 0 (zero) at axis bottom? Like this…,

How to hide zero in chart axis

Here is a handy little trick to do just that:

    How to hide zero in chart axis - use custom cell formatting codes

  1. Select the axis and press CTRL+1 (or right click and select “Format axis”)
  2. Go to “Number” tab. Select “Custom”
  3. Specify the custom formatting code as #,##0;-#,##0;;
  4. Press “Add” if you are using Excel 2007, otherwise press just OK.
  5. 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.

Written by Chandoo
Tags: , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

19 Responses to “How to hide “0” in chart axis [quick tip]”

  1. Blogg reader says:

    Stockholm FTW!
    🙂

  2. Bill says:

    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!!

  3. 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/

  4. Tony Rose says:

    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.

  5. Chandoo says:

    @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... 😉

  6. Wes says:

    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

  7. Bill says:

    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.

  8. Hui... says:

    Wes,
    use the format #0.00

  9. Gabriel says:

    OMG i'm sitting here at our company in Stockholm and reading this, you're a life saver!

  10. Phung nt says:

    p/s: i want hide # % on chart

  11. Hui... says:

    @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 #

  12. Penny says:

    Thank you so much for this!!!!

  13. bob says:

    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!

  14. kafran says:

    Chandoo you have the best Excel Knowledge Repository =). Please keep this always online. Thank you and congrats.

  15. Amed says:

    Thanks so much, that was a clear easy solution for the problem .

  16. Martin says:

    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

  17. Learner says:

    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

Leave a Reply