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 dialog 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 cans till be formatted (it doesn't become a picture). The formula bar in the figure below shows the SERIES() formula after converting the range references to arrays.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks