In a spreadsheet, a cell is the intersection of a
column and a row, and each cell has a unique address (or
reference), such as B3 or D1, that indicates its location. The columns
are named with letters of the alphabet -- A, B, ... Z, AA, AB, ... AZ,
BA, BB, ... BZ, and so on, through IA, IB, ... IZ -- for a total of
256 columns. Rows are numbered 1 through 65,536!

The address of a cell located in column C, row 24, is C24.
The address of the cell in column LM in the 2,321st row is
LM2321.

Cell addresses written this way -- M47, Q13, CT109, for instance --
in a formula, are called relative addresses because they refer
to the cell relative to the position of the active cell or the
cell which contains a formula - the distance and direction the cursor
has to move to reach that address from its present position.

For example: you've entered several columns of figures --
A3 through A17, B3 through B17, etc. -- and you want to total them at
the bottom of each column. You type the following formula in cell A18
to total the entries in column A: =SUM(A3:A17). If you copy the
formula into cells B18, C18, D18, etc., and then look at the formulas,
you'll see that the original reference (A3:A17) has changed in every
column. The formula in column B is

=SUM(B3:B17);

column C's formula is =SUM(C3:C17), etc. When you copied the formula
into the new columns, it continued to refer to the same rows as the
original formula (rows 3-17), but changed the column reference to
reflect the column into which you copied the formula.

Here's how it works: The column headings A, B, C, ML, IZ are there
for the end-user's convenience only, to provide an easy reference for
us human types. When you place the cursor in cell B18 and tell Excel to
insert the sum of the values in cells B3-B17, it adds up the numbers in
the 15 rows above the current cursor position (the cell in which you
are placing the formula, called the active cell).

When you copy the formula into columns C, D, E, etc., all Excel
cares about is that you still want to total the numbers in the 15 rows
above the cell containing the formula. The formula that the program
'sees' looks like this:

=SUM(R[-15]C:R[-1]C)

which simply means: back up 15 rows (R[-15]) and add up everything
between that cell and the cell one row above this formula.

The column reference, C, is not in brackets and doesn't reference a
number, so the program knows that you want it to stay in the same
column when it does the addition. The square brackets enclose a number
that represents a location relative to the present location: a
minus sign means up with respect to the present row or left of
the present column. A number in square brackets without a minus
sign means that the relative location is to the right of or down from
the current location.

These are relative addresses: they tell the program
how many cells to the left, right, up, or down to look for the values
to use, for example, in a calculation.

If you enter a cell address without the square brackets --
R2C5, for example -- you have entered an absolute
address: when the program sees an absolute address, it goes
directly to the referenced cell. The absolute address is an actual cell
in the worksheet, specifically, the cell located at the intersection of
row 2 and column 5 (in this example). You can copy a formula containing
an absolute address anywhere in your worksheet and -- unlike the
relative address -- it will always refer to exactly the same
cell (here, R2C5 or, in more familiar language, E2).

If you want to add up the numbers in a row instead of a column
(A3+B3+C3, for example), the formula would look like this:

=SUM(A3:C3).

If D3 is the active cell, the program knows that you mean it should
back up 3 cells and then add the contents of that cell and the next 2
cells to the right and print the total in D3. Here's the formula the
way the program sees it:

=SUM(RC[-3]:RC[-1])

which means to back up 3 columns in the same row [-3] and add up the
numbers in that cell, the cell 2 places back, and the cell just before
the one with the formula.

Whenever you use relative addresses and copy formulas from one
column or row to another column or row, the addresses will change to
reflect the new location, but will continue to calculate the same
relative range of cells.

Absolute cell references, on the other hand, always refer to
a particular cell. Perhaps you're doing a calculation in which you use
a constant (a discount or interest rate, for example, or another
calculation). You can place the constant in a cell anywhere in the
worksheet or workbook, then use the absolute address of that
cell (including the work sheet name, when appropriate) in any
calculations with the constant. Wherever you copy the formula that
refers to the constant, it will still refer to the same cell for the
constant portion of the formula.

Absolute cell addresses are written using dollar signs. For example,
if we want to write a reference that always refers to row 1, column 1,
the absolute address would be $A$1 (or R1C1, without square
brackets enclosing the numbers). When you copy formulas using relative
addresses, the row and/or column references will change as illustrated
above: if you copy the formula =SUM(A3:A17) from A18 into B18, the
formula will change to read =SUM(B3:B17).

If you create another calculation to add whatever value is in cell
A1 to the sums you just did, your formula might look like this:
=A18+$A$1. When you copy the formula to B18, the formula will
look like this: =B18+$A$1. In column C, it will be
=C18+$A$1.

The relative part of the formula will change, as we saw
above. The absolute address will always remain the same. All of
the formulas will refer to exactly the same cell, the one in row 1,
column 1 of your worksheet.

