Bar chart needs to ignore 0 values

[Nightlytic]

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

 

[GraH - Guido]

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

 

[Nightlytic]

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!

 

[NARAYANK991]

Hi ,

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

Please look into it.

Narayan

 

[GraH - Guido]

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