Update Charts Automatically

Update charts automatically when you enter new data

When you select a chart series, 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 A3) 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$3,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$3:$A$10, Sheet1!$B$3:$B$8,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.

Related posts:

1. Useful Tips in Excel
2. Update your Blog Directly from Facebook.
3. Separate full names into different columns
4. Moving Between Precedent and Dependent Cells
5. Combining Text and Values in one cell
6. How to create secure and strong password?
7. Firefox Tips – Automatically Close the Download Manager When Downloads Complete.
8. Automatically terminate “Not Responding” programs in Windows
9. Get personalized music recommendations automatically
10. How to Retouch your Photos Automatically