Cell References - Copy formulas correctly
Understand which parts of a cell address move and which stay fixed. Build calculations you can copy across a budget or down a report.
By the end, you can
- Read cell addresses and ranges.
- Choose relative, absolute, or mixed references.
- Predict a formula's references after copying or filling.
Before you start: Know how to enter a number and start a formula with =.
In this lesson
A cell reference is an address
A reference tells Excel where to read a value. Columns run vertically and have letters: A, B, C, and later AA, AB. Rows run horizontally and have numbers. B3 means column B, row 3. The address is not the value inside the cell.
=B2*C2Read the number in B2 and multiply it by the number in C2. If either input changes, the result recalculates. You do not need to type the amounts into the formula again.
A colon joins the endpoints of a range. B2:B5 includes four cells in column B; B2:C5 includes both columns across rows 2 to 5. A reference can be used in arithmetic or inside a function such as SUM.
Relative references: move with the formula
A reference without dollar signs, such as A1, is relative. When a formula is copied one row down, its relative row numbers increase by one. When copied one column right, its relative column letters advance by one. Filling follows the same rule.
₹1,500| A | B | C | D | |
|---|---|---|---|---|
| 1 | Department | Budget | Spent | Remaining |
| 2 | Operations | ₹20,000 | ₹18,500 | ₹1,500 |
| 3 | IT | ₹12,000 | ₹13,200 | −₹1,200 |
| 4 | Marketing | ₹15,000 | ₹14,000 | ₹1,000 |
=B2-C2Copy or fill D2 down to D3: Excel changes the formula to =B3-C3. In D4 it becomes =B4-C4.
Absolute references: keep one input fixed
An absolute reference locks both parts of an address: $A$1. The dollar sign before A fixes the column; the dollar sign before 1 fixes the row. Copying the formula changes neither. This locks the address, not the cell's value: editing that input still updates every dependent formula.
₹3,000| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Salary | Allowance | Rate | |
| 2 | Anika | ₹30,000 | ₹3,000 | 10% | |
| 3 | Dev | ₹42,000 | ₹4,200 | ||
| 4 | Meera | ₹25,000 | ₹2,500 |
=B2*$E$2In C3 the formula becomes =B3*$E$2; in C4 it becomes =B4*$E$2. Salary changes by row, but the percentage stays at E2.
- Enter the salaries in B2:B4 and 10% in E2.
- Enter =B2*$E$2 in C2, then copy or fill down through C4.
- Inspect C3: B3 should move, while $E$2 should remain fixed.
- Change E2 to 12%. The allowances become ₹3,600, ₹5,040, and ₹3,000 without editing the formulas.
Mixed references: fix just a row or column
- $A1 fixes column A but lets the row change. One row down it becomes $A2; one column right it remains $A1.
- A$1 fixes row 1 but lets the column change. One column right it becomes B$1; one row down it remains A$1.
₹500| A | B | C | D | |
|---|---|---|---|---|
| 1 | Base budget | 5% | 10% | 15% |
| 2 | ₹10,000 | ₹500 | ₹1,000 | ₹1,500 |
| 3 | ₹20,000 | ₹1,000 | ₹2,000 | ₹3,000 |
=$A2*B$1$A2 always reads column A but follows the budget row. B$1 always reads row 1 but follows the percentage column. C2 becomes =$A2*C$1; B3 becomes =$A3*B$1; C3 becomes =$A3*C$1.
Predict a copied reference
Suppose you copy a formula two columns right and two rows down. Apply that movement only to the parts without dollar signs:
- A1 → C3: both parts move.
- $A$1 → $A$1: neither part moves.
- $A1 → $A3: only the row moves.
- A$1 → C$1: only the column moves.
Switch reference types with F4
- In Excel for Windows, edit the formula and place the caret in the reference you want to change, or select that reference.
- Press F4 to cycle A1 → $A$1 → A$1 → $A1 → A1.
- Stop at the form you need, then press Enter to confirm. Some laptops require Fn + F4.
Common mistakes and fixes
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.