The Difference Between Relative & Absolute Addressing in Excel

Relative Addressing

When copying cells with formula answers Excel automatically calculates cells in different positions relative to the original formula e.g. A1+A2 copies to the right B1+B2 and C1+C2

1. Create the following worksheet:

2. Notice there is a formula in cell B6

3. It is possible to copy the formula to the right so that the same calculation is performed relative to the product prices in February and March

4. Hover the mouse pointer over the bottom right-hand corner of the active B6 cell

5. Click and drag to the right until you reach cell D6

6. This will copy the formula across the table

Absolute Addressing

Absolute Addressing keeps the cell reference the same when calculating formulas. It is used when you want to include a single cell in a formula and use this to calculate many cells in a range of data.

1. It is expressed using the dollar sign: $C$4

2. Create the following worksheet:

3. We want to calculate the interest on a savings account based on the amount saved

4. To do this, we need to multiply the interest rate in C4 by each of the amounts

5. Instead of typing out the formula for each amount, we can use absolute addressing

6. In cell B4 enter the following formula:

=A4*$C$4

7. Press the Enter key

8. The value should be €18

9. Hover the mouse pointer over the bottom right hand corner of the active cell

10. Click and drag down the formula to copy it to cells B4:B7

11. The resulting table should look like this:

12. Save the worksheet as 'Savings Accounts'

For more ICDL Excel tips, click on the paperback cover below: