|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
Next we need to get source data into these range names so that our plot will
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
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.