Cell Referencing: Relative vs. Absolute

Before learning any complex function, there’s one concept that decides whether your model scales cleanly or falls apart the moment you drag a formula into a new cell: cell referencing.

Relative vs. Absolute

Type =B2*C2 and drag it down, and Excel automatically adjusts the references (B3*C3, B4*C4…) — that’s a relative reference, the default behavior.

But if you want a specific cell (say, a fixed tax rate in $B$1) to stay locked no matter where you drag the formula, you prefix it with $ before the column letter and row number: =B2*$B$1. That’s an absolute reference.

Type	Form	Changes when dragged?
Relative	A1	Yes, both column and row
Fully absolute	$A$1	No, fully locked
Row-locked only	A$1	Column changes, row locked
Column-locked only	$A1	Column locked, row changes

Key shortcut: press F4 while your cursor is inside a reference in a formula, and Excel cycles through all four types automatically.

Why does this matter so much in finance?

In any financial model, the same formula repeats across months or years (e.g., calculating monthly revenue). If your assumptions (growth rate, tax rate…) live in a single cell tied with an absolute reference, you can update the assumption once and the entire model refreshes automatically. Forget the $, and every row silently pulls the wrong number — one of the most common mistakes junior analysts make.

Core arithmetic functions

• =SUM(range) — sum a range of numbers.
• =AVERAGE(range) — the average.
• =COUNT(range) / =COUNTA(range) — count numeric cells / non-empty cells.
• =ROUND(number, digits) — round a number to a given number of decimal places.

In the next lesson we’ll pair referencing with logic and lookup functions like IF and VLOOKUP.