Lesson 3.2 – Relative vs Absolute References

When you copy a formula in Excel, the cell references inside it may change automatically. This behavior is extremely powerful, but only if you understand how it works. In this lesson, you will learn the difference between relative, absolute, and mixed references.

---

1. What Are Cell References?

A cell reference tells Excel where to find the data used in a formula.

Examples:

• A1 → column A, row 1
• B5 → column B, row 5
• C10 → column C, row 10

When you copy a formula, Excel may adjust these references automatically.

---

2. Relative References (A1)

Relative references change when a formula is copied to another cell. This is the default behavior in Excel.

Example:

=A1 + B1

If you copy this formula from row 1 to row 2, it becomes:

=A2 + B2

Excel “moves” the references based on the direction of the copy.

---

3. Absolute References ($A$1)

Absolute references do not change when copied. You lock the column and the row using the dollar sign ($).

Example:

=$A$1 * B1

If you copy this formula down or across, $A$1 never changes.

Use absolute references when you need to refer to a fixed value, such as:

• VAT rate
• Exchange rate
• Constant multiplier
• Fixed cell in a table

---

4. Mixed References ($A1 or A$1)

Mixed references lock either the column or the row.

• $A1 → column A is locked, row changes
• A$1 → row 1 is locked, column changes

These are useful in advanced tables, especially when building multiplication tables or structured models.

---

5. Quick Reference Table

Type	Example	Behavior
Relative	A1	Changes when copied
Absolute	$A$1	Never changes
Mixed	$A1 / A$1	Locks either row or column

---

6. Practical Example

Suppose you want to calculate the price including VAT for a list of products.

• VAT rate is in cell D1
• Product prices are in column B

Correct formula:

=B2 * $D$1

When copied down, B2 becomes B3, B4, B5… But $D$1 stays fixed.

---

7. Practical Exercise

Practice relative and absolute references with the following steps:

1. Create a worksheet named Lesson_3_2_Practice.
2. Enter a list of prices in column A.
3. Enter a VAT rate in cell C1.
4. In column B, calculate price including VAT using =A2 * $C$1.
5. Copy the formula down and observe how references behave.
6. Create a small multiplication table using mixed references.

---

Next Lesson

Lesson 3.3 – Math Functions (SUM, AVERAGE, MIN, MAX)