Excel's multiple sheet workbooks make it convenient and easy to keep
related information together in a single file. You can use a separate
worksheet for each related category, then use data from more than one
sheet to calculate totals or determine trends. To do this, you need to
be able to give Excel specific information about where your data is
located. In the same worksheet, a cell address is enough.

If your workbook contains 6 or 11 or 20 sheets, you obviously need
to specify which sheet contains the data you want to use. This is done
by including a sheet name or range in the formula.

To insert the contents of a particular cell in another sheet, if you
have not renamed your worksheets, the reference would look like this:

=Sheet7!T4

Notice that there are no spaces between the elements and that the
sheet designation is followed by an exclamation point (!).

Renamed Sheets

If you have named your worksheets (e.g., Qtr. 1 Totals or
Aircraft Sales 95) and the names include any spaces, you must
enclose the entire sheet name in single quotes if you type it in
yourself:

Example: January Sales!$A$1

The example is an absolute reference to cell A1 in the
sheet named January Sales.

If you enter references by selecting cells directly (described
below), Excel will add the single quotes for you.

"A 3-D reference is a range that spans two or more sheets in
the workbook."

The syntax for entering a 3-D reference is very much the same as for
entering a range of cells: the name of the first sheet in the range, a
colon, and the name of the last sheet in the range; an exclamation
point (to separate the sheet reference from the cell reference); and
the cell or range of cells being referenced.

Example: Your workbook contains sales figures for
the year, with each month on a separate sheet. You want to create a
summary sheet that shows the quarterly totals for each of several
categories: gross sales by region, variable production costs, fixed
costs, selling expense, etc.

For a 3-D reference to work, the layout must be consistent
from one sheet to the next. If the total for the Northeast
Region January gross sales is located in Sheet1, cell B125, then the
Northeast Region gross sales totals for February through December
must also be located in cell B125 on sheets 2 through 12.

In the 1st Quarter Summary area of your worksheet, you will enter
the gross sales for January, February, and March, located in worksheets
1-3. Click on the cell where you want the total to appear, type in an
equal sign (=), then either type in the reference or select it.

