Sample Parallel Coordinate Chart
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In the Microsoft Charting newsgroup, a reader wanted to show separate lines on a line chart to compare the severity of the medical conditions of different patients after one to five visits. The question was whether it would be possible to resolve the individual patients.
A parallel coordinates chart can show an individual’s condition in front of a background of the entire population.

Here is a small sample of the data. The first column represents the patient identification number, and the other columns show the patients’ conditions on each visit, on a scale of 0 to 4. I’ve left a few blank rows at the top for extra chart data.

The first step is to chart all the data, by row, and format the lines in the same muted gray.

From the ActiveX Control Toolbox (below), select the List Box button, and draw a listbox. I drew mine next to the chart, the same height as the chart. In Excel 2003, I usually right click on another toolbar and choose Control Toolbox from the pop up menu, although there is also a button on the Visual Basic toolbar that toggles the visibility of the Control Toolbox. In Excel 2007 click on the Developer ribbon tab, choose Insert from the Controls group, and select the List Box from the list of ActiveX Controls.

Right click on the listbox and choose Properties.Select a cell for the LinkedCell property (I used A2) and a range of cells (A5:A35) for the ListFillRange property. You can also set other properties, including appearance, from the Properties window.

The list fill range is the list of patient IDs from column A, plus a blank cell at the bottom, because sometimes the listbox truncates the last item in the list. The linked cell is A2, which contains the selected patient ID. (A Forms toolbar listbox returns the index of the selected item, not the item itself).
Cell A3 has a formula to determine the index of the selected patiend number:
=MATCH(A2,A5:A34,0)
Cells B2 through F2 have simple index formulas to return the condition for the selected patient from that column. Select B2:F2, enter the following formula, and hold CTRL while pressing Enter, to enter the formula in all selected cells.
=INDEX(B$5:B$34,$A$3)
The top of the table, highlighted in blue, shows the data for the selected patient.

This data in A1:F2 is added to the chart, and given a distinct color. I added data labels to the first and last point, showing the patient number.

It’s very easy for the reader to select any patient and view the corresponding severity scores. I used a Controls Toolbox listbox instead of a Forms toolbar one, because after selecting one item, you can easily scroll through the rest using the arrow keys.
I have posted my sample Parallel Coordinates Workbook.
Possibly Related Posts:
- Forms Controls and ActiveX Controls in Excel
- How To: Assign a Macro to an ActiveX Control
- Interactive Multiple Line Chart
- Easier Interactive Multiple Line Chart
- Display One Chart Dynamically and Interactively
- How To: Assign a Macro to a Toolbar or Menu
- A Linked Table to Sort Data for Charting
- Gas Prices – Interactive Time Series
- AutoFilter Tricks
- Interactive Charts with Checkboxes and Formulas
Posted: Tuesday, September 9th, 2008 under Chart Types.
Comments: 2
Comments
Comment from derek
Time: Wednesday, September 10, 2008, 4:17 am
It might be worth jittering the data a bit to avoid the lines all falling on top of one another:
=B5+0.1*NORMSINV(RAND())
Comment from Jon Peltier
Time: Wednesday, September 10, 2008, 6:31 am
Derek -
That helps to show the amount of data for each line:

















Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.