Chart an expanding range in all versions of excel
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? û Theresa Phelps A When you select a chart series in Excel 97 the ranges used by the series are outlined on the worksheet. You could simply drag a corner of the outline to extend the range. But here's another approach for all versions of Excel. Instead of defining a range with cell addresses, use a formula. To see how it works, follow these steps. 2. Select InsertûNameûDefine to bring up the Define Name dialogue box. 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) Click Add. 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. Since Column A has a heading in Row 1, the formula subtracts 1 from the number. 3. Now type Sales in Names in workbook, and in Refers to enter: =offset(Sheet1!$B$2,0,0,counta (Sheet1!$B.$B)-1) Click Add, and then OK to close the dialogue box. 4. Activate the chart and select the data series. In this example, the formula in the formula bar will read: =series(Sheet1!$B$1,Sheet1!$A$2.$A$10, Sheet1!$B$2.$B$10,1) Replace the range references with the names you defined in Steps 2 and 3. The formula should read: =series(,Sheet1!Date,Sheet1!Sales,1) When you add data to Columns A and B, the chart will be updated. 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. û John Walkenbach
Formulas define the date and sales ranges so the chart is automatically updated when you add new data in Excel
Category: spreadsheet
Issue: May 1998