Dynamic serie selection on an excel chart

Question

I have a chart with a lot of series to plot. Somehow, these series are too numerous, so i want to only plot some of them. I can do this "statically", by hand, by duplicating the table and removing the unnecessary lines, but obviously, i would prefer something more dynamic, since new series are constantly introduced.

Typically, i want to only draw series which reach a certain threshold, or a certain rank. I can test the condition, and return a result such as 0 or 1 for example.

Now, i don't know how i can use this information to dynamically select the series that will be plotted into the chart. Is it even possible ?

Answer 1

Another approach involving a table and its filters. Here is the starting table, series names in first column, categories in first row, last column has some formula testing the values, returning true or false.

Insert a chart using all columns of the table except the last, and you'll probably have to switch rows and columns, so the series are plotted by row.

Click the autofilter dropdown in the header for the test column, and under Number Filters, select Equals, and enter True in the box.

Your test formula need not return true or false, it may just calculate a function of the values, or whatever you want. Just pick a suitable filter condition to hide what you don't want to show in the chart.

Answer 2

Yes.

Using dynamic ranges works very well with charts. There are two typical applications for this

1. Using dynamic ranges where the series is known, but the length of the data set for the series is variable.Stephen Bullen's FunChrt1.zip is an excellent example of this technique. No VBA required

2. When the number of series itself is variable than some basic VBA is required as when a dynamic range is assigned as a sourcedata range it becomes hard-coded (unlike a defined name for a specific chart series which stays "live"). This sounds like the approach for your query. Jon Peltier covers this well at Dynamic Chart Source Data