gw_logo_08.gif (1982 bytes) 
Last edit: 05-08-30 Graham Wideman
Excel
XLChartMatic Part 2: Organizing Data for Large Numbers of Points and XLChartMatic
Article created: 2005-03-04

Arranging Data

In this section I describe an arrangement of data that will be easy to generate from a database, and which XLChartMatic will be able to wrangle for Chart. I'll use an XY Scatter chart as an example, but this applies also to Line, Bubble and other charts.

Running Example Data

In this and following pages I'll use a contrived example involving data for subjects in a dieting experiment.  The experimenter hypothesizes that gender and eye-color are factors influencing weight loss.  Weight measurements were taken when each subject enters the program, and at several time points thereafter.  Sample data looks as follows:

Subjects Measures
SubjectId Gender EyeColor
S001 M Brown
S002 M Brown
S003 M Blue
S004 F Blue
S005 F Green
S006 F Green
MeasureId SubjectId Time_mo Weight_lb
32451 S001 0 231
45213 S001 5.5 205
76945 S001 9.3 192
23654 S002 0 162
94674 S002 4.7 142
56934 S002 8.2 153
90836 S003 0 180
93763 S003 7.5 177
19863 S004 0 168
41043 S004 4.6 168
24386 S004 9.2 153
62465 S004 12.9 158
12563 S005 0 137
87345 S005 8.3 129
77923 S006 0 135
75346 S006 3.6 126
32798 S006 9.7 114
34764 S006 12.6 105

So the task is to plot Weight vs Time for each Subject, connecting each subject's points with lines. In Excel Chart, the most obvious way to do this is to assign each subject to a data series. The chart below shows the approximate result we're looking for:

Obviously this sample data is not very extensive, but the ideas here do extend to hundreds of data series and thousands of points.

The "Traditional" Manual Data Arrangement

First, I've noticed that many descriptions for setting up an XY Scatter Chart with more than one series suggest the following arrangement of data in Excel:

One pair of columns for each series. Here Series S001 is selected, with the purple box outlining the X values, and the blue box around the Y values. For chart, you specify ranges  using...

... the Source Data dialog Series tab ... with the X Value and Y Value slots.

The Database Friendly Arrangement

From the Excel point of view, one arrangement of data is as good as another, as these rectangles of data have to be specified by hand. But in terms of arranging data from some other source, such as a database, this is a relatively difficult layout to accomplish automatically.

Instead, we would prefer to lay out data with one row per point, as that's very easy to generate using a database query. Here, for example, is how to do it in Access, starting with the two tables shown above:

.. which produces a datasheet view that can be pasted into Excel where it looks like the following...

Now if only we could select those X and Y values for Chart automatically...

Tips for copying data from Access to Excel:

1. Selecting Data for Copying. When viewing data in Access's datasheet view (either table or query) you can make the following selections quickly:
Several rows:  Position the mouse over leftmost grey column to get horizontal arrow cursor, then press-drag up or down down.
Several columns: Position mouse in grey column head to get vertical arrow cursor, then press-drag across.
Entire table: Click on empty grey cell in top left corner.

2. Paste-to-Excel Gotcha: When pasting tables of data from Access to Excel, use Excel's Paste Special  > Text method. If instead you use the default Paste method, then between Access and Excel, they manage to paste numbers so that they appear in Excel formatted as text. The result will look fine, but Chart won't understand numbers that are stored in cells as text. (Access and Excel 2002).

Yes, this is very counter-intuitive -- that you should Paste Special using Text format to AVOID storing the numbers in Text format in Excel. Sigh. The "rational explanation" is that the default paste method supplies the data to Excel with pretty formatting instuctions forcing Excel to treat the incoming data as strings. By contrast, the plain Text paste method supplies only the text of the data, and lets Excel decide what kind of cell format would be appropriate -- Excel correctly deduces that a series of digits is a number.

XLChartMatic's Role

The Basics

XLChartMatic's core job is to read data such as that shown above in "easy-for-databases" arrangement, figure out where the data rectangles should be (in this case using the SubjectId column to tell one series from another), and tell that to Excel Chart.

To instruct XLChartMatic exactly where the relevant columns are, we add an additional row of column heads.

The details of the added column heads layout will be explained in a subsequent page, but you can see that the heads pt_Series, pt_X and pt_Y tell XLChartMatic all it needs to know for the job.

Setting Chart Series Formatting Too

XLChartMatic is also able to read some additional columns containing formatting information for each point. Generating these from the database is quite straightforward. We can for example create a table telling how to translate the subject variables Gender and EyeColor into formatting:

SubjectVarsToChart

Gender EyeColor Symbol ForeColor BackColor LineColor
F Blue Circle 1 8 8
F Brown Circle 1 9 9
F Green Circle 1 4 4
M Blue Square 1 8 8
M Brown Square 1 9 9
M Green Square 1 4 4

... which we can then use in an extension of the previous query:

... to provide the following data in Excel:

Again, details on the chart attribute columns are on a subsequent page, but as a preview, XLChartMatic knows how to set a variety of useful Series and Point formatting.


Go to:  gw_logo_08.gif (1982 bytes) or up to XLChartMatic for Microsoft Excel