XL: Using Defined Names to Automatically Update Chart Range

ID: Q104185

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a
Microsoft Excel for OS/2, version 2.2, 3.0
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0

To set up a chart that is automatically updated as you add new information to an existing chart range, use either of the following methods.

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

Choose Define Name from the Formula menu.

In the Name box, type "Date" (without the quotation marks).

In the Refers To box, type:

=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)

Choose Add.

In the Name box, type "Sales" (without the quotation marks).

In the Refers To box, type:

=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

Clear cell B2, and type the following formula:

=RAND()*0+10

NOTE: The above formula, using the volatile function "RAND()", is needed to automatically update the OFFSET() formula used in the defined name "Sales" when new data is entered into column B. The value "10", used in this formula is the original value of cell B2.

To create a new chart, select the data, choose New from the File menu and select the Chart option.

From the Chart menu, choose Edit Series.

Choose "Sales" as the series to edit.

In the formula in the X Labels box, replace the cell reference with the defined name Date. For example, the formula might read, =FILENAME.XLS!Date.

In the formula in the Y Values box, replace the cell reference with the defined name Company. For example, the formula might read, =FILENAME.XLS!Sales.

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 do this, 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 choose Set Database from the Data menu.

From the Formula menu, choose Define Name.

In the Name box, type "Date" (without the quotation marks).

In the Refers To box, type:

=OFFSET(Database,1,0,ROWS(Database)-1,1)

Choose Add.

In the Name box, type "Sales" (without the quotation marks).

In the Refers To box, type:

=OFFSET(Database,1,1,ROWS(Database)-1,1)

Select the range A1:B4, and create a chart by choosing New from the File menu, and then selecting Chart from the list.

From the Chart menu, choose Edit Series.

Choose "Sales" as the series to edit.

In the formula in the X Labels box, replace the cell reference with the defined name Date. For example, this formula might read, =FILENAME.XLS!Date.

In the formula in the Y Values box, replace the Cell Reference with the defined name Company. For example, this formula might read, =FILENAME.XLS!Sales.

As long as the data that you want to appear in your chart is defined as a database, the chart will be updated automatically as you add new data.

NOTE: If you are creating a series chart that plots every value in a contiguous block of cells in single column starting from row 1, you can use either of the following formulas in the refers to portion of the defined name:


   =INDIRECT("Sheet1!$a$1:$a"&COUNT(Sheet1!$A:$A))

      -or-

   =Sheet1!$A$1:OFFSET(Sheet1!$A$1,COUNT(Sheet1!$A:$A),0)

To start on a row other than one, 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 contiguous non-numeric entries (such as labels), use COUNTA() instead of COUNT().

REFERENCES

"User's Guide 1," version 4.0, pages 306-325, 434-451
"Function Reference Guide," version 4.0, pages 68, 69, 299

Additional query words: 2.00 2.0x 4.00a database counta count dynamic

Keywords :
Version : WINDOWS: 2.0,3.0,4.0,4.0a; MACINTOSH: 2.0,3.0,4.0
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbhowto