Using Relative and Absolute Addressing

When you enter a formula that contains cell references into a cell, Excel keeps track of it in two ways. The first is to record the value of that cell and use the value in the calculation. The second way is to keep track of the relative position of the cells in the formula to one another.

Here's how relative addressing works if you enter the formula =A1+A2 in cell A3.

If the formula could talk, it would say, "Take the cell two rows above me and add the value of that cell to the cell one row above me and display the results in my cell." If you copied the formula in A3 to C3, the new formula would read =C1+C2 . Why? Because the new formula would be looking at the cells one and two rows above it

Relative cell referencing is great if you're adding a number of like columns , such as the ones shown in Figure 44.9. Imagine how tedious it would be to have to create a formula in the total column for each day. With relative cell referencing, you create the formula in the first total column and then copy it to all the other days of the month.

Figure 44.9. The formula in each cell in the Totals row adds the hours in the corresponding daily column.

Relative cell referencing is Excel's default way of copying formulas. Sometimes, a formula needs to reference a cell location that remains constant. In the illustration in Figure 44.10, an hourly rate has been entered into the worksheet on cell A21. Accordingly, the formula in cell B23 for the daily billable total is =B19*A21 . The formula literally translates to "Take the total of the cell three rows above me and multiply by the value located two rows up and one column to my left."

Figure 44.10. The formula produces the daily billable rate.

Copying the formula to the other days of the month would produce incorrect amounts. Relative cell referencing dictates that the formula would always multiply the value of the cell three rows up with the cell located two rows up and one column to its left. However, the hourly rate is placed in one specific cell: A21. In this case, the formula would need to absolutely use cell A21. This type of addressing is called absolute addressing. An absolute address remains constant; it doesn't change if the formula is copied or moved to another location in the worksheet. The $ (dollar sign) character indicates absolute addressing; thus a cell reference written as $A$21 would always point to cell A21.

Mixed referencing allows you to keep either a row or column constant. The dollar-sign character placed next to the column indicator, as in $A1 , means that the column remains constant, but the row changes as the formula is copied. A $ next to the row indicator, as in A$1 , indicates that the column is relative, but the row shown in the cell address is absolute.

When you're entering a cell reference into a cell, you can type the dollar signs to make the reference absolute. Or if you're using the mouse to point to the cells you want to use in the formula, click the cell and press F4. The dollar sign character appears before both the column and row indicators, meaning the cell reference is absolute.

To make only the column or row portion of a cell address absolute, press F4 again. Each time you press the F4 key, the $ moves to a different coordinate of the cell address. For example, $A$1 becomes A$1 , $A1 , and so on each time you press the F4 key.