[SOLVED] Stacked Column Graph with automatic zero column removal

**Niper** · 03-05-2010, 10:43 AM

All,

Great Board...picked up loads of tips, as such my excel knowledge is getting OK...but I do need help with this one...as such thought I'd throw it out and see if anyone can help!

I have a list of companies with jobs outstanding (split by time). I want to show these jobs as stacked column graph...but if the total number of jobs is zero, then there is no need to show the column..hence remove it.

You can see from the attached that there are 25 companies, of which 10 currently have no jobs outstanding...how can I remove these.!

I'd appreciate not only an example spreadsheet, but also some idea of how you did it...as I've tried adapting a couple of other examples for removing zeros from pie charts with little success.

Thanks again
Niper

**Andy Pope** · 03-05-2010, 11:11 AM

You need to determine which rows to plot, use SUM for that.
The check the summed value and report the row or lastrow+1
Use the SMALL function to list reporting rows.
Build a new chart data table using the INDEX function.

With the new chart data table you can set up 8 named ranges to define chart labels and series ranges.

**Niper** · 03-05-2010, 01:38 PM

Thanks Andy for the speedy response, if I want to cut a paste into another spreadsheet, is there anything special I need to do? found the named ranges, anything else?