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