Stack Chart Legend Display

[Guest]

I'm making a stack chart based on a quality report. My X-Axis is displayed
in Time
(Weeks) The Y-Axis displays defect counts by week. There are close to 20
different categories a defect can fall into, however, most of the time there
are 5 or 6 categories that have data week to week and some catergories that
have not had a defect yet. How do I make the stack chart legend only show
the categories that have data attached to it with a number larger than zero.
This way the legend won't display all 20 categories, but only the ones that
have a value. For Example, let's say I have 5 possible defect categories:
Spots, Dents, Wrinkles, Scratches, Stains. Each week, if the Quality
departments discovers a defect on a part, they add to the defect count under
that category for the week. Let's say the values for 1 week are as follows:
Spots-1, Dents-0, Wrinkles-0, Scratches-5, Stains-0. When I create a stacked
graph, I want the legend to only show the category labels for Spots and
Scratches because they have a value greater than zero. I'm sorry I wrote a
novel here, but hopefully you get the point. I know the manual way to do
this and because of the volume of graphs I have, it would be too tedious, so
hopefully an easier way exists. Thanks.

 

[Jon Peltier]

John -

Set up your data like this:

Totals Week 1 Week 2 Week 3
2 Spots 1 1
2 Dents 1 1
0 Wrinkles
8 Scratches 5 2 1
0 Stains

The Totals column is a running sum of each row. From the Data menu, choose Filter,
then AutoFilter. This puts a little down arrow for each cell in the top row of the
region. Click the dropdown arrow for Totals, select (Custom), and select "Is Greater
Than" in the first dropdown in the dialog, and select zero in the second.

By default, hidden rows are not shown in a chart, so any category of defect which
has a zero total will not appear in the chart or in the legend.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______