Create an Excel chart that automatically updates with new data

Are you constantly changing your Excel chart's data range as new information becomes available? Mary Ann Richardson shows how to set up the chart to automatically update as you add new rows of data to your spreadsheet.

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)`
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.