Absolute vs Relative References in Excel: What the Dollar Sign Locks

1. The fork: should this part move, or stay?

Before the explanation, one question worth answering from memory. Think of a formula you have dragged down a column. Say which parts of it needed to point somewhere new on each row, and which parts needed to keep pointing at the same place.

That question is the whole decision, and it gets asked once per reference in the formula. There are two possible answers.

Answer one: it should move. The reference points at something that belongs to this row. The amount for this order, the date of this order, the name on this line. If that is true, you want the reference to shift as the formula is copied, and you leave it alone. This is called a relative reference, and it is what you get by default.

Answer two: it should stay. The reference points at one thing that every row shares. A commission rate, a tax percentage, an exchange rate, the top-left corner of a lookup table. If that is true, you want every copy of the formula to ask the same cell, and you write it as $G$1. This is an absolute reference.

What decides between them is not the formula and not the cell. It is the sentence you would say out loud about that reference. "The amount on this row" moves. "The rate" stays.

It matters because getting it backwards does not usually produce an error message. It produces a number, and the next section is about why that number is often believable.

2. What a relative reference actually stores

Here is the fact that makes the rest of the page obvious rather than memorized. Type =D2 into cell E2. Excel does not remember "D2". It remembers "one cell to my left".

Copy that formula to E5 and it still means one cell to my left, which is now D5. The formula did not know it said D2. It only ever knew the distance.

Say it out loud, because it is the sentence the whole topic hangs off: a reference is a direction and a distance, not an address.

That is exactly why filling a column works at all. You write one formula for row 2 and copy it to row 17, and each copy quietly points at its own row, because "one cell to my left" is true on every row.

A dollar sign switches off that behavior for the half of the address it sits in front of. $G means column G, always, no matter where this formula ends up. $1 means row 1, always. Put both in and the reference stops being a distance and becomes a place.

3. The rate in one cell, and the 513.60 it cost

Here is the failure, worked end to end on real numbers.

The orders table sits in A1:D17. Column D holds the amount. Off to the side is a small block of assumptions, which is where a careful person puts rates so they are visible and editable instead of buried inside formulas.

Now commission per order goes in column E. In E2 you write =D2*G1 and fill it down to E17.

The first row is right. E2 reads 96.00, and 1,200 × 0.08 = 96.00. So the formula gets a nod and the fill gets trusted.

Here is what the next three rows did.

The column totals 1,840.00. It should total 1,326.40, because the sixteen amounts add up to 16,580 and 16,580 × 0.08 = 1,326.40. The sheet is over by 513.60, and 1,840.00 minus 1,326.40 = 513.60.

Before reading on, say why row 2 came out right. Getting this in your own words is worth more than the fix itself.

Row 2 is right because row 2 is the row you wrote the formula for. The distance from E2 to G1 is up one, right two. On row 3 that same distance lands on G2, which is the tax rate. On row 4 it lands on the uplift factor. From row 5 down it lands in empty space, and an empty cell is treated as zero in arithmetic.

Two of those wrong numbers, 190.00 and 1,554.00, look like money. That is the part worth remembering. This mistake does not announce itself, and the one row a reviewer usually checks is the one row that is correct.

The fix is one keystroke. In E2, click on the G1 and press F4, which turns it into $G$1, then fill down again.

=D2*$G$1

Now every row asks G1, the column totals 1,326.40, and changing the rate in G1 updates all sixteen rows at once. That second part is the real reward. The rate is in a cell someone can type into, which is what makes the sheet a model instead of a printout.

4. F4, and the four states in order

You almost never type dollar signs. You put the cursor on a reference and press F4, which cycles through the four possible states. Microsoft documents the order, and it is worth knowing because it means one extra press gets you where you want rather than starting over.

On a Mac the same toggle is Cmd+T, and F4 also works if your function keys are set up for it.

One thing that confuses people on Windows: F4 only cycles references when the cursor is on a reference inside a formula. Anywhere else it repeats your last action, which is a genuinely useful key for a different job.

5. Mixed: lock one half, free the other

Before the explanation, a prediction. If $G$1 pins both halves and G1 pins neither, say what you think G$1 pins.

The row. G$1 is free to move sideways and stuck on row 1. Its mirror, $G1, is stuck in column G and free to move up and down.

These are called mixed references, and they exist for one situation: a formula that gets copied in two directions at once. When you only ever fill down a column, relative and fully absolute cover everything you need. The moment you fill across as well, you usually want one half of the address pinned and the other half loose.

The rule of thumb is short. Pin the part that must not change in the direction you are copying.

• Your anchors sit in a row across the top, and you copy down: lock the row, G$1.
• Your anchors sit in a column down the side, and you copy across: lock the column, $D2.
• Both at once is the grid in the next section, and it needs one of each.

6. Nine answers from one formula

Finance wants the commission bill at three possible rates, for the first three orders, so they can pick one. That is nine numbers. It is also one formula.

Put the three rates across the top, in J1, K1 and L1: 0.06, 0.08 and 0.10. The amounts are already down column D. In J2 write this, then fill it right to L2 and down to row 4.

=$D2*J$1

Read it as two decisions rather than as punctuation. $D2 is locked to column D and free on the row, because the amount is always in D but changes row by row. J$1 is locked to row 1 and free on the column, because the rate is always in row 1 but changes column by column.

Here is what the nine cells hold, with the arithmetic.

Nine correct answers, one formula, typed once. If you get a grid like this wrong, the symptom is recognizable: the whole first row or the whole first column is right and everything else is nonsense, because the one copy you checked was the one that needed no shifting.

Now picture this on your own numbers. Take a table you actually use, put two or three scenario values across the top of a blank area, and imagine which half of each address you would pin. You do not have to build it. Just decide, for one formula, which two dollar signs you would place and where.

7. Four things the dollar sign does not do

Each of these catches somebody, and three of them are only visible after the damage.

It does not change the answer where you typed it. =D2*G1 and =D2*$G$1 return exactly the same number in E2. The difference appears only when the formula is copied somewhere else. That is why testing one cell proves nothing about a fill.

It does not protect the cell from editing. Locking a reference and locking a cell are unrelated. Stopping people from typing over G1 is sheet protection, which is a different feature in a different menu.

It does not stop a reference changing when the target physically moves. This one surprises people who think absolute means permanent. Insert a row above row 1, and every $G$1 in the workbook quietly rewrites itself to $G$2, because Excel is following the value you pointed at. Absolute means "does not shift when the formula is copied", not "never changes".

It does not survive a cut. Cutting the anchor cell with Ctrl+X and pasting it elsewhere drags every reference to it along, dollar signs and all. Copying it does not. If a model breaks the day someone tidied the layout, this is usually why.

8. When you need no dollar sign at all

There are two ways to get the same protection without any punctuation, and both read better afterwards.

Name the cell. Select G1, type CommissionRate into the Name Box beside the formula bar, and press Enter. Now the formula is =D2*CommissionRate, which is already absolute, and which says what it means to anyone who opens the file next year. Defined names do not shift when a formula is copied.

Put the data in a table. Inside an Excel Table created with Ctrl+T, a column reference is written [@Amount] and it means "the amount on this row" by name rather than by distance. There is nothing to lock and nothing to get backwards, and the reference keeps working when columns are inserted. This is one of the better reasons to press Ctrl+T on a range you are about to write formulas against.

Neither one removes the need to understand the dollar sign, because you will read other people's workbooks for the rest of your career. But for the sheets you build, a named rate is easier to be right about than a pinned address.