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


        XY Trend Chart Animation

This tutorial shows you how to make a dynamic and animated XY trend chart. It is based on the example in Chrt_XY_Trend_Animation.xls.

1. Charting Dynamic Ranges

Dynamic chart names provide a powerful way to automatically update chart ranges as more data is added to your source data table.

In this example we show how to use dynamic chart ranges to animate a chart. We start with time series data in Cols A&B. We next add dynamic range names for the data to be plotted. See cols D & E in data table.

We can create dynamic range names for our plot variables with tools > Name > Define > "Refers to" formulas as follows:

  Plot DT        = Offset(Data!$D$2,0,0,Counta(Data!$D2:$D$10000))

  Plot Value    = Offset(Plot_DT,0,1)

2. Dynamic Chart

Using the Chart Wizard, we can enter our dynamic range names right into the X and Y values definition boxes. Notice that we must enter the sheet name with an "!" or the workbook name with an "!" before the range name.

We now have a dynamic chart that will plot data in the Plot_DT and Plot_Value range names.

Next we need to get source data into these range names so that our plot will show data.



3. Copying Source Data to Dynamic Ranges

We use a simple VBA procedure to copy data from the source range to the Plot_DT and Plot_value dynamic ranges. See the download workbook for the VBA code.



4. Working Example

The working example in Chrt_XY_Trend_Animation.xls allows the user to turn the animation on/off and to generate a time report if desired. The user can clear previous charts and regenerate chart over and over.