Chapter 2 Working with Data 51
5 SUM($C$2:$C$11)
is an absolute reference to the range C2:C11. If you copied this formula into
other cells, it would still point to C2:C11 and would not be shifted.
You can also create formulas that use mixed references, combining both
absolute and relative references. For example, the formulas
5 SUM($C2:$C11)
and
5 SUM(C$2:C$11)
use mixed references. In the first example, the column is absolute but
the row is relative, and in the second example, the column is relative
but the row is absolute. This means that in the fi rst example, Excel will shift
the row references but not the column references, and in the second exam-
ple, Excel will shift the column references but not the row references. You
can learn more about reference types and how to use them in Excel’s online
Help. In most situations in this book, you’ll use relative references, unless
otherwise noted.
Range Names
Another way of referencing a cell in your workbook is with a range name.
Range names are names given to specific cells or cells ranges. For ex-
ample, you can define the range name Gas to refer to cells B2:B11 in
your worksheet. To calculate the total gasoline sales, you could use the
formula
5 SUM(B2:B11)
or
5 SUM(Gas).
Range names have the advantage of making your formulas easier to
write and interpret. Without range names you would have to know some-
thing about the worksheet before you could determine what the formula
=SUM(B2:B11) calculates.
Excel provides several tools to create range names. You’ll fi nd it easier to
perform data analysis on your data set if you’ve defi ned range names for all
of the columns. A simple way to create range names is to select the range
of data including a row or column of titles. You can then use the titles from
the worksheet to defi ne the range name. Try it now with the service station
data.
