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

  1. From the Insert menu, choose Name, and then choose Define.

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

  3. 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.

  4. Choose the Add button.

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

  6. 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.

  7. Choose the Add button, and then choose OK.

  8. Select the chart again, and select any series in the chart.

  9. 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.

  10. Select the worksheet and insert another new row 1.

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

  1. Enter the following into the new cells: 

         A1: FRED     B1: 15
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

  1. From the Formula menu, choose Define Name, and in the Name box, type "labels" (without the quotation marks).

  2. 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.

  3. Choose the Add button

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

  5. 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.

  6. Choose the Add button, and then choose the OK button.

  7. 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.

  8. Select the worksheet, and insert another new row 1.

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

  1. Enter the following in the new cells in the row: 

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

REFERENCES

For more information about the Offset and Indirect functions, choose the search button in the Reference Information Help and type: 

   Offset Function Indirect Function
"User's Guide," version 5.0, Chapter 15 "User's Guide 1," version 4.0, Chapter 12 "Function Reference," version 4.0, pages 238-239, 299-300 "User's Guide," version 3.0, Chapter 12 "Function Reference," version 3.0, pages 129, 163-164

KBCategory: kbusage
KBSubcategory:

Additional reference words: chart indirect offset 3.00 3.00a
4.00 4.00a 5.00 5.00c



THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 12, 1996
©1997 Microsoft Corporation. All rights reserved. Legal Notices.