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


        Stacked XY Charts in Cells

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 uses a chart in a cell technique and is based on the example found in Stacked_Trend_Chrt.xls.

1. Stacked Trend Charts

In many process operations, 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 's 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 2. Go here for Method 3.

 2. Stacked XY Charts in Cells

The stacked chart in the example workbook shows how to stack 8 trend charts from a data set that represents real time turbidity readings in 15 minutes intervals from 8 instruments. In this case, we want to see how the turbidity varies among the treatment units. The example builds on the Chart in A Cell   module. The example also includes a userform to allow user to select chart time period which is a follow-on to the discussion in the Control  X Axis Min - Max Values    module.

The screen to the right shows 2 of the 8 charts in our example. Notice that chart1 is set in cell B2, chart 2 is set in cell B3, etc.

If we decide to shrink or expand column B, all charts will be adjusted accordingly.

3. Limitations to Stacked XY charts in Cells

This example works well, however, there are two limitations to this approach which  may require some work-around:

  1. Y Axis Labels number of characters must be the same for all charts
  2.  X Axis labels must be shown for each chart

If you need to chart data with different number formats, you can solve the alignment issue by adjusting the number of decimals for each chart so that all charts have the same number of characters in the Y Axis Labels.

If you want to remove the X Axis labels for all charts except last chart you can do this by creating an extra chart which only shows the X Axis. This X axis label chart should not be set to the a single cell, rather it should be manually adjusted so that it aligns with the cell charts.