Update Charts Automatically When You Enter New Data

Category: Charts & Graphics | [Item URL]

Q. I use an Excel workbook to track and chart daily sales. Since the number of data points changes every day, I have to update the chart manually so it includes the new data. How do I get the chart range to expand automatically?

When you select a chart series in Excel 97 or later, the ranges used by the series are outlined on the worksheet. You could simply drag a corner of the outline to extend the range.

This document describes another approach that uses formulas to define the ranges used in a chart. The steps listed below describe how to create dynamic ranges for the chart shown in figure below.

1. Enter the data and create the chart shown in the figure.
2. Select Insert, Name, Define to bring up the Define Name dialog box.
3. In the 'Names in workbook' field, enter Date. In the 'Refers to' field, enter this formula:
   =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1! $A:$A)-1)
4. Click Add to create the name. Notice that the OFFSET function refers to the first data point (cell A2) and uses the COUNTA function to get the number of data points in the column. Because column A has a heading in row 1, the formula subtracts 1 from the number.
5. Now type Sales in 'Names in workbook', and in 'Refers to' enter this formula:
   =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)
6. Click Add, and then OK to close the dialog box.
7. Activate the chart and select the data series. In this example, the (unmodified) formula in the formula bar will read:
   =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$10, Sheet1!$B$2:$B$10,1)
8. Replace the range references in the SERIES formula with the names you defined in steps 4 and 5. The SERIES formula should read:
   =SERIES(,Sheet1!Date,Sheet1!Sales,1)

After performing these steps, you'll find that the chart updates automatically when you add new data to the worksheet.

To use this technique for your own data, make sure that the first argument for the OFFSET function refers to the first data point, and that the argument for COUNTA refers to the entire column of data. Also, if the columns used for the data contain any other entries, COUNTA will return an incorrect value.