Tip #2000-3: Make your Excel charts update dynamically
Is there a way to make the chart include new live data automatically
without having to edit the chart each time?
You can create dynamic names
for the data you are charting, and then use those names for the
chart series. A dynamic name is a name that remains constant while
the range of cells it represents changes. By using a dynamic name,
every time new data comes in, the chart will automatically expand
to include it.
The steps are:
Define range names Make the range names dynamic with Excel's OFFSET and COUNT functions Use the dynamic names for your chart
As an example, we prepared a worksheet showing the date and yield
for the US 30-Year Treasury Bond. (Reuters users can download a
sample sheet at left.) Approximately a year's worth of data is listed
in chronological order. The chart displays the daily movement of
the yield over the past year. When we created the chart, we assigned
dynamic names to the series.
When new data is added, the chart series expands automatically to
reflect the addition of the new data. Show
me the sheet.
The first step is to assign the names Cdate
and Yield to cells A2 and B2.
To assign a name to a cell, first select the cell (in this case
A2).
 |
Click once in the Names box to
the left of the formula bar. (The Names box should display the
active cell: A2.) Type the name Cdate and press Enter. |
Repeat this procedure with cell B2 to name the cell Yield. These
names will act as anchors when we use Excel's Offset function
to create dynamic ranges below them.
We'll assign the name ChartDate to refer to the range of cells
that contains data below the Cdate (cell A2), and Yld30
as the name to refer to the range of cells that contains data
below Yield (cell B2).
First,
select Insert Name Define... from
the Excel menu. You'll see the two names you just created in
the box. Show me the Define Name dialogue box.
Type ChartDate in the Names in WorkBook: edit box.
Then click the Refers to: edit box and type
|
=OFFSET(Cdate,1,0):OFFSET(Cdate,COUNT($A:$A),0)
|
Click the Add button to add the name to the list, and
then click OK for the changes take effect. What should the dialog box look like now?
Now, how does it work?
Rather than having these names refer to a fixed range of cells, we
defined them with the Offset and Count functions to refer to
a range of cells that will expand or contract as data is added
or removed from the range. More details
on how the Count function works.
To make our chart update automatically, we need to edit the chart
series to refer to our dynamic names: ChartDate, representing
the date values in column A, and Yld30, representing the yield
data in column C.
To edit the chart, double-click the line in the chart. Show
me
The chart series formula appears in the formula bar. In our case
we changed the formula from
|
=SERIES(,'30YRBND'!$A$3:$A$265,'30YRBND'!$B$3:$B$265,1)
|
to:
|
=SERIES(,Tbond.xls!ChartDate,Tbond.xls!Yld30,1)
|
In the first formula, the range reference is preceded by the worksheet
name (30YRBND) followed by an exclamation point (!). In the
second formula, the names we created are preceded by the work
book name (Tbond.xls) followed by an exclamation point
(!). We found that, when editing the chart series, all you need
to do is to replace the absolute cell references with the dynamic
names you created. Excel changes the worksheet name to the workbook
name automatically.
If you've created your dynamic range names but haven't yet created
the chart, select Chart from the Insert
menu to launch Excel's Chart wizard. When the wizard asks you
to define the range in Step 1 of 5, enter =ChartDate:Yld30 in
the Range: edit box. Complete the remaining steps of
the Chart wizard to create your chart. Show me
It's worth noting that dynamic ranges are not visible in the Names
box next to your formula. If you were to click the down arrow
in the Names box, you would only see a list of the names
that refer to absolute references. Because they are not visible
in the Names box, dynamic names cannot easily be selected when
working in Excel. However, you can select dynamic ranges from
within a macro.
|
