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!

[Andy Pope]

If Plot visible cells only is set you can hide the row/column containing the data.

[Brython]

If Plot visible cells only is set you can hide the row/column containing the data.

Thanks for your answer.
But this won't solve my problem because for a certain row, there might be no data, but the one beneath it does have data. So I can't just hide those columns. (I'm making a different graph per row out of the same table)

[Andy Pope]

Assuming the setting is on are you saying it has no affect when you hide the cells?

Post example worrkbook

[Brython]

It does have effect, however, not the effect that is desired. As I am currently working with confidential documents I can't post a document. I might make a draft one with exemplary figures in a minute if I find the time.

Basically what I have is a table of 7 rows and 30 columns and one additional header row. Basically I am making a graph for each row. So each row uses the header row and the data that is in each of the 30 columns on that specific row. The graph of each row only has 3-5 columns with data on average. So the legend has 29 values (30 columns minus the first columns). But each graph only has 3-5 values. I'm making a stacked bar chart with these 3-5 values and I only want those colors to be mentioned in my Legend. However, this does not work with the function you proposed. But I really appreciate your input already.

[Andy Pope]

If you have a single data table for all charts and those charts only require some of the data then hiding information will not work as once hidden it will affect all charts.

either use separate data tables for each chart or vba code to remove unwanted legend entries

[Brython]

Ok thanks. Too bad Excel does not have this function, this is a huge bummer regarding my limited time.
Thanks a lot!

[Andy Pope]

xl2013 has the ability, albeit manually, to filter out series

[Brython]

xl2013 has the ability, albeit manually, to filter out series

And how would this work then? This might be a savior

[Andy Pope]

If you select the chart object you should see 3 squares on the right, Add elements, Format and Filter.

Click the Filter and see what effect that has.

[Izandol]

It may also be done in code if required:

Please Login or Register  to view this content.

for example will hide the second series.

[kersin01]

If I'm using excel 2010, where do I locate the option button to "If Plot visible cells only"?

[Izandol]

You may right-click the chart, then 'Select Data...' then click the 'Hidden and empty cells' button.