Automatic Removal of X-Axis Categorical Values in a Bar Chart

Hello,

I need to automatically remove, from the X-axis in a Bar Chart, items that have a Y-axis value of zero in the source data. Thus, only non-zero items will appear in the chart's X-axis. This is for Excel 2007.

Attached is a sample Excel file. In "Class A Chart" the only products that should appear along the X-axis are Product 1, 48, 50, 52, 66, & 69, as these are the only non-zero Y-axis values in the source data.

The ideal solution cannot alter the source data, and must dynamically add/remove items from the X-axis based on the Y-axis value.

Only automatic way would be to autofilter the data. But that would alter all charts.

I have used formula to create a table of INDEX values for each series. Along with a value for how many data points each series will contain.

This is then used to build a table of labels and values for each class.

Finally dynamic named ranges are used to define the data to be used in the chart. I have set up the named ranges for Class A only.
There are many post on the subject with links to sites explaining how dynamic named ranges work.