Using Cell References in Formulas:

A cell reference identifies a cell or a group of cells in a workbook. When you include cell references in a formula, the formula is linked to the referenced cells. The resulting value of the formula is dependent on the values in the referenced cells and changes automatically when the values in the referenced cells change.

To see cell referencing at work, select cell A1 and type the formula =10*2. Now select cell A2, and type the formula =A1. The value in both cells is 20. If at any time you change the value in cell A1, the value in cell A2 changes also. Now select cell A3, and type =A1+A2. Excel returns the value 40. Cell references are especially helpful when you create complex formulas.

Entering Cell References with the Mouse:

You can save time and increase accuracy when you enter cell references in a formula by selecting them with the mouse. For example, to enter references to cells A9 and A10 in a formula in cell B10, do the following:

1 Select cell B10, and type an equal sign.
2 Click cell A9, and type a plus sign.
3 Click cell A10, and press Enter.

When you click each cell, a marquee surrounds the cell and a reference to the cell is inserted in cell B10. After you finish entering a formula, be sure to press Enter. If you do not press Enter and select another cell, Excel assumes that you want to include the cell reference in the formula.

The active cell does not have to be visible in the current window for you to enter a value in that cell. You can scroll through the worksheet without changing the active cell and click cells in remote areas of your worksheet, in other worksheets, or in other workbooks, as you build a formula. The formula bar displays the contents of the active cell, no matter which area of the worksheet is currently visible.

Tip: Redisplay the active cell.

If you scroll through your worksheet and the active cell is no longer visible, you can redisplay it by pressing Ctrl+Backspace.

Understanding Relative, Absolute, and Mixed References:

Relative references — the type we’ve used so far in the sample formulas — refer to cells by their position in relation to the cell that contains the formula, such as “the cell two rows above this cell.” Absolute references refer to cells by their fixed position in the worksheet, for example, the cell located at the intersection of column A and row 2. A mixed reference contains a relative reference and an absolute reference, for example, the cell located in column A and two rows above this cell. Absolute and mixed references are important when you begin copying formulas from one location to another in your worksheet. When you copy and paste, relative references adjust automatically, while absolute references do not.

A relative reference to cell A1, for example, looks like this: =A1. An absolute reference to cell A1 looks like this: =$A$1. You can combine relative and absolute references to cell A1 to create these mixed references: =$A1 or =A$1. If the dollar sign precedes only the letter (A, for example), the column coordinate is absolute and the row is relative. If the dollar sign precedes only the number (1, for example), the column coordinate is relative and the row is absolute.

When you enter or edit a formula, press F4 to change reference types quickly. The following steps show how:

1 Select cell A1, and type =B1+B2 (but do not press Enter).
2 Press F4 to change the reference nearest to the flashing cursor to absolute. The formula becomes =B1+$B$2.
3 Press F4 again to change the reference to mixed (relative column coordinate and absolute row coordinate). The formula becomes =B1+B$2.
4 Press F4 again to reverse the mixed reference (absolute column coordinate and relative row coordinate). The formula becomes =B1+$B2.
5 Press F4 again to return to the original relative reference.

Creating References to Other Worksheets in the Same Workbook:

You can refer to cells in other worksheets within the same workbook just as easily as you refer to cells in the same worksheet. For example, to enter a reference to cell A9 in Sheet2 into cell B10 in Sheet1, do this:

1 Select cell B10 in Sheet1, and type an equal sign.
2 Click the Sheet2 tab.
3 Click cell A9, and then press Enter.

After you press Enter, Sheet1 is made active. Select cell B10, and you will see that it contains the formula =Sheet2!A9.

The worksheet portion of the reference is separated from the cell portion by an exclamation point. Note also that the cell reference is relative, which is the default when you select cells to create references to other worksheets.

Creating References to Worksheets in Other Workbooks:

You can refer to cells in worksheets in separate workbooks in the same way that you refer to cells in other worksheets within the same workbook. These references are called external references.

For example, to enter a reference to Book2 into Book1, follow these steps:

1 Create a new workbook—Book2—by clicking the New button on the Standard toolbar.
2 Choose Window, Arrange, Vertical.
3 Select cell A1 in Sheet1 of Book1, and type an equal sign.
4 Click anywhere in the Book2 window to make the workbook active.
5 Click the Sheet2 tab at the bottom of the Book2 window.
6 Click cell A2.
7 Press Enter to lock in the reference.

Understanding Row-Column Reference Style:

