How to remove text from chart when value is zero??

[juncker88]

Hallo everybody,

My question relates to a simple bar-chart, where I have values from the past week’s sales performance for all our products.

Ex:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Week 45[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


How can I make the text disappear when the value is zero – and make the chart fill out, so there is no gaps??

It is an automated update, so the solution has be automatic.

Many thanks,

Andreas Juncker

 

[pgc01]

Hi Andreas
Welcome to the board

I see 3 options

1 - each time you update the data use a filter with sales different from 0. The chart won't use the hidden rows.

2 - without vba, I'd build a dynamic table based on the source data, where only the items with sales different from 0 appear.
In the case of your example, the result table would not have the item Apple.
I would then base the chart on this result table.
This would be a completely automatic solution.

3 - If you know vba, you could write some code to build the chart based on the items with sales different from 0.
Then you'd execute it each time there's an update