Microsoft Office Tutorials and References
In Depth Information
Chapter 17: Charting Techniques
A SERIES formula does not use structured table referencing. If you edit the SERIES
formula to include a table reference such as Table1[Widgets], Excel converts the table
reference to a standard range address.
As I noted previously, a SERIES formula cannot use worksheet functions. You can, however,
create named formulas (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
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 automatically. 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 such a chart:
h Paste it as a picture. Activate the chart and then choose Home
CopyAs Picture. (Accept the default settings in the Copy Picture dialog box.) Then,
activate any cell and choose Home
Paste (or press Ctrl+V). The result is a
picture of the copied chart. You can then delete the original chart if you like.
h 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 (see Figure
17-2). Repeat this for each series in the chart. This technique (as opposed to creating a
picture) enables you to continue to edit and format the chart. This technique will not
work for large amounts of data because Excel imposes a limit on the length of a SERIES
formula (about 1,024 characters).
Figure 17-2: A SERIES formula that uses arrays rather than ranges.