In R1C1 reference style, both rows and columns are numbered. The cell reference R1C1 means row 1, column 1; therefore, R1C1 and A1 refer to the same cell. Although R1C1 reference style isn’t widely used anymore, it was the standard in some spreadsheet programs, such as Multiplan. The normal reference style in Excel assigns letters to columns and numbers to rows, such as A1 or Z100. To turn on the R1C1 reference style, choose Tools, Options, click the General tab, and select the R1C1 Reference Style option. The cell references in all your formulas automatically change to R1C1 format. For example, cell M10 becomes R10C13, and cell IV65536, the last cell in your worksheet, becomes R65536C256. In R1C1 notation, relative cell references are displayed in terms of their relationship to the cell that contains the formula rather than by their actual coordinates. This can be helpful when you are more interested in the relative position of a cell than in its absolute position.

For example, suppose you want to enter in cell R10C2 (B10) a formula that adds cells R1C1 (A1) and R1C2 (B1). After selecting cell R10C2, type an equal sign, select cell R1C1, type a plus sign, select cell R1C2, and then press Enter. Excel displays =R[-9]C[-1]+R[-9]C. Negative row and column numbers indicate that the referenced cell is above or to the left of the formula cell; positive numbers indicate that the referenced cell is below or to the right of the formula cell. The brackets indicate relative references. This formula reads, “Add the cell nine rows up and one column to the left to the cell nine rows up in the same column.” A relative reference to another cell must include brackets. Otherwise, Excel assumes you’re using absolute references. For example, the formula =R8C1+R9C1 uses absolute references to the cells in rows 8 and 9 of column 1.

How Copying Affects Cell References:

One of the handiest things about using references is the capability to copy and paste formulas. But you need to understand what happens to your references after you paste so that you can create formulas with references that operate the way you want them to.

Copying Relative References:

When you copy a cell containing a formula with relative cell references, the references change automatically, relative to the position of the cell where you paste the formula. suppose you type the formula =AVERAGE(B4:E4) in cell F4. This formula averages the values in the four-cell range that begins four columns to the left of cell F4. (see this example in file Exam.xls.)

You want to repeat this calculation for the remaining rows as well. Instead of typing a new formula in each cell in column F, you select cell F4 and choose Edit, Copy. Then you select cells F5:F8, choose Edit, Paste Special, and then select the Formulas and Number Formats option (to preserve the formatting). Because the formula in cell F4 contains a relative reference, Excel adjusts the references in each copy of the formula. As a result, each copy of the formula calculates the average of the cells in the corresponding row. For example, cell F7 contains the formula =AVERAGE(B7:E7).

Copying Absolute References:

If you want cell references to remain the same when you copy them, use absolute references.

For example, if cell B2 contains the hourly rate at which employees are to be paid, and cell C5 contains the relative reference formula =B2*B5. Suppose that you want to copy the formula in C5 to the range C6:C8. Now if you copy the existing formula to this range: You get erroneous results. Although the formulas in cells C6:C8 should refer to cell B2, they don’t. For example, cell C8 contains the incorrect formula =B5*B8. The problem is that the formula in cell C5 contains relative references. We copied the relative formula in cell C5 to cells C6:C8, producing incorrect results. (You can find the sample file used in this example in Wages.xls.) Because the reference to cell B2 in the original formula is relative, it changes as you copy the formula to the other cells. To correctly apply the wage rate in cell B2 to all the calculations, you must change the reference to cell B2 to an absolute reference before you copy the formula. To change the reference style, click the formula bar, click the reference to cell B2, and then press F4. The result is the following formula: =$B$2*B5. When you copy this modified formula to cells C6:C8, the second cell reference, but not the first, is adjusted within each formula. For example, cell C8 now contains the correct formula: =$B$2*B8.

Copying Mixed References:

You can use mixed references in your formulas to anchor a portion of a cell reference. (In a mixed reference, one portion is absolute and the other is relative.) When you copy a mixed reference, Excel anchors the absolute portion and adjusts the relative portion to reflect the location of the cell to which you copied the formula. To create a mixed reference, you can press the F4 key to cycle through the four combinations of absolute and relative references—for example, from B2 to $B$2 to B$2 to $B2. The loan payment example (Loan.xls) uses mixed references (and an absolute reference). You need to enter only one formula in cell C6 and then copy it down and across to fill the table. Cell C6 contains the formula = –PMT ($B6,$C$3,C$5) to calculate the annual payments on a $10,000 loan over a period of 15 years at an interest rate of 6 percent. We copied this formula to cells C6:F10 to calculate payments on three additional loan amounts using four additional interest rates.

The first cell reference, $B6, indicates that we always want to refer to the values in column B but the row reference (Rate) can change. Similarly, the mixed reference, C$5, indicates that we always want to refer to the values in row 5 but the column reference (Loan Amount) can change. For example, cell E8 contains the formula =–PMT ($B8,$C$3,E$5). Without mixed references, we would have to edit the formulas manually in each of the cells in the range C6:F10.