Microsoft Office Tutorials and References
In Depth Information
Understanding the Secret about Names
Understanding the Secret about Names
Most Excel users have at least a rudimentary understanding of named cells and named ranges.
You can use the Formulas
Define Name command to provide a meaningful
name to a cell or range. Then you can use those defined names in your formulas.
Defined Names
For example, if you give the name Sales to range A2:A13, you can write a formula such as
=SUM(Sales). Just about every Excel user I know refers to this concept as named ranges or
named cells (even I did, in the first paragraph of this tip). This terminology isn’t quite accurate,
however.
Here’s the secret to understanding names:
When you create a name, you’re creating a named formula. Unlike a normal formula, a named
formula doesn’t exist in a cell. Rather, it exists in Excel’s memory.
Although this revelation isn’t exactly earth-shaking, keeping this “secret” in mind helps you
understand some advanced naming techniques.
When you work with the New Name dialog box, the Refers To field contains the formula, and the
Name field contains the formula’s name. You’ll find that the contents of the Refers To field always
begin with an equal sign, which makes it a formula.
As you can see in Figure 87-1, the workbook contains a name (InterestRate) for cell B4 on Sheet1.
The Refers To field lists the following formula:
=Sheet1!$B$4
Figure 87-1: Technically, the name InterestRate is a named formula, not a named cell.
Whenever you use the name InterestRate, Excel evaluates the formula named InterestRate and
returns the result. For example, you might type this formula into a cell:
=InterestRate*1.05
 
Search JabSto ::




Custom Search