Microsoft Office Tutorials and References
In Depth Information
To test the results of your efforts, enter new data in columns A and B, or delete data
from the bottom of the columns. If you performed the preceding steps correctly,
the chart will update automatically. If you receive an error message or the chart
doesn’t update itself, review the preceding steps carefully. This method does work!
Understanding how it works
Many people use this self-expanding chart technique without fully understanding
how it works. There’s certainly nothing wrong with that. If you go through the
hands-on exercise described previously, you should be able to adapt the procedures
to your own charts. But understanding how it works will make it possible to go
beyond the basic concept and create more powerful types of dynamic charts.
ABOUT NAMED FORMULAS
Many of the interactive chart techniques described in this chapter take advantage
of a powerful feature called named formulas . You’re probably familiar with the
concept of named cells and ranges. But did you know that naming cells and ranges
is really a misnomer? When you create a name for a range, you are really creating
a named formula .
When you work with the Define Name dialog box, the Refers to field contains
the formula, and the Names in workbook field contains the formula’s name. You’ll
find that the contents of the Refers to field always begin with an equal sign — a sure
sign that it’s a formula.
Unlike a normal formula, a named formula doesn’t exist in a cell. Rather, it
exists in Excel’s memory and does not have a cell address. But you can access the
result of a named formula by referring to its name, either in a standard formula or
in a chart’s SERIES formula.
After defining the two named formulas, Excel evaluates these formulas every
time the worksheet is calculated. But these named formulas aren’t used in any cells,
so there is no visible effect of creating these named formulas — until you use them
to define the chart series.
To get a better handle on named formulas, use the Define Name dialog box to
create the following formula, and name it Sum12Cells .
After you’ve created the named formula, enter the following formula into any cell:
This formula will return the sum of A1:A12.