Chart: only show legend elements with values

[Brython]

Hi guys
I have 10 graphs with more than 20 legend entries. However, each graph only needs 3-4 elements out of the 20 legend entries in the graph.
Is there any way to force Excel to only show those legend entries that have a value? (without deleting them manually)
Thanks in advance!

 

[Andrew Poulsom]

Did you enter a zero in one of the cells under data? You can use a dynamic range to exclude the blanks, using a formula like:

=OFFSET(Sheet1!$P$2,0,0,COUNTIF(Sheet1!$P$2:$P$30,"?*"),1)

 

[RichCowell]

I tried it in the downloaded example, but showed results then as completely different...

I've not used dynamic ranges before, but I've just tried entering

=OFFSET(ISP!$P$2,0,0,COUNTIF(ISP!$P$2:$P$30,"?*"),1)

in the Chart data range, (changed the name of the worksheet to ISP) but when clicking OK it says "That function isn't valid"

 

[Andrew Poulsom]

The formula I posted is for a Name. For an article on dynamic ranges in charts see:

Create a Dynamic Chart

 

[RichCowell]

The formula I posted is for a Name. For an article on dynamic ranges in charts see:

Create a Dynamic Chart

Thanks - trying to work through that, got it all done up to the creating/editing the chart... does it only work with bar charts? I'm looking to use Pie Charts as it looks better to give an overall impression without including the values.

(not that I can get it working with the bar charts either!)

p.s. I do know I've got a lot to learn, and I am grateful for all the help!

 

[Andrew Poulsom]

It should work with any chart type.

 

[RichCowell]

Assuming I've created the ranges correctly, and I'm pretty sure I have (called ISP_PriVal and ISP_PriLab), where do I select those for the data sources?

 

[Andrew Poulsom]

One will be for the Series and the other for the Axis labels. Press Edit to change the source.

 

[RichCowell]

Okay, I've got it displaying the correct information, but it still shows the legend colour squares for the empty cells that contain the formulae... So at the moment I've got the formulae in 30 rows, but only data in 4, but it's still showing 30 colour squares in the legend, but only 4 with text...

So I'm kinda back where I was...

 

[Andrew Poulsom]

Do you want to put your workbook on a share like Box.com and post the URL so that I can take a look?

 

[RichCowell]

Do you want to put your workbook on a share like Box.com and post the URL so that I can take a look?

That'd be awesome if you can - I've started these extra stats in a new workbook, having replicated the data from the main workbook, but I've just put some dummy data in this one so it'll work independently...

Thanks

http://goo.gl/CMoujq