Chart: only show legend elements with values

Brython

New Member
Joined
Nov 21, 2013
Messages
3
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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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!

Hi,
Not sure how your data is arrange/organised, but you could filter data to show only 'Greater than or equal to' 0 (zero), such would hide the rows with NA() and would display a chart with only data with value.
 
Upvote 0
Is there any way of doing this without filtering?

I'm determined to get the result of loads of Pivot Charts without using Pivot Tables or Pivot Charts, and I've got the data using COUNTIFS - which is fine - but that lists loads of 0 values...

The Pie Charts I'm creating don't show those 0 values in the chart, but do in the legend...
 
Upvote 0
sorry, without filtering I am not sure this could be solved unless you used vba. Or if you would use helper columns to segregate only the data that are >=1, but this not fit your query.
 
Upvote 0
I'd like to avoid VBA if possible... but I'd be open to using a helper column...

The columns at the moment look like this:

XY
AVSecondary
Option 112
Option 22
Option 344

<tbody>
</tbody>
Don't know whether it makes a difference, but the the AV details in the AV column are actually just grabbing the list from another column elsewhere (e.g. =B2), and the Count column is actually a CountIFS using two named ranges - ISP & SchoolType (the $Y$2 obviously referring to "Secondary" in this case)
Code:
=COUNTIFS(ISP,B3,SchoolType,$Y$2)

So the formulae in the columns looks like:
XY
AVSecondary
=B2=COUNTIFS(ISP,B2,SchoolType,$Y$2)
=B3=COUNTIFS(ISP,B3,SchoolType,$Y$2)
=B4=COUNTIFS(ISP,B4,SchoolType,$Y$2)

<tbody>
</tbody>
I'd certainly be interested in adding another column - or editing the formulae in the X column - I did try
Code:
=IF(Y2>0, B2, "")
then I tried
Code:
IF((COUNTIFS(ISP,B2,SchoolType,$Y$2))>0, COUNTIFS(ISP,B2,SchoolType,$Y$2), "")

then I tried them the other way round (if the count is equal to one, display "", otherwise display the text)... the best I've got with either attempt is as I mentioned - the text from the legend disappears, but the colour square remains...

If there's another column or a better formula I need to use, I'd be keen to try it :)
 
Upvote 0

Just before I try that - do you know if it'll work with Pie Charts too?

My problem/concern is, that I don't know how many rows I'm going to have, I've got 14 worksheets in the workbook, each worksheet is going to need 6 of these tables/pie charts, with a varying amount of number of rows in each - so it sounds like this would require constant checking and editing to make sure text boxes match the data in the table... if that makes sense...
 
Upvote 0
Just before I try that - do you know if it'll work with Pie Charts too?

Why don't you try it?

You can make a list that excludes the zero values like this (formula in D2 copied down and across):


Excel 2010
ABCDE
1AVSecondaryAVSecondary
2Option 112Option 112
3Option 20Option 344
4Option 344
Sheet1
Cell Formulas
RangeFormula
D2{=IFERROR(INDEX(A$2:A$4,SMALL(IF($B$2:$B$4<>0,ROW(A$2:A$4)-ROW(A$2)+1),ROWS(D$2:D2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.


Then create a dynamic name for that list excluding blanks and plot that.
 
Upvote 0
Your second option sounds & looks like exactly what I'm looking for!! :)

I've only recently discovered Named Ranges (in working out how to get a list of unique values from a range (which also uses array formulae) and I love them... so I'll investigate dynamic named ranges now too :)
 
Upvote 0
Okay, I've edited that formula to try to get the new table... (because my data's actually all over the place, and I'm trying to minimise unnecessary duplication of data (it's already taking about 5 seconds to update every time I make a change))

{=IFERROR(INDEX(B$3:B$20,SMALL(IF($Y$3:$Y$20<>0,ROW(B$3:B$20)-ROW(B$3)+1),ROWS(W$3:W3))),"")}

but it doesn't seem to be playing ball... Is the Small a named range or a general term?
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top