Charting Passenger Volume with a Stacked Area Chart

The Problem

A reader of the Microsoft charting newsgroup (see sidebar) wanted a special chart to plot customer volume on a coach route with four stops. The table below shows his data, as percentages of all riders, with the rows signifying the stop where a rider boarded the coach and the columns signifying where the rider got off. For example, 1% of the passengers got on at Centre and got off at C. Park, while 12% got on at Centre but rode to LGW.

The type of chart the user wanted was a horizontal bar chart, with bars showing the distance traveled from left to right. Rather than plotting simple categories on the vertical axis, the user wanted the height of each bar to represent the percentage of passengers who traveled between each pair of stops.

It is not a difficult exercise to construct a floating bar chart to show the different legs of the trip, as shown below:

Floating Bar Chart

The challenging part is depicting the percentages to change the bar height, since the bars of a standard Excel bar chart cannot be independently changed. There are workarounds, of course-I describe or link to several techniques to produce variable width column charts on two pages in my web site, Column Chart with Variable Width Columns and Variable Width Column Charts. The method I plan to use for this example is derived from my recent Matrix Chart article in Tech Trax; it may be helpful to reread this article to help understand this technique. Half of the secret of using Excel is knowing how to work around its inadequacies and defaults, and fortunately Excel has enough inherent flexibility to enable these tricks.

The Data

When creating charts in any charting program, or by hand for that matter, the most important step is to properly arrange your data. This is especially true when stretching the limits of a charting tool.

I created a range with a header row and a header column. There is one row per visible bar in the chart ("C-CPark", "C-LGW", etc.), and I inserted rows above the first data row, below the last data row, and between each pair of data rows, to allow for a thin gap between adjacent bars in the chart (the height of the gap was arbitrarily set to 3 percentage points). These are labeled "blank" in the table, denoting the blank areas in the chart. The header row consists of the numbers 1 through 4 for the four stops, with the values between the endpoints (i.e., 2 and 3) listed twice to produce the vertical ends of the bars. The top left cell in this range is left blank, to help Excel detect the header row and column.

Creating the Chart

To start the creative process, select the data range and start the Chart Wizard. In Step 1, select a Stacked Area type, then shuffle through the rest of the wizard. In step two, make sure to select Series in Rows. When you get to Chart Options in step 3, click on the Gridlines tab and uncheck all the options.

Stacked Area Chart

Clean up the chart by double clicking the Plot Area (the gray area at the top, above all the charted series), and changing the border to black and the fill to white.

Cleaned Up Plot Area

Change the category axis to a time-scale axis as follows. Choose Chart Options from the Chart menu, and click on the Axes tab. You'll see the following dialog.

Automatic (Category) Axis Setting

Change the Category (X) Axis option from Automatic to Time-Scale. Notice that the thumbnail of the chart updates, as the axis tick labels change to dates and the sloped edges between colored areas become vertical.

Time Scale Axis Setting

Click OK, and the transformation applied to the thumbnail chart in the dialog is now applied to the chart itself.

Time Scale Axis

Now it's time to add another series to the chart. This is an invisible helper series, which will be converted into an XY Scatter series; data labels on its invisible points will replace the dates currently used as category axis labels.

Create a data range like the following. The first row has the labels which will ultimately adorn the points. The top row of the series data includes a blank cell plus X values corresponding to the 1 through 4 in the header row of our main data table. The second row includes a label "Axis" which will become the series name, plus the four zeros as Y values, since the points will be located on the X axis (i.e., Y=0).

Copy the series data (the bottom two rows) of this new range, select the chart, choose Paste Special from the Edit menu, and apply the settings shown here:

Paste Special Dialog

The chart (below) doesn't appear to be changed by this, but notice the new entry in the legend, for "Axis". This new series is another area series stacked on the others, but with zero value, there is nothing to see.

"Axis" Series Added

Select this new series to convert it to an XY Scatter series. You cannot directly select the series with the mouse; you can't even see it. But you can select a different series, then use the up and down arrow keys until the desired series is selected. Perhaps a more straightforward selection technique is to select the series from the Chart Objects dropdown in the Chart command bar. (An idiosyncrasy of the Chart Objects dropdown is that if there are multiple series with the same name, like "blank" in our chart, no matter which one you select from the list, Excel will select the first one in the chart.)

