Cell References in Excel: The Idea Every Formula Needs
What cell references are, why you point at cells instead of typing numbers, and how relative, absolute and mixed references behave when copied.
By Ayoub Farid · · 2 min read
If you understand cell references, Excel stops being a grid of numbers and becomes a calculator that updates itself. It's the first idea in every course for a reason.
What a cell reference is
Every cell has an address: column letter, then row number. A formula can point at that address instead of a fixed number.
| A | B | |
|---|---|---|
| 1 | Item | Price |
| 2 | Espresso | 2.50 |
| 3 | Latte | 3.50 |
| 4 | Tea | 2.00 |
=B3+B4This returns 5.50, the price of a Latte plus a Tea. Change B3 to 3.80 and the answer becomes 5.80 by itself.
Why typing numbers is a mistake
=3.50+2.00 also returns 5.50. But it's frozen. When prices change, it's silently wrong, and nobody can see where 3.50 came from. In job tests, a typed number usually counts as a fail, even when the result is right.
Ranges: many cells at once
A range is first cell, colon, last cell. B2:B4 means B2, B3 and B4. Functions use ranges:
=SUM(B2:B4)You can also reference whole columns (B:B) or rows (2:2), but explicit ranges are clearer for beginners.
The three kinds of reference
When you copy a formula, Excel adjusts references. How it adjusts depends on the $ signs.
| Reference | Copied down one row | Copied right one column |
|---|---|---|
B2 (relative) | B3 | C2 |
$B$2 (absolute) | $B$2 | $B$2 |
$B2 (column locked) | $B3 | $B2 |
B$2 (row locked) | B$2 | C$2 |
Relative is the default and right most of the time: revenue in row 3 should use row 3's price. Absolute is for one fixed cell every row needs, like a total or a tax rate. Read absolute references and mixed references for full examples.
References to other sheets
To point at another worksheet, add its name and an exclamation mark:
=Prices!B3If the sheet name has spaces, wrap it in single quotes: ='Price list'!B3.
Common mistakes
- Referencing the header.
=SUM(B1:B13)includes "Sales" text. SUM ignores text, but other formulas may not. - Range one row short.
=SUM(B2:B12)when data goes to B13. Always check the last row. - Referencing the answer cell itself. That creates a circular reference.
Practise it
Pointing at cells is the very first lesson in Cellio. Instead of typing B3 on a phone keyboard, you tap the cell to add it to the formula, and tap a second cell to make a range. Answers are checked on hidden versions of the data, so a typed-in number can't pass.
Frequently asked questions
What is a cell reference in Excel?
The address of a cell, like B3, used in a formula so the formula reads whatever that cell contains.
What is the difference between relative and absolute references?
Relative references change when you copy the formula; absolute references with $ stay fixed.
How do I reference a cell in another sheet?
Write the sheet name, an exclamation mark and the cell: =Sheet2!B3.
Why does my formula show the wrong cells after copying?
The references moved as relative references. Lock the ones that shouldn't move with $.