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.
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.
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.
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.
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.
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.
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.
Peltier Technical Services, Inc., Copyright © 207. All rights reserved.