fbpx

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

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

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.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher
5/5

– Jason

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

20 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

  18. Khairu says:

    What about to hide zero if the value of the axis are in decimal? I tried this method but it doesn't work.

Leave a Reply