Unlink a chart series from its data range


Normally, an Excel chart stores data in a range. If you change the data in that range, the chart updates automatically. Sometimes you may want to unlink the chart from its data ranges and produce a static chart that remains unaffected by later changes in the data. For example, suppose you plot data generated by various what-if scenarios, and you want to save a chart that represents some baseline scenario for comparison with others.

One way to create a static chart is to copy and paste it as a picture. Activate your chart, hold down the <Shift> key, and choose Edit-Copy Picture (this option is available only when you hold down <Shift> as you select Edit). The Copy Picture dialogue box will appear. Click OK to accept the defaults. Then click anywhere in your worksheet and choose Edit-Paste.

Another way to create a static chart is to convert the range references into arrays. Select a chart series and then click the formula bar to activate the SERIES() formula. Press <F9> to convert the range references into arrays. Repeat this for each series in the chart. Now the chart remains formattable rather than becoming a picture. The formula bar in FIGURE 2 shows the SERIES() formula after conversion of the range references into arrays.

-- John Walkenback


Category:Spreadsheets
Issue: June 2000

These Web pages are produced by Australian PC World © 2000 IDG Communications