1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Bar chart needs to ignore 0 values

Discussion in 'Discuss Data Visualizations and Charting' started by Nightlytic, Jan 10, 2018.

  1. Nightlytic

    Nightlytic Member

    Messages:
    110
    Can I please ask for a little help? :)

    See attached,

    I need the 'Bakery' to not show at all on the bar chart, if the corresponding total is 0 (this number is returned by a formula)

    Essentially I have a challenge: can't use VBA, or pivot tables, allowed to create helper columns though.
    I recon I either need to:
    a. Automatically resize the chart selection area to exclude items that have a 0 (as result of formulae) and not therefore show them on the chart at all.
    b. Get the charts to skip these gaps.

    Maybe there's another solution. I researched this and can't see anything that gets rid of this empty space
    upload_2018-1-10_15-25-10.png

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    791
    You can use aggregate inside an index to build 2 helper columns.
    And then via the name manager build the dynamic ranges (I've used an offset formula).
    Some fine Mike Girvin/Bob Umlas tricks (and perhaps even some other dudes).

    Attached Files:

    Khalid NGO and Thomas Kuriakose like this.
  3. Nightlytic

    Nightlytic Member

    Messages:
    110
    Hi GraH, thank you for this! I see how this works, and lovely seems I can adapt multiple series into it. Pretty flexible!

    Many thanks!
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    There seems to be a problem with the horizontal axis labels.

    Please look into it.

    Narayan
  5. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    791
    Indeed, I believe I did a screw up :oops: in the data series. Forgot to change the formula in Axis Label Reference. I did not refer to the name manager but to the original data series. Corrected it.

    Attached Files:

    NARAYANK991 likes this.

Share This Page