• 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...

  • 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

Nightlytic

Member
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
 

Attachments

  • ChartChandoo Example.xlsx
    13.6 KB · Views: 4
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).
 

Attachments

  • Copy of ChartChandoo Example.xlsx
    19.3 KB · Views: 6
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!
 
Hi ,

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

Please look into it.

Narayan
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.
 

Attachments

  • Copy of ChartChandoo Example.xlsx
    19.2 KB · Views: 6
Back
Top