Horizontal Panel 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:

(Best) Position on a common scale / axis
Position on identical non-aligned scales / axes
Length
Angle
Slope
Area
Volume
Density
Color saturation
(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.

Horizontal Panel Trend Chart - Alternative to Stacked Column Chart