In Excel, cell references determine how a formula behaves when copied to other cells. There are three types of cell references: Absolute, Relative, and Mixed.
1. Relative Cell Reference (Default)
- Changes when copied to another cell.
- Adjusts based on the new location.
- Example:
=A1+B1
- If copied from C1 to C2, it automatically updates to A2+B2.
When to Use?
* When you want the formula to adjust dynamically when copied.
2. Absolute Cell Reference ($)
- Fixed reference that does not change when copied.
- Uses a $ (dollar sign) before the row/column.
- Example:
=$A$1+B1
- If copied to C2, it remains $A$1+B2 (A1 stays fixed, but B2 changes).
When to Use?
* When you always want to refer to a fixed cell (e.g., tax rates, constants).
3. Mixed Cell Reference ($ in one part)
- Partially locked: Either the row or the column stays fixed.
- Two types:
- $A1 → Column A is fixed, but the row changes.
- A$1 → Row 1 is fixed, but the column changes.
- Example:
=$A1+B$1
- If copied, A stays fixed, but row changes.
- Row 1 stays fixed, but column changes.
When to Use?
* When copying across rows or columns but needing part of the reference to stay the same.
Comparison Table
| Reference Type |
Example |
Changes When Copied? |
| Relative |
A1 |
Yes (both row & column adjust) |
| Absolute |
$A$1 |
No (stays fixed) |
| Mixed (Column Fixed) |
$A1 |
Row changes, but column is fixed |
| Mixed (Row Fixed) |
A$1 |
Column changes, but row is fixed |
