Cell and Range References

Most formulas reference one or more cells . This reference can be made by using the cell's or range's address or name (if it has one). Cell references come in four styles:

Relative: The reference is fully relative. When the formula is copied , the cell reference adjusts to its new location. Example: A1.
Absolute: The reference is fully absolute. When the formula is copied, the cell reference does not change. Example: $A$1.
Row Absolute: The reference is partially absolute. When the formula is copied, the column part adjusts, but the row part does not change. Example: A$1.
Column Absolute: The reference is partially absolute. When the formula is copied, the row part adjusts, but the column part does not change. Example: $A1.

By default, all cell and range references are relative. To change a reference, you must manually add the dollar signs. Or, when editing a cell in the formula bar, move the cursor to a cell address and press F4 repeatedly to cycle through all four types of cell referencing.

Why use references that aren't relative?

If you think about it, you'll realize that the only reason why you would ever need to change a reference is if you plan to copy the formula. Figure 3-1 demonstrates why this is so. The formula in cell C3 is

 =$B3*C

Figure 3-1: An example of using nonrelative references in a formula.

This formula calculates the area for various lengths (listed in column B) and widths (listed in row 3). After the formula is entered, it can then be copied down to C7 and across to F7. Because the formula uses absolute references to row 2 and column B and relative references for other rows and columns , each copied formula produces the correct result. If the formula used only relative references, copying the formula would cause all the references to adjust and thus produce incorrect results.

About R1C1 notation

Normally, Excel uses what's known as A1 notation: Each cell address consists of a column letter and a row number. However, Excel also supports R1C1 notation. In this system, cell A1 is referred to as cell R1C1, cell A2 as R2C1, and so on.

To change to R1C1 notation, access the Formulas tab of the Excel Options dialog box. Place a check mark next to R1C1 Reference Style. After you do so, you'll notice that the column letters all change to numbers . All the cell and range references in your formulas are also adjusted.

Table 3-1 presents some examples of formulas that use standard notation and R1C1 notation. The formula is assumed to be in cell B1 (also known as R1C2).

Table 3-1: COMPARING SIMPLE FORMULAS IN TWO NOTATIONS
Open table as spreadsheet
Standard	R1C1
=A1+1	=RC[ “1]+1
=$A$1+1	=R1C1+1
=$A1+1	=RC1+1
=A$1+1	=R1C[ “1]+1
=SUM(A1:A10)	=SUM(RC[ “1]:R[9]C[ “1])
=SUM($A$1:$A$10)	=SUM(R1C1:R10C1)

If you find R1C1 notation confusing, you're not alone. R1C1 notation isn't too bad when you're dealing with absolute references. But when relative references are involved, the brackets can be very confusing.

The numbers in brackets refer to the relative position of the references. For example, R[ “5]C[ “3] specifies the cell that's five rows above and three columns to the left. On the other hand, R[5]C[3] references the cell that's five rows below and three columns to the right. If the brackets are omitted, the notation specifies the same row or column. For example, R[5]C refers to the cell five rows below in the same column.

Although you probably won't use R1C1 notation as your standard system, it does have at least one good use. Using R1C1 notation makes it very easy to spot an erroneous formula. When you copy a formula, every copied formula is exactly the same in R1C1 notation. This is true regardless of the types of cell references that you use (relative, absolute, or mixed). Therefore, you can switch to R1C1 notation and check your copied formulas. If one looks different from its surrounding formulas, there's a good chance that it might be incorrect.

In addition, if you write VBA code to create worksheet formulas, you might find it easier to create the formulas by using R1C1 notation.

Referencing other sheets or workbooks

When a formula refers to other cells, the references need not be on the same sheet as the formula. To refer to a cell in a different worksheet, precede the cell reference with the sheet name followed by an exclamation point. Here's an example of a formula that uses a cell reference in a different worksheet (Sheet2):

 =Sheet2!A1+1

You can also create link formulas that refer to a cell in a different workbook. To do so, precede the cell reference with the workbook name (in square brackets), the worksheet name, and an exclamation point. Here's an example:

 =[Budget.xlsx]Sheet1!A1

If the workbook name in the reference includes one or more spaces, you must enclose it (and the sheet name) in single quotation marks. For example:

 ='[Budget For 2008.xlsx]Sheet1'!A1

Referencing Data in a Table

Excel 2007 supports a special type of range that has been designated as a table (using the Insert Tables Table command). Tables add a few new twists to formulas.

When you enter a formula into a cell in a table, Excel automatically copies the formula to all of the other cells in the column - but only if the column was empty. This is known as a calculated column. If you add a new row to the table, the calculated column formula is entered automatically for the new row. Most of the time, this is exactly what you want. If you don't like the idea of Excel entering formulas for you, use the SmartTag to turn this feature off. The SmartTag appears after Excel enters the calculated column formula.

Excel 2007 also supports "structured referencing" for referring to cells within a table. The table in the accompanying figure is named Table1.

You can create formulas that refer to cells within the table by using the column headers. In some cases, this may make your formulas easier to understand. But the real advantage is that your formulas will continue to be valid if rows are added or removed from the table. For example, these are all valid formulas:

 =SUM(Table1[Washington]) =Table1[[#Totals],[California]] =Table1[[#Headers],[California]] =SUM(Table1[[#This Row],[California]:[Washington]])

The last formula, which uses [#This Row], is valid only if it's in a cell in one of the rows occupied by the table.

If the linked workbook is closed, you must add the complete path to the workbook reference. Here's an example:

 ='C:\Budgeting\Excel Files\[Budget For 2008.xlsx]Sheet1'!A1

Although you can enter link formulas directly, you can also create the reference by using normal pointing methods . To do so, the source file must be open. When you do so, Excel creates absolute cell references. If you plan to copy the formula to other cells, make the references relative.

Working with links can be tricky. For example, if you choose the Office Save As command to make a backup copy of the source workbook, you automatically change the link formulas to refer to the new file (not usually what you want to do). Another way to mess up your links is to rename the source workbook when the dependent workbook is not open.