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]

You can use + for Or:

Excel 2010
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	School Type					Devices			Staff				School Type	Devices	Staff
2	Primary					20			5				Primary	20	5
3	Secondary					500			50				Secondary	500	50
4	College					400			30				Secondary	1200	70
5	Secondary					1200			70				Primary	50	12
6	Primary					50			12
7	College					80			16
Sheet1

Cell Formulas
Range	Formula
N2	{=IFERROR(INDEX(B$2:B$500,SMALL(IF((($B$2:$B$500="Primary")+($B$2:$B$500="Secondary")),ROW(B$2:B$500)-ROW(B$2)+1),ROWS(N$2:N2))),"")}
O2	{=IFERROR(INDEX(G$2:G$500,SMALL(IF((($B$2:$B$500="Primary")+($B$2:$B$500="Secondary")),ROW($B$2:$B$500)-ROW($B$2)+1),ROWS(O$2:O2))),"")}
P2	{=IFERROR(INDEX(J$2:J$500,SMALL(IF((($B$2:$B$500="Primary")+($B$2:$B$500="Secondary")),ROW($B$2:$B$500)-ROW($B$2)+1),ROWS(P$2:P2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RichCowell]

Thank you!!

You've helped me create one heck of a spreadsheet!! Well, two now as it needed breaking down lol, but I wouldn't have been able to do it without your help!!

 

[RichCowell]

Nope, turns out there's one more question...

I'm trying to do a scatter graph from these latest results, when I select just the data, the scatter graph obviously works perfectly, when I select more cells at the bottom (I want to set it to use about 500 rows) it ignores the data in the first column and decides they're a numerical count so the first row is 1, second is 2 etc.

I've tried creating Dynamic Ranges like I did for the other data, which seems to work, but when I try using those Dynamic Ranges in the Select Data window, it doesn't let me enter it... Saying the Formula you typed contained an error...

Does that make sense? Should I be able to use dynamic ranges with Scatter Graphs?

 

[Andrew Poulsom]

Yes dynamic ranges can by used in XY Scatter charts. What do they refer to?

 

[RichCowell]

Just the data I filtered down using that same formula in...

I've removed all the unnecessary sheets and deleted their named ranges, so this just contains what I'm talking about:
https://drive.google.com/file/d/0B3hscJGVyxGBLUZ5a2Fsc1VpOUU/edit?usp=sharing

 

[Andrew Poulsom]

Define RatD_PriDev as:

=OFFSET(Ratios!$J$4,0,0,COUNT(Ratios!$J$1:$J$500),1)

 

[RichCowell]

Define RatD_PriDev as:

=OFFSET(Ratios!$J$4,0,0,COUNT(Ratios!$J$1:$J$500),1)

You're too good!!!! Thank you, again!! Can I buy you a bottle of wine or something as a thank you? I would have given up hunting long ago if you hadn't helped me so much!

 

[Andrew Poulsom]

Your thanks is reward enough. Glad to help.