Last edit: 05-08-30 Graham Wideman
|XLChartMatic Part 1: A Bit About Excel Chart
Article created: 2005-03-04
I'll not try to make this a tutorial on Excel Chart, but there are a few features of Chart that are particularly salient to point out.
(By the way, this entire article is about Excel's Chart feature, herein called "Chart", and not about the similar but separate program Microsoft Chart. XLChartMatic doesn't work with MS Chart).
One slight misdirection in Excel is over the naming of the various kinds of chart that might be useful for plotting the relationship between Y and X measurements of something: gas mileage versus odometer reading; temperature encountered by climbers versus height on mountain; price of computers versus percentage ownership and so on.
Typically, you collect a bunch of such measurements, plot the points and connect the dots... with lines. The fact that Excel offers a "Line" chart suggests that this is the kind of chart you want for that kind of job. Turns out that's true, but only if your points are evenly spaced along the X axis. If they are not, you will need one of the variants of the XY Scatter chart that includes lines.
Even though you start the charting process by selecting a particular chart type, in fact this is not much of a commitment -- you can change chart type at any later stage. And many of the variants in the spectrum of chart types that the Chart Wizard offers are the same chart type with different lower-level options chosen.
For example it's particularly helpful to know that you don't have to agonize at the start over whether you're choosing XY Scatter with curved lines versus straight lines. Individual data series have a setting for smoothed lines (on/off), or you can select the whole chart and flip all the smoothed-line settings at once by revisiting the Chart Types dialog and selecting the type with smoothed or straight lines.
Of course, if you flip to a chart type that needs different data than what you've already specified, you will need to fill in those blanks. (Sometimes Chart will try to guess... but often badly.)
Potential Gotcha: If you cavalierly flip to a chart type which involves Chart forgetting data for some aspect (such as flipping from "with Lines" to "without Lines"), then when you return to the earlier chart type that aspect of the Chart will likely be reset to "Automatic".
Any data to be plotted in Chart is provided to Chart in terms of one or more "Series' " of data. Amongst other things, a Series specifies a list of Y values (or simply "values"). Typically this list of values is specified as a range of cells, but if you want you can provide a simple list of values.
When you are ready to provide or edit the specification for a Series, you will interact with the Source Data dialog, which you invoke using right-click (on various parts of a chart) > Source Data. On the Series Tab panel, enter a spec for a series of cells (or list of values) in the Y Values slot.
In normal use, the user must manually select these various ranges of data, either by typing in cell range formulas, or by dragging rectangles around cells. Needless to say, impractical for anything but a handful of series.
Chart's facilities for formatting elements of a chart are considerable, but may seem complex or mysterious to some users. In general, Chart remembers format settings at the level of a data Series, and also at the level of individual Points. For a series whose markers and lines should look consistent (colors, line format, choice of marker symbol and size etc), you would usually set the formatting at the Series level, and that causes all points in that series to look the same. However, you can select individual points and set some or all of their formatting to be different if you wish.
Adding somewhat further confusion, there are so many different parts to a chart that users may have trouble knowing what things they can click on, and what gets selected when. For example, note the subtle distinctions when you want to edit a series:
|Click sequence (on marker)||Or equivalently...||What happens|
|Click on marker||Click on line||Selects series|
|Click on marker, pause, click again||Click on line, pause, click again||Selects point [See ALERT below!]|
|Double-click on marker||Double-click on line||Opens window "Format Data Series" dialog|
|Click on marker, pause, double-click||Click on line, pause, double-click||Selects point then opens "Format Data Point" dialog.|
ALERT: When you have selected a point you can actually drag the point around. Doing so updates the data in the cell that supplies data for this point. This may well be rather a surprise!
Go to: or up to XLChartMatic for Microsoft Excel