Microsoft Office Tutorials and References
In Depth Information
A SERIES formula does not use structured table referencing. If you edit the SERIES for-
mula to include a table reference such as Table1[Widgets], Excel converts the table ref-
erence to a standard range address. However, if the chart is based on data in a table,
the references in the SERIES formula adjust automatically if you add or remove data
from the table.
As I noted previously, a SERIES formula cannot use worksheet functions. You can, however, create named for-
mulas (which use functions) and use these named formulas in your SERIES formula. As you see later in this
chapter, this technique enables you to perform some useful charting tricks.
Unlinking a chart series from its data range
Normally, an Excel chart uses data stored in a range. If you change the data in the range, the chart updates auto-
matically. In some cases, you may want to “unlink” the chart from its data ranges and produce a static chart —
a chart that never changes. For example, if you plot data generated by various what-if scenarios, you may want
to save a chart that represents some baseline so you can compare it with other scenarios.
There are two ways to create a static chart:
• Paste it as a picture. Activate the chart and then choose Home ⇒ Clipboard ⇒ Copy ⇒ Copy as Picture. (Ac-
cept the default settings from the Copy Picture dialog box.) Then, activate any cell and choose Home ⇒ Clip-
board ⇒ Paste (or press Ctrl+V). The result is a picture of the copied chart. You can then delete the original
chart if you like. When a chart is converted to a picture, you can use all of Excel's image editing tools. Fig-
ure 17-2 shows an example.
• Convert the range references to arrays. Click a chart series and then click the Formula bar to activate the
SERIES formula. Press F9 to convert the ranges to arrays. Repeat this for each series in the chart. This tech-
nique (as opposed to creating a picture) enables you to continue to edit and format the chart. Here's an ex-
ample of a SERIES formula after the range references were converted to arrays.