Be able to replicate formulae using absolute and relative cell references where appropriate

Topic 20 – Spreadsheets

Learning Objective

Be able to replicate formulae using absolute and relative cell references where appropriate.

1. Cell References

In a spreadsheet a cell reference tells a formula where to find the data it needs. There are three types of references:

• Relative reference – changes when the formula is copied to another cell (e.g., A1).
• Absolute reference – remains constant when the formula is copied (e.g., \$A\$1).
• Mixed reference – either the column or the row is fixed (e.g., \$A1 or A\$1).

2. Why Use Absolute References?

Absolute references are required when a formula must always refer to the same cell, such as a tax rate, a fixed price, or a constant multiplier.

Example: If the tax rate is stored in cell B1 and you want to calculate tax for many items, the formula in column C would be =A2*\$B\$1. When the formula is copied down, A2 changes to A3, A4, … but \$B\$1 always stays the same.

3. Replicating Formulae

1. Select the cell that contains the original formula.
2. Drag the fill handle (small square at the bottom‑right corner) across the range where the formula is required.
3. Excel automatically adjusts relative references and keeps absolute references unchanged.

4. Example Scenario

A small shop records the quantity sold and the unit price of three products. The shop wants to calculate:

• Total price for each product (Quantity × Unit Price).
• VAT (Value Added Tax) at a fixed rate of 20 % stored in cell E1.
• Grand total (Total price + VAT) for each product.

5. Sample Spreadsheet Layout

6. Demonstrating the Copy Process

Assume the formula =B2*C2 is entered in D2. When the fill handle is dragged down to D3 and D4, the formulas become:

• D3 = B3*C3
• D4 = B4*C4

Because the references are relative, the row numbers adjust automatically.

For the \cdot AT formula =D2*\$E\$1 in F2, copying down yields:

• F3 = D3*\$E\$1
• F4 = D4*\$E\$1

Here \$E\$1 stays fixed, ensuring the same tax rate is applied to every row.

7. Common Pitfalls

• Forgetting the $ signs results in the tax rate changing when the formula is copied.
• Using absolute references for the quantity or price cells (e.g., \$B\$2) prevents the formula from adapting to each row.
• Mixed references are useful when copying across columns but keeping the same row (e.g., A$1).

8. Quick Reference Cheat‑Sheet

9. Practice Exercise

Complete the following steps in a spreadsheet:

1. Enter the data shown in the table above (excluding the formula column headings).
2. In cell D2 type =B2*C2 and copy the formula down to D5.
3. Enter the \cdot AT rate 0.15 in cell E1.
4. In cell F2 type =D2*\$E\$1 and copy down to F5.
5. In cell G2 type =D2+F2 and copy down to G5.
6. Check that the \cdot AT column always uses the value from E1 even after copying.

10. Suggested Diagram