Dynamic Chart and Zero Values

[RobJay]

Hi guys,

I have attached a sample Excel file I need help with.

On Sheet1 I have sample data that is similar to the data I get dumped from our company database (this query cannot be altered in any way as it generates data for multiple reports)

On Sheet2 I have the data I need to chart based on the table in Sheet1.

My problem is when I apply a filter to the table on Sheet1 my Data on Sheet2 updates but I would like to hide the Chart Opt values that are shown as 0 on the Chart Breakdown.

See Chart Opt 10, 13 and 19 on the saved workbook attached.

I have tried changing the formatting of the number as suggested in other posts
I have also tried to set the 0 in my IFERROR formula to N/A() also to no effect

This example is of course a simplification of my original data set.

Any assistance or advice would be appreciated.

Rob

[BadlySpelledBuoy]

Hi Rob,

For clarification purposes, do you mean you don't want the 0.00% to show for those values or you don't want them to show at all? i.e. on the Y axis you don't want "CHART OPT 19 / 13 / 10" to show in any way?

BSB

[RobJay]

Hi BSB

I don't want the CHART OPT 19 / 13 / 10 Displayed on the chart as certain filters will only have 3 or 4 options with values and then there are too many Y Axis fields that only show zero...

Thanks
Rob

[MrShorty]

Have you tried using autofilter? You can have autofilter filter out the 0 values. When it hides the rows containing 0, those entries should also be removed from the chart.
https://support.office.com/en-US/Art...rs=en-US&ad=US

[RobJay]

Hi MrShorty,

Sometimes the simplest explanation stares you in the face.

The AutoFilter works 100%

Thank you