### Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog

Books at Amazon.com

# Excel XY Chart Variations with VBA

This page builds on the example code in Quick Chart VBA Examples. This page contains sample VBA procedures for constructing XY charts using data arrangements which would strangle the Chart Wizard and lead to tedious redefinition of chart series data sources in the Source Data dialog or in the chart series formulas.

Outline: Excel XY Chart Variations with VBA

### Excel XY Scatter Chart Variations

An XY Scatter chart with a single charted series has one set of X data and one set of Y data. When you select more than two columns (or rows) of data and choose a Scatter chart, Excel's standard treatment is to assume all series share the X values in the first column or row, and that each successive column or row holds the Y data for a separate series. Sometimes, your data may be structured differently. For example, all the series may share their Y values, but have distinct X values. Or more commonly, each series may have its own X values and Y values. Generally you would have to do a lot of cutting and pasting, or add all series after the first one, but you can write some relatively simple VBA precedures to construct these charts automatically.

#### Standard Series with Shared X Values and Distinct Y Values

When the Chart Wizard creates an XY Scatter chart from a worksheet range, such as the data range below left, Excel assumes the top row of the selected range contains series names, and the left hand column contains category (X) values. (In these tables, the X values are shown in aqua and the Y values in yellow.) The chart wizard creates a chart like the one below the table. The corresponding points on each series line up above each shared X value. No code is needed here, because Excel's default behavior treats the data just fine.

 X Y1 Y2 Y3 Y4 Y5 Y6 1 10 40 70 100 130 160 2 20 48 76 104 132 160 3 30 56 82 108 134 160 4 40 64 88 112 136 160 5 50 72 94 116 138 160 6 60 80 100 120 140 160 7 70 88 106 124 142 160 8 80 96 112 128 144 160 9 90 104 118 132 146 160 10 100 112 124 136 148 160 11 110 120 130 140 150 160

If you decide to make a line, column, or area chart from the table above, Excel will not always assume your chart should use the first column for its category values. If the first column contains data which is somehow recognizable as different from the rest of the columns (e.g., it is nonnumeric, such as text labels, or it consists of dates), Excel will decide to use it for categories. But if it is numeric, you will get a chart with one more series than expected. Notice that the line chart below has one lonely series at the bottom, labeled "X", whose Y values range from 1 to 11.

To help Excel parse the data range, no matter which chart type you will be using, simply clear the top left cell of the data range. This tells Excel that the top row is different from the other rows and the left column is different from the other columns. Another trick is to create an XY chart from the data, then change the entire chart to the type you want.

Top of Page

#### Series with Distinct X Values and Shared Y Values

Sometimes your data is differently arranged, and each series might have different X values for each shared Y value. The data and the chart below illustrate this.

 X1 X2 X3 X4 X5 X6 Y 10 20 30 40 50 60 1 10 22 34 46 58 70 2 10 24 38 52 66 80 3 10 26 42 58 74 90 4 10 28 46 64 82 100 5 10 30 50 70 90 110 6 10 32 54 76 98 120 7 10 34 58 82 106 130 8 10 36 62 88 114 140 9 10 38 66 94 122 150 10 10 40 70 100 130 160 11

