Chart Gallery
Video Tutorials
Trend Analysis
Using R
Data Visualization
 Global Warming
Map Excel Data with Google Earth
Chart Doctor
Advanced Chart Techniques
What's New
 Search Site RSS
   04/4/2008 4:30 PM


        Horizontal Panel Trend Chart  - Alternative to Stacked Column Chart

Click here for Worksheet demonstrating development of a Horizontal Panel Chart using  data transformation and X axis dummy series techniques.

Here's a comparison of a stacked column chart and a horizontal panel chart for the same data.

                             Stacked Column Chart                                                                              Horizontal Panel Chart









Why Not Use a Stacked Column Chart?

Cleveland and McGill (1984) conducted research on people's ability to decode data from  graphs (reference link). Cleveland has ranked graph viewer's abilities to decode graphed data as follows:

  1. (Best) Position on a common scale / axis

  2. Position on identical non-aligned scales / axes

  3. Length

  4. Angle

  5. Slope

  6. Area

  7. Volume

  8. Density

  9. Color saturation

  10. (Worst) Color hue

This means that graph viewer's best data visualization ability is to interpret data shown on a common axis, next best is to use identical non-aligned  scale/ axes. Decoding length is the third best method. Stacked bar charts show the position of the total bar and first bar component as positions on a common scale/ axis, however,  they require  viewers to interpret the length of the internal bar elements.  Horizontal panel chart shows the data on a series of parallel panels with identical non-aligned scale/axes, making them easier for a viewer to interpret.

Using Dummy Axis to Make Horizontal Panel Plots

Users can construct Horizontal  Panel charts by using an XY chart with a constant Y axis and set of similar offset dummy x axes, one dummy axis for each data series. The offset allows the individual series to be plotted in parallel panels with a repeating x axis scale and axis labels.

This technique requires a set of data transformations so that the original data can be plotted properly on the transformed X axis. The horizontal axis transformation concept is shown conceptually below. 

Making Horizontal panel Chart

1. Organize Data Table: We need to organize our data to support our data transformation steps. the original data was in the form of a data table arranged by year and Data series.  This data was converted to a vertical data base with a simple VBA macros included in the workbook.

2. Create Horizontal Data Control Sheet - To standardize and expedite my panel chart development,  I use a Hor_Data_Control  worksheet that includes all of the information that is needed to develop the horizontal panel chart.  

I use a VBA macro to transform the vertical database data to the Hor_Data_Control  sheet format. The horizontal panel chart is automatically updated when changes are made to the Hor_Data_Control sheet.

Click here for Worksheet demonstrating development of a Horizontal Panel Chart using  X axis dummy series.