Create an Excel chart that automatically updates with new data

If you add a row
or two of data to your spreadsheet, you can easily update the Excel chart
pertaining to that data by changing the chart’s data range. However, if you
find yourself continually changing the data range, you may want to set up the
chart so that it automatically updates as new data is added.

Suppose you are
keeping track of the maximum temperature for each day in April. You have
entered the first two dates for April in Column A, which contains the field
name Date in A1. The corresponding temperature for each day will appear in
Column B, which contains the field name Temperature in B1. Thus, you enter 55
in B2 for 4/1/06
in A2, and 60 in B3 for 4/2/06
in A3. You select A1:B3, and create a chart for Maximum Daily Temperatures for
April. To have the chart update automatically as each new row is added daily,
follow these steps:

1. Go
   to Insert | Name| Define.
2. Enter
   Date in the Names In Workbook text box.
3. Enter
   the following formula in the Refers to text box:

   =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)

4. Click
   Add.
5. Enter
   Temperature in the Names In Workbook text box.
6. Enter
   the following formula in the Refers To text box:

   =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)

7. Click
   Add and then OK.
8. Click
   on the chart, and then on the data series.
9. Change
   the formula in the formula bar to the following:

   =SERIES(,Sheet1!Date,Sheet1!Temperature,1)

The chart will
update automatically each day with a new temperature. Be sure you don’t use
Columns A and B for any other data; otherwise, COUNTA will return an incorrect
value.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.