|
This tutorial is one of two on this Site that show you how to build a series of XY trend charts that are aligned vertically so that you can compare trends between several parameters at the same time. This page shows a data transformation technique and is based on the example found in Stacked_Trend_Chart_transformed_data.zip Vertical Panel Trend Chart
Parallel XY Trend Chart presents a similar approach for producing parallel XY charts.
1. Stacked Trend Charts - Several Approaches
In many situations, it is useful to compare trends of several parameters at the same time to see how they vary over time, are they parallel, inverse or random. For 2 or even 3 parameters, it is possible to include them on a single chart. If the scales vary, you can add a second y axis.
What do we do when we want to compare 6 - 8 variables at the same time? In this case, we want to build a series of aligned charts so that we can compare trends between our parameters.
Jon Peltier has a good discussion on how to build stacked line charts on his site.
What about stacked XY charts? For XY charts, we have three ways to build stacked charts:
1) Align embedded XY charts manually or programmatically
2) Place embedded charts in a cell so that Excel will automatically handle chart alignment.
3) Transform data and place all series in a single chart, use dummy Y axis series to provide labels for each chart series.
This page and related workbook provide information on Method 3, transforming data and dummy Y axis label series. Go to this page for Method 2.
2. Transformed Data Approach
Jon Peltier presented an example of this technique in his advanced charting class at the Excel User's Forum in Atlantic City on April 19, 2006.
Jon's idea is to transform the Y values of each data series to fit into a particularr chart value range and to then use a dummy series to label the Y axis with values for each series. The data is plotted correctly and labeled appropriately.
3. Example
For this example, we will use US oil consumption trend data for the period 1949 - 2004 (EIA - Historical Energy Information and Statistics). Organization of our data to support stacked trend charting can make the task much easier.
Columns A:E include the original data, in this case, million barrels/day of oil use in USA by sector. Columns H:K include the transformed data. The transformation factors are specified in the range H2:K7. The User requested gap between data series is specified in range H8. The annual transformed values by sector use the basic formula outlined in the Data Transformation Concept graphic above to provide the proper Y axis position for each data point.
The Y axis dummy series is specified in Columns M:O. The horizontal solid line separator for each series is provided in columns Q:S. The final chart also shows the first and last data points for each series.
Review of the example workbook should provide a good start for making your own stacked trend charts.
|