Add Multiple Series To Chart Excel VBA Macro

We can add a chart in Excel with just one line of VBA code specifying a range with source data. Then we can add as many series (i.e. lines, columns) as needed with an additional line for each new series. That may be needed to keep updating an existing chart with additional weekly or monthly data. In this post we add series that represent additional trials of an ongoing clinical test for a number of sample groups.

Macro code:

 
 Sub AddChartSeries()
 
 Dim MyChart As ChartObject
 Set MyChart = ActiveSheet.ChartObjects.Add(50, 50, 400, 200)
 With MyChart.Chart
         .ChartType = xlColumnClustered
         .SetSourceData Source:=Range("A1:A6")  'series label is added by default
         'Add more series to chart
         .SeriesCollection.Add Source:=Range("B1:B6"), SeriesLabels:=True
         .SeriesCollection.Add Source:=Range("C1:C6"), SeriesLabels:=True
 End With
 
 End Sub
 

 

Macro explained:

• First we declare an object variable (MyChart) as a ChartObject. The ChartObject object represents an embedded chart in a worksheet (as opposed to a Chart “sheet” object).
• Next, we add the chart to the active sheet with the following position/dimension: 50 points from the left of the worksheet, 50 points from the top, 400 points wide and 200 points high. But we could also target an existing chart; in that case we would use the following expression instead.

 Set MyChart = ActiveSheet.ChartObjects("MyChartName")

 

• We define the chart type, in this example is a column clustered chart. We do not need to do this if we choose to target an existing chart. Similarly, we do not need to set the source data when having an existing chart. In this example, we do it from scratch and therefore we need to set the source data range of the chart. The code above selects the data in column A alone. After that, we will keep adding series (see later). We could also set the source to all three existing columns (range A1:C6). That would create a chart with all the existing data (the three trials). So, the following would create the full chart with all three trials at once:

 MyChart.Chart.SetSourceData Source:=Range("A1:C6") 

 

• However, this macro example attempts to show how to add more series and therefore we have started with data in column A alone (range A1:A6). If the range includes the first row with series labels, those are recognized and added by default.
• Now we can add as many series a needed targeting the SeriesCollection method and specifying the source data for each of them. Note that we need to indicate whether the series includes labels if the range spans from row 1 and we want to show the series label.

 

This is how we add multiple series to a chart in Excel using VBA macros.

Other examples:

• How to Extract Emails From Outlook with Excel VBA macros
• How to Add Conditional Formatting For Another Cell with Excel VBA macros
• How to Send Google Mail from Excel with VBA macros