Microsoft Office Tutorials and References

In Depth Information

**Testing it**

Testing it

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
.

=SUM($A$1:$A$12)

After you’ve created the named formula, enter the following formula into any cell:

=Sum12Cells

This formula will return the sum of A1:A12.