Horizontal Panel Trend Charts Excel

Small Multiples - Trellis Like Plots in Excel

Data visualization researchers have similar advice when plotting multivariate data: Tufte and Few recommend "small multiples", Cleveland and Robbins  recommend "Trellis Displays". Advanced statistical and graphical analysis systems like R and S-Plus include Trellis display capabilities.

Excel users can develop small multiple or single vector trellis like displays using Excel charting, data transformations and dummy axis techniques explained in this site.

The chart on the right shows the market share of US car sales from 1970 to 2005 for Detroit's Big 3, 2 Japanese and all others combined automakers. The 6 data series are presented in parallel plots on a single chart. This emphasizes the market share trends and makes the chart much more effective than Excels default of putting all 6 series in one plot.

The tutorial and workbook download  show you how to develop parallel plots this this example using a small multiple - trellis display like approach in Excel.

The Stacked XY Chart - Transformed Data page shows how to build a series of plots vertically using a similar data transformation technique and a series of dummy axis.

Data Transformation Concept

The basic idea is to transform the X values of each data series to fit into a particular chart value range and to then use a dummy series to label the X axis with values for each series. The data is plotted correctly and labeled appropriately.

Example

For this example, we will use US Car Sales market share data for the 1970 - 2005 period. Organization of our data to support parallel XY plots can make the task much easier.

Overall chart titles and notes are added in D2:D5.  The number of data series and number of data points per series are entered in L1:L2.The gap between plots is added in L3.

The panel offsets and series label data is handled in A9:F15. The data to be plotted is in M9:P56. The dummy X axis series data is in I9:K49.  The chart top border is a horizontal line, defined in A18:B19.

The vertical horizontal solid line separators for each series are Y error bars added series beginning position. 

Review of the example workbook should provide a good start for making your own stacked trend charts.