Chapter 3: Relative and Absolute Cell References

Cell references are commonly used in the formulas compared to the actual numbers in the cells because you can easily change the numbers or values included in the calculation to instantly affect the formula results. If you use the actual numbers in the formula such as =20+10 instead of using cell references, such as =A1+A2, the change of numbers or values that are included in the formula will not affect the formula results. For example, if your formula refers to cells A1 and A2, such as =A1+A2, whenever you key in a new value in cell A1, the formula result changes to reflect the new value. Otherwise, you would need to edit the formula to the new value used in the formula. Therefore, you should use cell references in your formula except for cases where you know the number will not change or constant.

When you use a cell reference in a formula, you will have either relative or absolute cell references or a combination of both.

Relative cell references can change when you copy the formula to other cells by row or column. For example, if this formula, =B2*C2 is copied to other cells by row, Excel creates relative cell references in the formulas, =B3*C3, =B4*C4, =B5*C5, and so on. By default, Excel creates relative cell references in formulas.

Both relative and absolute cell references are significant only if you copy the formula to other cells.

Relative Cell References

To illustrate the relative cell references, create a blank workbook, and type the contents and the formula in cell D2 in their respective cells as shown in the Figure below.

Now, you are going to copy the formula in cell D2 and paste it to other cells in the same column, in this case to cells D3, D4, and D5.

Select cell D2.

Identify the Fill Handle, the small square in the lower right corner of cell D2.

See the Figure below.

Move your mouse pointer around the Fill Handle until it turns into a black cross.

See the Figure below.

As the mouse pointer turns into a crosshair, click, and hold your mouse button and drag it down to cells D3, D4, and D5.

See the Figure below.

When you release the mouse button on the last cell or cell D5 in this case, you will see the selected cells filled with the values depending on the formulas they have.

See the Figure below.

You have now copied the formula =B2*C2 in cell D2 and pasted it to cells D3, D4, and D5 using the Fill Handle. Ignore the floating button that appears on the last cell after the operation.

Excel has automatically filled up the formulas in those cells relative to the row headings. The formulas are =B3*C3 in cell D3 in row 3, =B4*C4 in cell D4 in row 4, and =B5*C5 in cell D5 in row 5.

See the Figures below.

Save your workbook with your preferred file name.

That’s all about relative cell references.

Absolute Cell References

Unlike the relative cell references, the absolute cell references do not change when you copy the formula to other cells by row or column. These cell references are preceded by a dollar sign when they are written in the formula, such as =$A$5. This way, you can have the same cell reference in your formula regardless of the cell position.

Let’s learn how you can use absolute cell references in formulas.

Open the workbook you have constructed earlier if you have closed it. Type Sales Tax Rate in cell A8, 5% in cell C8, and Sales Tax in cell E1.  Depending on your Excel version, the content in cell E1 may be automatically bold.

See the Figure below.

You will create a formula to calculate the Sales Tax amount for each item based on the Sales Tax Rate, 5% in cell C8. That means cell C8 will be used as an absolute cell reference in the formula by inserting dollar signs into the cell reference, $C$8.

For example, the Sales Tax amount for item Tables in cell E2 is $56.25 ($1,125.00 x 5%), and the formula is written as =D2*$C$8. Let’s type this formula in cell E2.

See the Figure below.

After typing the formula in cell E2, press Enter to get the result. You should get $56.25 in cell E2. Click cell E2 to make it active, then use the Fill Handle as described earlier to copy the formula in cell E2 and paste it to other cells in the same column, namely cells E3, E4, and E5.

The results show that all the remaining cells E3, E4, and E5 have cell $C$8 in the formulas. This is because cell $C$8 has been made an absolute cell reference. So, when you copy the formula in cell E2 and paste it to other cells, cell $C$8 stays in the formulas.

See the Figures below.

Note that, though the absolute cell reference, $C$8 stays in the formula, the other cell references change because they are relative to the row headings or row numbers.

That’s all for this Chapter.

You’re awesome!