Cell and Range References
Most formulas reference one or more cells by using the cell or range address (or name if it has one). Cell references come in four styles; the dollar sign differentiates them:
-
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
Creating an Absolute or a Mixed Reference
When you create a formula by pointing to cells, all cell and range references are relative. To change a reference to an absolute reference or a mixed reference, you must do so manually by adding the dollar signs. Or when you enter a cell or range address, you can use the F4 key to cycle among all possible reference modes.
If you think about it, you may realize that the only reason you would ever need to change a reference is if you plan to copy the formula.
Figure 2-3 demonstrates an absolute reference in a formula. Cell D2 contains a formula that multiples the quantity (cell B2) by the price (cell C2) by the sales tax (cell B7):
=B2*C2*$B$7
Figure 2-3: This worksheet demonstrates the use of an absolute reference.
The reference to cell B7 is an absolute reference. When the formula in cell D2 is copied to the cells below, the $B$7 reference always points to the sales tax cell. Using a relative reference (B7) results in incorrect results in the copied formulas.
Figure 2-4 demonstrates the use of mixed references. Note the formula in cell C4:
=$B3*C$2
Figure 2-4: An example of using mixed references in a formula.
This formula calculates the area for various lengths (listed in column B) and widths (listed in row 2). After you enter the formula, it can then be copied down and across. Because the formula uses absolute references to row 2 and column B, each copied formula produces the correct result. If the formula uses relative references, copying the formula causes the references to adjust and produce the wrong results.
 |
Normally, Excel uses 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, choose File 
Excel Options to open the Excel Options dialog box, click the Formulas tab, and place a check mark next to the R1C1 Reference Style option. Now, notice that the column letters all change to numbers. And all the cell and range references in your formulas also adjust.
Look at the following examples of formulas using standard notation and R1C1 notation. The formula is assumed to be in cell B1 (also known as R1C2).
| 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) |
Open table as spreadsheet If you find R1C1 notation confusing, you're not alone. R1C1 notation isn't too bad when you're dealing with absolute references. When relative references are involved, though, the brackets can drive you nuts.
The numbers in brackets refer to the relative position of the references. For example, R[−5]C[−3] specifies the cell that appears five rows above and three columns to the left. Conversely, R[5]C[3] references the cell that appears five rows below and three columns to the right. If you omit the brackets, it 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. 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 remains true regardless of the types of cell references 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, it's probably incorrect.
However, you can take advantage of the background formula auditing feature, which can flag potentially incorrect formulas. I discuss this feature in Chapter 21.
|
Referencing Other Sheets or Workbooks
A formula can use references to cells and ranges that are in a different worksheet. To refer to a cell in a different worksheet, precede the cell reference with the sheet name followed by an exclamation point. Note this 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, like this:
=[Budget.xlsx]Sheet1!A1+1
If the workbook name or sheet name in the reference includes one or more spaces, you must enclose it (and the sheet name) in single quotation marks. For example
='[Budget Analysis.xlsx]Sheet1'!A1+A1
If the linked workbook is closed, you must add the complete path to the workbook reference. For example
='C:\MSOffice\Excel\[Budget Analysis.xlsx]Sheet1'!A1+A1
Although you can enter link formulas directly, you also can create the reference by using normal pointing methods discussed earlier. To do so, make sure that the source file is open. Normally, you can create a formula by pointing to results in relative cell references. But, when you create a reference to another workbook by pointing, Excel creates absolute cell references. If you plan to copy the formula to other cells, you must edit the formula to make the references relative.
| Caution | Working with links can be tricky and may cause some unexpected problems. For example, if you use the Office Button |
EAN: 2147483647
Pages: 212