To type in the reference: Obviously, before you can type in a
reference, you must know which sheets and which cell ranges contain the
data you want to access. If you have the information, enter the
reference as follows (we'll use Excel's SUM function so
we don't have to type in all of the individual addresses.):

=SUM(Sheet1:Sheet3!B125)

The same calculation, without using the SUM function, would look
like this:

=Sheet1!B125+Sheet2!B125+Sheet3!B125

To reference a range of cells in the same sheets:

=SUM(Sheet1:Sheet3!B5:B123)

The same calculation without using the SUM function...well, don't
even bother! First you have to add B5 through B123: =B5+B6+B7+... B123
for sheet1, then do the same for sheet2, then for sheet 3... so...

but you can't type in little dots; you have to type in all
the numbers between B7 and B123. Three times! Use the SUM function!!!

To Select a Reference:

In the location where you want the referenced data to appear,
type in an equal sign (=).

Click on the tab of the first sheet you want to include in your
calculation. Hold down the [Shift] key and click on the
tab of the last sheet to be included, then use the mouse to
select the cell or range of cells you want to use.

NOTE: Moving, copying,
adding, or deleting cells or sheets can affect references. Most of the
time when cells are moved or copied, Excel adjusts the references and
there is no problem. However, if you paste or copy a cell into a
location that contained referenced data, the cells that used the
original data in the cell may show an error or might just produce
incorrect results.

Results of calculations may also be affected if you add, delete, or
change the position of worksheets within a specified range of
sheets.

The Excel User's Guide has explanations of possible problems
in the section Moving and Copying Formulas and References, pages
138-141. (For my fellow employees, call Corporate Training at extension
4898 to either check out the book or have these pages photocopied for
you.)

Please Note: Much of
the material in this section was either taken directly or paraphrased
from Excel 97 Help.

Excel 97 lets you use cell names, range names, and constant names in
formulas instead of referencing cells by column and row addresses. Not
only do names make it more obvious what the formula refers to, but when
it becomes necessary to change the value in a constant, you won't have
to find every formula in your worksheet that uses that constant.

If you enter a numeric value in each occurrence of a formula you'll
have to find and change each one manually when, for example, the sales
tax rate changes. Perhaps you multiply each total sale by the tax rate,
then add the result to the total sale amount. If the sales tax
increases from 7.5% to 8%, you'll need to find and replace each
occurrence of 7.5% with 8.0%. Even with the Find and Replace
function, that could be a tedious job in a large workbook.

And there's a good possibility that you'll miss some occurrences,
which will result in errors in your calculations.

If you create a named constant  for example, SalesTaxRate
 then refer to it in your formula, you'll only have to make a
single change the next time taxes go up.

When you enter the formula to calculate the tax amount on a
particular sale, you'll use the name you created instead of the
amount. The calculation might look like this:

=T97*SalesTaxRate

The contents of cell T97 will be multiplied by whatever value it
finds in the constant SalesTaxRate. When the tax rate changes,
you only change the value once  in the named constant.

You can also use the column and row labels in a worksheet to refer
to data. For example, if a table contains sales amounts in a column
labeled Sales and a row for a division labeled Support,
you can find the sales amount for the Support division by entering the
formula

The space between the labels is called the intersection
operator, and specifies that that value the formula will return is
the value it located in the cell at the intersection of the row labeled
Support and the column labeled Sales.

If you haven't put labels in your worksheet or if you have
information on one worksheet that you want to use on other sheets in
the same workbook, you can create a name that describes the cell or
range. A descriptive name in a formula makes it easier to understand
what the formula does. For example, the formula

=SUM(FirstQuarterSales)

might be easier to identify than

=SUM(Sales!C20:C30).

In this example, the name FirstQuarterSales represents the
range C20:C30 on the worksheet named Sales.

Names are available to any sheet within the workbook. For example,
if the name ProjectedSales refers to the range A20:A30 on the
first worksheet in the workbook, you can use the name
ProjectedSales on any other sheet in the same workbook to refer
to range A20:A30 on the first worksheet. Names can also be used to
represent formulas or constants.

The first character of a name must be a letter or an underscore
character. Remaining characters in the name can be letters,
numbers, periods, and underscore characters.

Names cannot be the same as a cell reference, such as $Z$100 or
R1C1.

Spaces are not allowed. Underscore characters and periods may be
used as word separators  for example, First.Quarter
or Sales_Tax.

A name can contain up to 255 characters.

Names can contain uppercase and lowercase letters. Excel 97 does
not distinguish between uppercase and lowercase characters in
names. For example, if you have created the name Sales and then
create another name called SALES in the same workbook,
the second name will replace the first one.

To change cell references in formulas to names

Select the range that contains formulas in which you want to
replace references with names.

To change the references to names in all formulas on the
worksheet, select a single cell.

On the Insert menu, point to Name, and then click
Apply.

In the Apply names box, click one or more names.

To create a name to represent a formula or a constant value

On the Insert menu, point to Name, and then click
on Define.

In the Names in workbook box, enter the name for the
formula.

In the Refers to box, type = (an equal sign), followed by
the formula or the constant value.

To determine what a name refers to

On the Insert menu, point to Name, and then click
on Define.

In the Names in workbook list, click the name whose
reference you want to check.

The Refers to box displays the reference, formula, or
constant the name represents.

Tip: You can also create a list of the available names
in a workbook. Locate an area with two empty columns on the worksheet
(the list will contain two columns  one for the name and one for
a description of the name). Select a cell that will be the upper-left
corner of the list. On the Insert menu, point to Name,
and then click Paste. In the Paste Name dialog
box, click Paste List.

When you have labels for the columns and rows on your worksheet, you
can use those labels to create formulas that refer to data on the
worksheet.

If your worksheet contains stacked column labels  in which a
label in one cell is followed by one or more labels below it  you
can use the stacked labels in formulas to refer to data on the
worksheet.

For example, if the label Projected is in cell E5 and the
label 1996 is in cell E6, the formula =SUM(Projected 1996)
returns the total value for the Projected 1996 column. If row 8
contains sales amounts and the label Sales is in cell D8, you
can refer to the projected sales amount for 1996 with the formula
=Projected 1996 Sales.

When you refer to information by using stacked labels, you refer to
the information in the order in which the labels appear, from top to
bottom. If the label 1996 is in cell E5 and the label Actual is in cell
E6, you can refer to the actual figures for 1996 by using 1996 Actual
in a formula. For example, to calculate the average of the actual
figures for 1996, use the formula =AVERAGE(1996 Actual).

If your data does not have labels or if you have information stored
on one worksheet that you want to use on other sheets within the same
workbook, you can create a name that describes the cell or range. A
descriptive name in a formula can make it easier to understand the
purpose of the formula.

For example, the formula =SUM(FirstQuarterSales) might be easier to
identify than =SUM(Sales!C20:C30). In this example, the name
FirstQuarterSales represents the range C20:C30 on the worksheet named
Sales.

Names are available to any sheet within the workbook. For example,
if the name ProjectedSales refers to the range A20:A30 on the first
worksheet in the workbook, you can use the name ProjectedSales on any
other sheet in the same workbook to refer to range A20:A30 on the first
worksheet. Names can also be used to represent formulas or values that
do not change (constants). For example, you can use the name SalesTax
to represent the sales tax amount (such as 6.2 percent) applied to
sales transactions.