Absolute vs Relative References in Excel: What the $ Sign Actually Does

 

Absolute vs Relative References: What the $ Sign Actually Does

A relative reference (like A2) shifts automatically when you copy or drag a formula elsewhere, while an absolute reference (like $A$2) stays locked to that exact cell no matter where the formula goes. The dollar sign is what tells Excel to lock a reference in place, and mixing the two correctly is what lets one formula work properly when copied across an entire column or row.

Years ago I built a commission calculator that multiplied each salesperson's revenue by a commission rate sitting in one fixed cell, say F1. I wrote the formula for row 2, dragged it down 200 rows the way I always did, and didn't think to check it closely because the first few results looked plausible. It wasn't until someone further down the list got a commission figure of literally zero dollars that I went back and realised the formula had quietly shifted its reference to the commission rate cell on every single row, pointing at an empty cell by about row 50 onward. A whole afternoon's work, and the fix was one dollar sign I'd forgotten to type in the very first formula.

What's the actual difference between A2 and $A$2?

• A2 is a relative reference. Copy or drag a formula containing A2 down one row, and it becomes A3. Drag it across one column, and it becomes B2. It shifts along with wherever the formula lands.
• $A$2 is an absolute reference. Copy or drag a formula containing $A$2 anywhere at all, and it stays exactly $A$2 — the dollar signs lock both the column letter and the row number in place.

This is exactly the mistake from my commission story above — I'd written =B2*F1 for row 2, dragged it down, and F1 (my commission rate) quietly became F2, F3, F4, and so on, instead of staying locked to F1 where the actual rate lived.

What does a mixed reference like $A2 or A$2 mean?

This is the part that trips people up most, and it's exactly what came up in my posts on "SUMIF, COUNTIF and SUMIFS" and "Conditional Formatting". A mixed reference locks only one part — either the column or the row — while leaving the other free to shift.

• $A2 locks the column (A), but lets the row number shift as you drag down or up
• A$2 locks the row (2), but lets the column letter shift as you drag left or right

This is genuinely useful when building something like a two-way lookup or a multiplication table, where you want a formula to reference the same column consistently down a set of rows, but still reference a changing row header across the columns, or vice versa.

Reference	Column locked?	Row locked?	Shifts when dragged down	Shifts when dragged across
A2	No	No	Yes	Yes
$A2	Yes	No	Yes	No
A$2	No	Yes	No	Yes
$A$2	Yes	Yes	No	No

How do I quickly switch between reference types without retyping the dollar signs?

Click into the cell reference within a formula (with your cursor sitting right in or next to it) and press F4. Each press cycles through the four combinations in order: $A$2, A$2, $A2, A2, and back to $A$2 again. This is far quicker than manually typing dollar signs, especially once you're used to reaching for it automatically while building a formula.

Why would I want to lock a reference on purpose?

Any time a formula needs to keep pointing at the same fixed cell while being copied or dragged elsewhere — a tax rate, a commission percentage, a fixed conversion factor, or a single lookup range that shouldn't expand or shift as the formula moves down a column.

Calculating GST-inclusive prices across a whole column, where the GST rate sits in a single fixed cell F1:

=B2*(1+$F$1)

Dragged down any number of rows, this always multiplies by whatever's in F1, rather than sliding down to F2, F3, and so on the way a plain relative reference would.

Why do lookup formulas like VLOOKUP or INDEX/MATCH usually need absolute references?

If you've read my post on "VLOOKUP vs XLOOKUP" you'll have seen lookup ranges written with dollar signs, like VLOOKUP(A2, $B$2:$D$200, 3, FALSE). The lookup value (A2) needs to shift as you drag the formula down to check each row, but the range you're searching within ($B$2:$D$200) needs to stay fixed, or it'll quietly shrink or shift out from under your data with every row you drag across, the same way my commission rate cell did.

FAQ

Does pressing F4 work the same way on a laptop without a dedicated F4 key? On many laptops you'll need Fn+F4 instead, since the function keys often double up with other shortcuts by default.

Can I lock only part of a range, like the start but not the end? Yes — each cell reference within a range can be locked independently, so $B$2:B200 locks the start of the range but lets the end shift, which is occasionally useful for a formula meant to grow with new data added below it.

Why does my formula still shift even though I added dollar signs? Double-check the dollar signs are actually in front of both the column letter and row number where you intended — a common mistake is adding them to only one part, like B$2 instead of $B$2, then being surprised when dragging sideways still shifts the column.

Do absolute references work the same way when copying a formula versus dragging its fill handle? Yes — both copying and dragging follow the same reference rules. The behaviour depends on the dollar sign placement, not on which method you used to duplicate the formula.

Is there a performance difference between absolute and relative references? No meaningful difference for everyday spreadsheet sizes. This is purely about which cells a formula points to, not about how quickly it calculates.