Charts with a Variable Number of Series

The Problem

The documentation does not describe how to implement charts with a variable number of series.

The Solution

Right now there are 2 ways to do implement charts with a variable number of series. Each method has its advantages and disadvantages.

Method 1: Ghost Series

This method works very well but the problem with it is that there are usually extra blank series which can make some 3D charts, as well as the Legend box, look a bit strange (but the charts are still useful. You can still read them)

The method is this:

Create more series in the Source Workbook than you could possibly need. This is very easy, just select a larger range than you need before starting the Chart Wizard.

If you know you will need no more than 10 series, then create 10.

That's it really. If not enough Field Marker data is inserted then the unused series will just be blank series.

Method 2: VBA

The VBA approach is very clean and works perfectly except for the fact that it uses VBA. The big drawback to this is that the user usually gets the annoying Excel allow macros? dialog.

If you would like to use VBA to implement charts with a variable number of series, please contact support@excelspeedgen.com