Microsoft KB Archive/65112

{| = Chart Legend Affected by Changes in Spreadsheet =
 * width="100%"|

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 &quot;Sheet1$A$3&quot; refers to the location of the series name in the worksheet, &quot;Sheet1!$B$1:$C$1&quot; refers to the location of the category label in the worksheet, &quot;Sheet1!$B$3:$C$3&quot; refers to the location of the values in the worksheet, and &quot;2&quot; refers to the position of the series in the sequence of series in the chart.
 * }