XL: Using Defined Names to Automatically Update a Chart Range
ID: Q183446
|
The information in this article applies to:
-
Microsoft Excel 97 for Windows
-
Microsoft Excel for Windows 95, version 7.0
-
Microsoft Excel for Windows, version 5.0
SUMMARY
To set up a chart that is automatically updated as you add new information
to an existing chart range, create defined names that dynamically change
as you add or remove data.
MORE INFORMATION
This section includes two methods for using defined names to automatically
update the chart range.
NOTE: The methods in this section assume there are no more than 200 rows
of data. You can revise the defined names so that they use the appropriate
number and reflect the maximum number of rows.
Method 1: Use OFFSET with a Defined Name
To use this method, follow these steps:
- In a new worksheet, type the following data:
A1: Month B1: Sales
A2: Jan B2: 10
A3: Feb B3: 20
A4: Mar B4: 30
- Point to Name on the Insert menu and click Define.
- In the Names in workbook box, type Date.
- In the Refers To box, type:
=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)
- Click Add.
- In the Names in workbook box, type Sales.
- In the Refers To box, type:
=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)
- Clear cell B2, and then type the following formula:
=RAND()*0+10
NOTE: This formula, which uses the volatile RAND function,
automatically update the OFFSET formula that is used in the defined
name "Sales" when you enter new data into column B. The value 10,
which is used in this formula, is the original value of cell B2.
- Select $A$1:$B$4.
- Create the chart and add the defined names in the chart.
If you are using Excel 97, follow these steps:
- Click Chart on the Insert menu to start the Chart Wizard.
- Click a chart type and click Next.
- Click the Series tab. In the Series list, click Sales.
- In the Category (X) Axis Labels box, replace the cell reference
with the defined name Date.
For example, the formula might be similar to the following:
=Sheet1!Date
- In the Values box, replace the cell reference with the defined
name Sales.
For example, the formula might be similar to the following:
=Sheet1!Sales
- Click Next.
- Make any changes you want in step 3 of the Chart Wizard and click
Next.
- Specify the chart location and click Finish.
If you are using Excel 5.0 or Excel 7.0, follow these steps:
- Point to Chart on the Insert menu and click As New Sheet to start
the Chart Wizard.
- Click Next.
- Click a chart type and click Next.
- Click a chart subtype and click Next.
- Click Columns for Data Series In and type 1 for Use First 1
Columns for Category (x) Axis Labels. Click Next.
- Click the titles you want to display and click Finish.
The chart appears on a new chart.
- Select the data series. Click Select Data Series on the Format
menu.
- Click the X Values tab. In the X Values box, replace the cell
reference with the defined name Date.
For example, the formula might be similar to the following:
=Sheet1!Date
- Click the Name And Values tab. In the Y Values box, replace the
cell reference with the defined name Sales.
For example, the formula might be similar to the following:
=Sheet1!Sales
- Click OK.
Method 2: Use a Database, OFFSET, and Defined Names
You can also define your data as a database and create defined names for
each chart data series.
To use this method, follow these steps:
- In a new worksheet, type the following data:
A1: Month B1: Sales
A2: Jan B2: 10
A3: Feb B3: 20
A4: Mar B4: 30
- Select the range from A1 to B4 and click Set Database on the Data
menu.
- On the Formula menu, click Define Name.
- In the Name box, type Date.
- In the Refers To box, type:
=OFFSET(Database,1,0,ROWS(Database)-1,1)
- Click Add.
- In the Name box, type Sales.
- In the Refers To box, type:
=OFFSET(Database,1,1,ROWS(Database)-1,1)
- Select $A$1:$B$4
- Repeat step 10 from Method 1 to create the chart and add the defined
names to the chart.
As long as the data that you want to appear in your chart is defined as a
database, the chart is updated automatically as you add new data.
NOTE: If you are creating a series chart that plots every value in an
adjacent block of cells in single column, and the block of cells starts
with the first row, you can use either of the following formulas in the
Refers To box for the defined name:
=INDIRECT("Sheet1!$a$1:$a"&COUNT(Sheet1!$A:$A))</FIXEDTEXT>
-or-
=Sheet1!$A$1:OFFSET(Sheet1!$A$1,COUNT(Sheet1!$A:$A),0)
To use a block of cells that start with a cell on a row other than the
first row, reference that row in the first cell reference and add the
starting row number to the count to find the last row number. To plot
adjacent nonnumeric entries (for example, labels), use COUNTA instead of
COUNT.
Additional query words:
XL97 database counta count dynamic
Keywords : kbdta xlchart
Version : WINDOWS:5.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto
|