Chart Command Bar

Now that the "Axis" series is selected, choose Chart Type from the Chart menu, and select an XY Scatter type as shown.

XY Scatter Chart Type

The "Axis" series now appears along the bottom of the chart; I've changed its markers here to red squares for visibility. Excel has also "helpfully" added secondary X and Y axes, which we don't need.

"Axis" Series Converted to Scatter

Choose Chart Options from the Chart menu and click on the Axes tab. In the dialog, uncheck the secondary axis options.

Secondary Axis Settings

Click OK, the unneeded secondary axes vanish, and the "Axis" series markers now line up with the primary axis tick marks.

Secondary Axes Removed

Double click the horizontal axis, and click on the Patterns tab of the dialog that pops up. Select the None option for Tick Mark Labels, to remove the date labels from the axis. The plot area of the chart expands downward to fill the space vacated by the labels; select the plot area and drag its bottom edge upward to recapture this space for the labels that will be applied next.

Axis Labels Removed

The chart is now ready to accept new axis labels. Excel's built in data labels options are limited, and Excel doesn't have the native ability to apply data labels to a series from another range in the worksheet. You could apply the default Labels or Values data labels, then select each label individually to change its text to the desired custom label. There are several third party utilities that do this for you, saving time and effort. Two of the best are free: Rob Bovey's Chart Labeler (http://appspro.com) and John Walkenbach's Chart Tools (http://j-walk.com). Each instals easily and integrates seamlessly into Excel's interface. In this example I use Rob's Chart Labeler.

With the Chart Labeler installed, select the chart, and at the bottom of the Tools menu, select Chart Labels, then Add Chart Labels. Select the series to label ("Axis"), with your mouse choose the range containing the labels, then assign the Below position for the labels, so they appear below the data points.

Chart Labeler's Add Labels Dialog

The newly added series data labels are indistinguishable from "true" axis tick labels.

Chart with Added Axis Labels

The chart is almost ready. Double click on the "Axis" series, and on the Patterns tab, choose None for Markers and for Lines, to hide the series. Double click on one of the "blank" series, and on the Patterns tab, choose None for border and None for Area, to hide this series. Select the next "blank" series, and press the F4 function key, which is a shortcut for "Repeat last action". Repeat the select+F4 process with the rest of the "blank" series.

Clean Up Underway

Double click the Y axis. On the Scale tab, choose 0 for Minimum and 121 for Maximum (the topmost edge of the highest "blank" series) and keep the Auto checkboxes unchecked; switch to the Patterns tab, and check None wherever possible: Major Tick Marks, Minor Tick Marks, Tick Mark Labels, and Line. Delete the legend. Select each remaining visible series in turn, choose a nicer color for the Area, and check None for Border.

Finished Variable Height Floating Bar Chart

Variations

In this version of the chart, the border of the plot area has been removed to focus attention on the charted data.

Cleaner Chart Without Plot Area Border

The next version chart has no gap between adjacent bars. To improve the visibility of the lowest bar, a single gap has been introduced, between the bottom bar and the X axis. The colors of the bottom two bars should be adjusted so they don't blend together. This chart shows that the gap probably increases clarity of the chart.

Gaps Removed Between Adjacent Bars

Since the X axis is a time-scale axis, you can represent the actual distance between the stops. Suppose the C. Park stop is one mile from the Centre stop, LGW is three miles beyond that (4 miles from Centre), and LHR is two miles beyond that (6 miles from Centre). Replace the arbitrary {1, 2, 3, 4} values in the main data range and in the Axis series data range with {0, 1, 4, 6}, and the axis will have proportionally spaced labels. The original X axis tick marks were hidden (double click on the axis, set Major Tick Marks to None on the Patterns tab), and the "Axis" markers were changed to black crosses.

Proportionally Spaced Stops Along X Axis

The added information from the proportional X axis leads to new insights. Perhaps ridership between the first two stops is so low because they are close enough to walk between.