Absolute Cell Referencing

When you copy a formula from one cell to another, Excel automatically adjusts the column letters or row numbers of cell addresses. For example, suppose the formula =B5*C5 were entered in cell D5 and then copied to cells D6, D7 and D8. Cell D6 would then contain the formula =B6*C6 and cell D8 would contain =B8*C8. This is called relative referencing. Until now, all the formulas and functions you have used contained relative cell references.

Sometimes you might need to create a formula that refers to a cell in a fixed location on the worksheet. A reference that always specifies the same cell when it is copied is an absolute reference. Absolute references contain dollar signs before the column letter and the row number. For example, $B$5. You do not have to type the dollar signs, however. Pressing the F4 function key as you point to a cell when you are creating a formula, makes the cell reference absolute by entering the dollar signs for you.

To help you understand this, please print this page and then open Excel. Follow the instructions below.

1. Enter the labels and values shown in rows 4-9 below. Note row 9 should contain =sum functions to add the values in columns B and C.

absolute.gif (4904 bytes)

2. To compute the % of 1999 Sales, we need to divide each region's 1999 sales by the Total Sales for 1999. Enter this formula in cell E5: =B5/B9

3. Now copy this formula down column E. The result in cells E6..E8 will be ERR.

4. Place the pointer in cell E6 and look at the formula: =B6/B10. The first cell reference, B6, referring to South America's sales, is correct. But the second cell reference in the formula now refers to a blank cell. We actually want to divide each region's 1999 sales by the Total 1999 sales. Each formula in column E should divide by cell B9.

5. To correct this, place the pointer in cell E5 again and edit the formula by clicking on the Edit line. Place the insertion point near the B9 as shown. The insertion point can be in front of the B, after the B, or after the 9. Only the cell reference that the insertion point is on will become absolute when you hit F4.

6. Press function key F4. This will make the cell reference appear as $B$9.

7. Copy the formula down the column again. The worksheet should now appear like this. There are no more ERRs. Look at each formula in column E. Notice how each you are dividing by $B$9 in each formula now that B9 is an absolute reference.

absolut2.gif (5376 bytes)

You just learned how to fix a formula that should contain an absolute cell reference. To be honest with you, this is probably what you will use most of the time. I usually do because I don't realize a cell needs to be made absolute until I've copied the formula. But, suppose you DO know that a cell reference needs to be absolute before you enter the formula. You don't have to enter the incorrect formula and then go back and edit it like we just did. You can enter it correctly to begin with.

8. Delete all the formulas in column E and let's do it again. This time, we'll do it right the first time.

9. Place the pointer in cell E5. Type =. Then point to cell B5, then type the / for division. Now point to cell B9 and while you are pointing to cell B9, press function key F4. This will make the cell reference in the formula absolute.

10. Copy the formula down the column. You should get the same results you did before (see the last example above).

Absolute referencing allows you to copy formulas and not have to enter similar formulas over and over and over again. This is a great time saver. I will expect you to use absolute referencing whenever needed from now on. How will you know if it's needed? If you have to enter basically the same formula over and over because one cell in the formulas should always be the same, you should use an absolute reference. But remember, absolute cell referencing is only necessary if you are going to copy the formula.