How to fix the chart data
You can unlink a chart from its data ranges and produce a static chart that remains unaffected by later changes in the data.
You have two ways to do this:
- Copy the chart 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 appears. Click OK to accept the defaults. Then click anywhere in your worksheet and choose Edit -> Paste.
- Convert the range references to 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 can still be formatted. (It doesn't become a picture.)
There is a limit to the length of a Series formula, so the second procedure will not work if the chart series contains many data points.