Excel - Graphs and Legends

[EliseFreedman]

Hi There

I am currently creating a spreadsheet which automatically feeds into a pie chart. I only want to include within the Pie Chart, Accidents which are >0 for the time period that I am working on. I know I can manually delete the legends that I dont want to see on my chart but I wondered if there was a better way of doing it

Elise

 

[AnotherHiggins]

There might be a better way still, but the first thing that comes to mind is to use a Named Range for the Data Sources.

Let's say your data looks like this:
[tt]
Category Accidents
a 1
b 2
c 1
d 2
e 2
f 0
g 1
[/tt]
Insert a new Named Range, let's call it rngCategories. In the refers to box, you'd put something like this:
[tab][COLOR=blue white]=offset(Sheet1!$A$1, 1, 0, countif(Sheet1!$B:$B, ">0"), 1)[/color]

And another called rngAccidents. That would refer to
[tab][COLOR=blue white]=offset(Sheet1!$A$1, 1, 1, countif(Sheet1!$B:$B, ">0"), 1)[/color]

Here's the shortfall of my idea: You've got to sort the data Descending by column B.

But once that's done, you can single click on the pie chart.

You'll see the formula bar read something like this:
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$8,Sheet1!$B$2:$B$8,1)

Change the ranges to use your Named Ranges, which will exclude all zero values:
[tab][COLOR=blue white]=SERIES(Sheet1!$B$1,Book2!rngCategories,Book2!rngAccidents,1)[/color]


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.