Chart Legend Affected by Changes in Spreadsheet

 Last reviewed: November 2, 1994
Article ID: Q65112

SUMMARY

When a row or column is deleted or inserted in a Microsoft Excel worksheet, the chart and legend will update incorrectly because the SERIES formula is using absolute referencing.

If a row or column of data gets deleted from a worksheet, the corresponding SERIES formula will still be referencing those empty cells. The empty cells will be returning zeros for data, resulting in a data series that is invisible against the X axis. If a legend is included in the chart, the legend will still have a pattern for the invisible series.

To remove the pattern, delete the invisible series from the chart as follows:

  1. Use the arrow keys to select sections of the chart until the series is located and the corresponding SERIES formula appears in the formula bar.

  2. Use the F2 key to activate the formula bar, backspace over the SERIES formula and press ENTER, or select the SERIES formula with the mouse and delete the series.

The following is the only workaround to prevent the chart from updating incorrectly:

  1. Select the chart series one at a time and replace each SERIES formula's absolute references with names.

  2. Go to the corresponding spreadsheet and from the Formula menu, choose Define Name to define the absolute references as the names previously used in the SERIES formulas.

This technique only works when the rows or columns are being inserted or deleted above or below the block of data being used for the chart.

MORE INFORMATION

On an Excel chart, each of the data series corresponds to data on the spreadsheet through SERIES formulas, which use absolute references. A SERIES formula consists of four arguments, as follows:

  1. Location of series name in the worksheet

  2. Location of category labels in the worksheet

  3. Location of values in the worksheet

  4. Position of the series in the sequence of series in the chart

The following is a sample SERIES formula 

   =SERIES(Sheet1$A$3,Sheet1!$B$1:$C$1,Sheet1!$B$3:$C$3,2)
where "Sheet1$A$3" refers to the location of the series name in the worksheet, "Sheet1!$B$1:$C$1" refers to the location of the category label in the worksheet, "Sheet1!$B$3:$C$3" refers to the location of the values in the worksheet, and "2" refers to the position of the series in the sequence of series in the chart.

KBCategory: kbother
KBSubcategory:

Additional reference words: noupd

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: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.