So far, you have used cell references in formulas that can be automatically adjusted as the formula is copied from cell to cell. In general, when you include cell references in formulas, Excel assumes that you wish the references to be adjusted relative to their new location in the worksheet. For example, in the formula =C4*(2+0.5*E4), the references to cells C4 and E4 will be automatically adjusted if the formula is copied to other cells. This type of reference is called a relative reference.
However, you may not want this formula adjustment to be automatic. For instance, you might have a worksheet where the current interest rate is always placed in cell C5. Suppose many other cell formulas are referenced to use the interest rate in cell C5. As you extend the worksheet by copying formulas from one location to another, you will want to keep the reference to cell C5 the same in all formulas. In other words, you want it to be absolutely the same. This type of reference, then, is called an absolute reference.
An absolute reference is a cell address that Excel retains in fixed and unchanged form as a result of changes made with copy operations. The convention used to indicate an absolute reference is the $ (dollar) symbol. This usage has nothing to do with currency; it is just a symbol meaning “absolute.” For example, $C$5 is an absolute reference to cell C5 on the current worksheet.
If only part of the reference is absolute, it is called a mixed reference. Excel provides the following types of mixed references:
• Only the column reference is set to absolute by adding $ to the column reference.
Example: $C5 fixes the column as absolute, but allows the sheet and row address to be
adjusted.
• Only the row reference is set to absolute by adding $ to the row reference.
Example: C$5 fixes the row as absolute, but allows the sheet and column address to be
adjusted.
Because Excel workbooks are three-dimensional, a cell reference also includes a sheet
reference. If you are referencing a particular worksheet, Excel always routinely sets the name
of the sheet to absolute reference. For example, the formula Sheet2!A4–Sheet1!D6 subtracts the contents of cell D6 on Sheet1 from that of cell A4 on Sheet2.
To make a cell reference absolute (or mixed), you can type the $ symbol where needed as you type or edit a formula. Alternatively, you can press the F4 (Absolute) key immediately after you have typed the cell address in the entry line. Excel inserts the $ symbols into the cell address at the left of the cursor location. You will have an opportunity to practise this technique in Exercise 6-5.
EXERCISE 6-5
Using absolute and mixed references
In this exercise, you will enter a formula using absolute references and see their effect on copy operations.
1. Open a new worksheet. 2. Enter these values as listed:
3. In cell A3, enter the formula =A1+A2 resulting in the value of 30.
4. Copy the formula in cell A3 to cell B3. Notice that the formula in cell B3 reads =B1+B2 and displays the result of 300. This example shows the use of relative cell references in a simple formula.
5. In cell A4, enter the formula =$A$1+A2 by typing each of the characters directly from the keyboard. This formula includes an absolute reference to cell A1. The value displayed should be 30.
6. Copy the formula from cell A4 to B4. Move the cell pointer to B4 to read the formula in the Edit line. The formula in B4 now reads =$A$1+B2, showing that the absolute reference to A1 is unchanged but the original reference to cell A2 has been adjusted to B2 by the copy operation. The value displayed should be 210.
7. In cell A5, enter the formula =$A1+A2, which should display the value 30. $A1 is a mixed reference; it specifies that column A is absolute, but the row reference to row 1 is relative and may be adjusted.
8. Copy the formula from cell A5 to cell B6 (not B5). Click cell B6 and verify the result of =$A2+B3. This result indicates that Excel has adjusted the relative row reference of $A1 to $A2 (that is, one row down from row 1, which is what happened when you copied from row 5 to row 6). However, it left the absolute column reference to A unchanged. Both the column and row references of the relative cell reference to A2 were routinely adjusted to column B and row 3. The resulting value is 320.
9. In cell A6, enter the formula =A$1+A2 and copy this formula to cell B7, resulting in a value of 400. A$1 specifies a relative column reference to column A, but an absolute row reference to row 1. The formula in cell B7 should read =B$1+B3, indicating that the absolute row reference is unchanged.
10. In the Formulas ribbon, click on Show Formulas to display the worksheet formulas (Exhibit 6-6). Study the relative, absolute, and mixed references used in the worksheet.
EXHIBIT 6-6
Absolute and relative references
11. Click on Show Formulas again to turn off formula display. Leave this worksheet open for the next exercise.
EXERCISE 6-6
Using the F4 key
In this exercise, you will learn to use the F4 key to create absolute cell references and simplify your typing tasks. You should start with the same worksheet as at the end of Exercise 6-5. 1. Double-click cell A3. You are now in Edit mode. Cell A3 displays the formula:
=A1+A2
2. Press HOME to move the edit cursor to the beginning of the line. Press RIGHT ARROW to move the cursor to just before A1.
3. Press F4 once; by doing this you change the reference to cell A1 to absolute (notice the $ in front of the column and row):
=$A$1+A2
4. Press F4 again; by doing this you change the cell reference to a mixed reference, with row fixed, but column set to relative:
=A$1+A2
5. Press F4 again; the row is now set to be relative: =$A1+A2
6. Press F4; both column and row are now set to be relative: =A1+A2
7. Press ESC to abandon the changes to cell A3.
8. Close this worksheet without saving changes.