Excel: Chart Changes When Inserting or Deleting Columns/Rows

SUMMARY

In Microsoft Excel, charts use absolute references to the worksheet to obtain charting information. When a row or column is deleted or inserted, the information on the spreadsheet is moved. Thus, the links on the chart are not updated and point to the wrong cells.

To allow insertion and deletion of rows or columns, use defined names when referencing the cells on the worksheet. When rows or columns move due to an insertion or deletion, the defined names are adjusted along with the charts.

Note: When the chart is reopened independently, you are asked about updating links to a closed document. If there were no changes to the worksheet, then you can click No. If the worksheet was changed, it must also be open for the links to be properly accessed. This is due to the complex nature of the links.

Example

If a chart series resembles

   =SERIES(,,$A$1:$A$20,1)

Then change the range in the SERIES() formula to be the following:

   =SERIES(,,Series1,1)