Normally you'd have to do lots of cutting and pasting to chart these series of data. But the following simple VBA procedure will construct such a chart, as quickly as the Chart Wizard constructs a "regular" chart.

 ```Option Explicit Sub MultiX_OneY_Chart() Dim rngDataSource As Range Dim iDataRowsCt As Long Dim iDataColsCt As Integer Dim iSrsIx As Integer Dim chtChart As Chart Dim srsNew As Series If Not TypeName(Selection) = "Range" Then '' Doesn't work if no range is selected MsgBox "Please select a data range and try again.", _ vbExclamation, "No Range Selected" Else Set rngDataSource = Selection With rngDataSource iDataRowsCt = .Rows.Count iDataColsCt = .Columns.Count End With '' Create the chart Set chtChart = ActiveSheet.ChartObjects.Add( _ Left:=ActiveSheet.Columns(ActiveWindow.ScrollColumn).Left + _ ActiveWindow.Width / 4, _ Width:=ActiveWindow.Width / 2, _ Top:=ActiveSheet.Rows(ActiveWindow.ScrollRow).Top + _ ActiveWindow.Height / 4, _ Height:=ActiveWindow.Height / 2).Chart With chtChart .ChartType = xlXYScatterLines '' Remove any series created with the chart Do Until .SeriesCollection.Count = 0 .SeriesCollection(1).Delete Loop For iSrsIx = 1 To iDataColsCt - 1 '' Add each series Set srsNew = .SeriesCollection.NewSeries With srsNew .Name = rngDataSource.Cells(1, iSrsIx) .Values = rngDataSource.Cells(2, iDataColsCt) _ .Resize(iDataRowsCt - 1, 1) .XValues = rngDataSource.Cells(2, iSrsIx) _ .Resize(iDataRowsCt - 1, 1) End With Next End With End If End Sub ```

Top of Page

#### Series with Distinct X and Y Values

Usually, each of the series that I end up charting have distinct X and Y values, as illustrated by the data and chart below.

 X1 Y1 X2 Y2 X3 Y3 1 1 2 12 3 7 2 2 3 11 4 7 3 3 4 10 5 7 4 4 5 9 6 7 5 5 6 8 7 7 6 6 7 7 8 7 7 7 8 6 9 7 8 8 9 5 10 7 9 9 10 4 11 7 10 10 11 3 12 7 11 11 12 2 13 7

As in the prior example, you'd have to do lots of cutting and pasting to chart these series manually. But the following simple VBA procedure will construct this chart, as easily as using the Chart Wizard.

 ```Option Explicit Sub MultiXY_Chart() Dim rngDataSource As Range Dim iDataRowsCt As Long Dim iDataColsCt As Integer Dim iSrsIx As Integer Dim chtChart As Chart Dim srsNew As Series If Not TypeName(Selection) = "Range" Then '' Doesn't work if no range is selected MsgBox "Please select a data range and try again.", _ vbExclamation, "No Range Selected" Else Set rngDataSource = Selection With rngDataSource iDataRowsCt = .Rows.Count iDataColsCt = .Columns.Count End With If iDataColsCt Mod 2 > 0 Then MsgBox "Select a range with an EVEN number of columns.", _ vbExclamation, "Select Even Number of Columns" Exit Sub End If '' Create the chart Set chtChart = ActiveSheet.ChartObjects.Add( _ Left:=ActiveSheet.Columns(ActiveWindow.ScrollColumn).Left + _ ActiveWindow.Width / 4, _ Width:=ActiveWindow.Width / 2, _ Top:=ActiveSheet.Rows(ActiveWindow.ScrollRow).Top + _ ActiveWindow.Height / 4, _ Height:=ActiveWindow.Height / 2).Chart With chtChart .ChartType = xlXYScatterLines '' Remove any series created with the chart Do Until .SeriesCollection.Count = 0 .SeriesCollection(1).Delete Loop For iSrsIx = 1 To iDataColsCt - 1 Step 2 '' Add each series Set srsNew = .SeriesCollection.NewSeries With srsNew .Name = rngDataSource.Cells(1, iSrsIx + 1) .Values = rngDataSource.Cells(2, iSrsIx + 1) _ .Resize(iDataRowsCt - 1, 1) .XValues = rngDataSource.Cells(2, iSrsIx) _ .Resize(iDataRowsCt - 1, 1) End With Next End With End If End Sub ```

#### Quick XY Chart Utility

After writing about procedures that can create charts from regular but nonstandard ranges, I built a utility that handles this very task. The utility creates XY charts exclusively, for ten different columnar data arrangements, including the three discussed above. Click on the dialog below to visit the Quick XY Chart Utility page.