Microsoft KB Archive/122136

= Microsoft Knowledge Base =

Information in Chart Series Changes When You Insert a New Row
Last reviewed: September 12, 1996

Article ID: Q122136

The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.0, 4.0, 5.0, 5.0c
 * Microsoft Excel for OS/2, version 3.0
 * Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0
 * Microsoft Excel for Windows NT, version 5.0

SYMPTOMS
In Microsoft Excel, if you insert a new row in a worksheet, links to the worksheet will refer to the original cells and are not updated for the inserted cells. This behavior may not be convenient if you want a series formula for a chart to always refer to the same location on the worksheet.

CAUSE
When you insert a new row in a worksheet, and the worksheet contains an embedded chart, the formulas for the chart series are updated to reflect the movement of the rows. For example, if you create an embedded column chart from a worksheet with the following data

A1: BOB     B1: 3 A2: SUE     B2: 6 A3: TOM     B3: 1 A4: MARY    B4: 7 A5: TIM     B5: 9 and you select row 1 and insert a new row, the reference for the first chart series will change from A1:A5 to A2:A6. To see this change, double- click the chart, and select either column in the chart. The series formula will read as follows

=SERIES(,SHEETNAME!$A$2:$A$6,SHEETNAME!$B$2:$B$6,1) where SHEETNAME is the name of the worksheet.

WORKAROUND
To work around this problem, use a defined name in combination with the INDIRECT function. To have the chart series always reflect the top five rows of data, follow the appropriate sample procedure below.

Microsoft Excel version 5.0
 From the Insert menu, choose Name, and then choose Define. In the Name box, type "labels" (without the quotation marks).  In the Refers To box, type the following formula =OFFSET(INDIRECT("SHEETNAME!A1"),0,0,5,1) where SHEETNAME is the name of the worksheet that contains the chart data, and 5 is the number of rows of data to be charted.  Choose the Add button. In the Name box, type "data" (without the quotation marks).  In the Refers To box, type the following formula =OFFSET(INDIRECT("SHEETNAME!B1"),0,0,5,1) where SHEETNAME is the name of the worksheet that contains the chart data, and 5 is the number of rows of data to be charted.  Choose the Add button, and then choose OK. Select the chart again, and select any series in the chart.</li>  Edit the series formula in the formula bar to read as follows: =SERIES(,SHEETNAME!labels,SHEETNAME!data,1) where SHEETNAME is the name of the worksheet that contains the chart data. </li> Select the worksheet and insert another new row 1.</li></ol>

The chart should be updated automatically and will show a column of zero height as the first column.

<ol>  Enter the following into the new cells: A1: FRED    B1: 15 </li></ol>

The chart should now have a column for five rows from A1 to B5 (the last five rows of data that were entered into the worksheet).

Microsoft Excel versions 3.0 and 4.0
<ol> From the Formula menu, choose Define Name, and in the Name box, type "labels" (without the quotation marks).</li>  In the Refers To box, type the following formula =OFFSET(INDIRECT("A1"),0,0,5,1) where 5 is the number of rows of data to be plotted in your chart. </li> Choose the Add button</li> In the Name box, type "data" (without the quotation marks).</li>  In the Refers To box, type the following formula =OFFSET(INDIRECT("B1"),0,0,5,1) where 5 is the number of rows of data to be plotted in your chart. </li> Choose the Add button, and then choose the OK button.</li>  Select the chart again, and select any series in the chart, edit the series formula in the formula bar to read as follows =SERIES(,SHEETNAME!labels,SHEETNAME!data,1) where SHEETNAME is the name of the worksheet that contains the chart data. </li> Select the worksheet, and insert another new row 1.</li></ol>

The chart should automatically be updated and should show a column of zero height as the first column.

<ol>  Enter the following in the new cells in the row: A1: FRED    B1: 15 </li></ol>

The chart should now have a column for five rows, from A1:B5, the last five rows of data entered into the worksheet.