Microsoft® Office Excel 2003 Inside Out

Using Arrays to Create a Static Chart

Under some circumstances you might want to "freeze" a chart so that it no longer updates itself. The most likely reason to do this is to create a snapshot of your data at some point in time—either as a baseline or as a scenario case.

One way to produce such a chart is to copy the source data somewhere and either hide or protect the worksheet on which it resides. A safer way is to convert all range references in all the chart's SERIES formulas to arrays. To do so, select a series on the chart you want to freeze. A SERIES formula should appear on Excel's formula bar. Press F2 to edit the formula, and then press F9. Excel replaces all range references (including names) to arrays of constants. These constants are ...