Microsoft Office Tutorials and References
In Depth Information
Working With Range Names
A P P E N D I X A
■ ■ ■
Working With Range Names
What’s in a Name? Plenty, if it’s a Range
It’s easy enough to understand how to identify a range – e.g., A34:R78 refers to all the cells camped out
between A34 and R78, including those two cells which hold down the upper left and lower right corners
of the range. But that reference isn’t as informative as it could be. You might want or need to know
what kind of data populates a range, be they test scores, income figures, or population statistics. As a
result, Excel lets you name a range and use it in a formula, so that an expression such as
=SUM(A6:A20)
=SUM(Income)
where the word “Income” represents or acts as a proxy for A6:A20, which could be listing a collection of
income data. Naming a range helps you, and anyone else who may be viewing the workbook, to
quickly understand what the range is about, and can also ease the formula writing process. After all, it
may be si mpl e r to ty pe
=AVERAGE(tests)
than
=AVERAGE(B15:B112)
which requires you to remember those range coordinates, and/or drag down all those cells.
And there’s another reason you might want to name a range, one I alluded to about 300 pages ago.
I wrote there about naming a range which consists of exactly one cell. If that cell – say C1 - is applied
repeatedly to different formulas – say a constant grade bonus of five points entered in that cell, added
to a series of test scores listed down a column – I’d have to write something like
=A3+C\$1
and then copy that formula down the column in order to add the five points to all the other exams listed
down the A column. The dollar sign establishes an absolute reference, whereby the 1 in C1 is held
constant. But if you name C1 Bonus, for example, you can write
=A3+Bonus
without having to worry about those dollar signs. Naming a range automatically holds its cell
references constant, no matter where you copy it.
Naming a range is easy, although as usual Excel offers you more than one way to achieve this end.

Search JabSto ::

Custom Search