Absolute, Relative and Mixed References: The Dollar Signs That Decide What Fills Down

Somebody typed one formula into D2, checked it against a calculator, and filled it down to D7. The first line was right. The other five were wrong, and the order still went out.

D2:  =B2*C2*(1-F2)      → 1568.16    trade discount, 12% — correct
D3:  =B3*C3*(1-F3)      → 1012.30    discounted by the carriage uplift
D4:  =B4*C4*(1-F4)      → 2830.94    discounted by the settlement discount
D5:  =B5*C5*(1-F5)      → 988.13     discounted by the export surcharge
D6:  =B6*C6*(1-F6)      → 996.00     discounted by VAT
D7:  =B7*C7*(1-F7)      → 4033.50    discounted by an empty cell, so not discounted at all

No error value. No green triangle. Six numbers that all look like money, in a column that totals 11,429.03 when the honest answer is 10,625.30. The invoice is 803.73 heavy — about 7.6% — and every single figure on it is internally consistent, because F3 really does contain 0.045 and Excel really did do the multiplication.

One character fixes it. The whole of this article is about which character, and how to know that before the fill rather than after the customer.

What this covers. Dollar signs work identically in every version of Excel ever shipped, and in Google Sheets and LibreOffice Calc. ISFORMULA and FORMULATEXT in section 11 need Excel 2013 or later. Structured references in section 9 need a Table, so Excel 2007 or later. Keyboard: F4 on Windows, ⌘+T on Mac (and fn+F4 on Mac keyboards that have an F4 key doing something else).

1) A Reference Is a Direction, Not an Address

This is the one idea. Everything else in the article is a consequence of it.

When you type =A1 into cell C5, Excel does not store "A1". It stores "two columns to my left, four rows above me". The text A1 is only how that direction is drawn on screen, from the point of view of the cell you happen to be standing in. Copy that formula to C6 and the direction is unchanged — two left, four up — so the display changes to A2. The formula did not adapt. It never moved.

You can see this yourself, and it is worth five minutes because it turns the dollar sign from a superstition into a mechanism. Turn on R1C1 reference style: File → Options → Formulas → Working with formulas → R1C1 reference style (on Mac, Excel → Preferences → General). Column headers become numbers, and every formula on the sheet redraws itself as an offset:

The square brackets are the relativity. A dollar sign in A1 style and a missing bracket in R1C1 style are the same fact written twice. Turn it off again afterwards if it makes your eyes hurt — but notice that in R1C1, every cell in a filled-down column shows identical text. That is the property you are really trying to achieve when you fill a formula down, and A1 style hides it from you by redrawing each row differently.

2) The Four Shapes of One Reference

A reference has two halves — a column letter and a row number — and each half can be locked independently. That gives four shapes, and F4 cycles through them in this order:

A1  →  $A$1  →  A$1  →  $A1  →  A1  →  ...

The reading rule is mechanical: the dollar goes immediately in front of the thing that must not change. $A means the column stays A. $1 means the row stays 1. Nothing more subtle than that.

Using F4 properly. Put the cursor inside or immediately after a reference in the formula bar and press F4 — you do not need to select the whole thing. Press it repeatedly to cycle; press it a fifth time and you are back where you started, which is the cheapest way to undo a wrong guess. Select a range of the formula text containing several references and F4 toggles all of them together.

The two-question test, which is faster than remembering the table. Write the formula for the top-left cell of the block, then ask of each reference in it:

1. When this formula moves one column right, should this reference move right with it? If no, put a $ before the column letter.
2. When this formula moves one row down, should this reference move down with it? If no, put a $ before the row number.

Two yes/no answers per reference, and the dollars write themselves. Sections 3 to 6 are that test applied to the four situations you will actually meet.

3) One Constant, Locked Both Ways

🎯 Scenario: Price the six order lines in the grid at the trade discount in F2.

The formula reads the row's own price and units, and one cell that every row shares. Run the test on each reference:

• B2 (price) — fills down to B3? Yes. So no dollars.
• C2 (units) — fills down to C3? Yes. So no dollars.
• F2 (the rate) — fills down to F3? No. Every row wants F2. So both halves get locked.

D2:  =B2*C2*(1-$F$2)

Fill that to D7 and the column reads 1568.16, 932.80, 2554.20, 925.06, 1095.60, 3549.48 — total 10,625.30. Now compare it against the version at the top of the article, which differed by one keystroke:

Why this is the dangerous one. A missing dollar sign that lands on empty cells gives you multiplication by zero, and a column of 0.00 gets noticed in about four seconds. A missing dollar sign that lands on other assumptions gives you plausible money. The assumptions block is not an unlucky layout — it is the layout everybody builds, because stacking the rates in a tidy column next to the data is the obvious thing to do. It is also six loaded chambers directly beneath the one cell your formula meant to read.

Two habits that make the failure structural rather than lucky:

• Put assumptions on their own sheet, or at least above the data rather than beside it. A formula filling down cannot drift into a block that is not below it.
• Give the cell a name. =B2*C2*(1-TradeDiscount) cannot lose its dollars, because it does not have any — a defined name is absolute by default. Section 9 comes back to this.

4) Mixed References: One Formula That Fills a Whole Table

This is the section that pays for the other eleven. Mixed references are how one formula, typed once, fills a rectangle.

🎯 Scenario: Build a three-tier price matrix — every SKU down the side, every tier across the top — without typing eighteen formulas.

Put the tier rates in a header row to the right of the grid, in H1:J1:

H1:  0.12        Trade
I1:  0.18        Distributor
J1:  0.27        Export

Now the top-left cell of the matrix, H2. Run the test twice on each reference:

• B2 (the list price) — filling right to I2, should this become C2? No, C2 is units. Lock the column: $B2. Filling down to H3, should it become B3? Yes, that is the next SKU's price. Leave the row loose.
• H1 (the tier rate) — filling right to I2, should this become I1? Yes, that is the next tier. Leave the column loose. Filling down to H3, should it become H2? No, the rates only ever live in row 1. Lock the row: H$1.

H2:  =$B2*(1-H$1)

Copy H2, select H2:J7, paste. Eighteen prices from one formula:

The three ways to get it wrong, and how loudly each one fails:

Notice that all three failures are loud. Over-locking and under-locking a matrix produce visible nonsense, which is why mixed references have a reputation for being fiddly rather than for being dangerous. Section 3's failure is the quiet one. Fiddly is survivable; quiet is not.

The mental shorthand, once you have done it a few times: the reference pointing at your left-hand key column gets its column locked ($B2), and the reference pointing at your top header row gets its row locked (H$1). Dollars point outward, towards the edges of the block.

5) Locking Only the Column: Whole-Row Rules

🎯 Scenario: Shade the entire row of any order line over 2,000, and show each line as a share of the order.

Conditional formatting is where column-locked references earn their keep, and where the largest number of people quietly give up. A conditional formatting formula is written for the top-left cell of the range you applied it to, and Excel then translates it to every other cell exactly as if you had filled it. So the rules are the ordinary rules — but the "fill" happens invisibly.

Select A2:F7, then Home → Conditional Formatting → New Rule → Use a formula:

=$D2>2000

The $ before D says: however far right this rule travels — into B2, C2, all the way to F2 — keep looking at column D. The missing $ before 2 says: as it travels down, let it look at row 3, row 4, and so on. Column locked, row loose, and the whole row lights up from one column's value. Rows 4 and 7 shade.

Get those two dollars the wrong way round and the behaviour is diagnostic:

The other conditional formatting trap. The formula is relative to the top-left cell of the applied range, not of your selection when you opened the dialog, and not of the sheet. Apply a rule to A2:F7 and write =$D3>2000 and every row is testing the row below itself. If a rule is behaving as though it is off by one, open Manage Rules and read the Applies to box before touching the formula.

Percent of total, which is the same idea in a plain formula — one loose reference, one locked range:

E10:  =D2/SUM($D$2:$D$7)      → 14.8%   filled down, the denominator never moves

If you write =D2/SUM(D2:D7) and fill it down, the denominator shrinks every row until the last line is 100% of itself. It is the only percentage column in the world whose entries add up to something over 200%, and it appears in real workbooks constantly.

6) The Expanding Range: $B$2:B2

🎯 Scenario: A running total down the order, and a per-family sequence number.

Lock one end of a range and leave the other loose, and the range grows as the formula fills. It is the neatest trick in this entire subject.

G2:  =SUM($D$2:D2)

In G2 that range is D2:D2. Filled to G3 it becomes D2:D3, then D2:D4, and by G7 it is D2:D7. One formula, a running total:

1568.16    2500.96    5055.16    5980.22    7075.82    10625.30

The same shape counts as well as it sums. Numbering each SKU within its product family — FX, VB, PS, TS — takes one formula:

=COUNTIF($A$2:A2, LEFT(A2,2) & "*")      → 1, 2, 1, 2, 1, 1

Row 3 is the second FX; row 5 is the second VB. The count only ever sees the rows at or above the current one, because the bottom end of the range is loose. Swap the dollars — =COUNTIF(A2:$A$7, ...) — and you get a countdown instead, which is occasionally exactly what you want for "how many of these are still to come".

The classic use of this shape is flagging duplicates in a way that keeps the first one:

=IF(COUNTIF($A$2:A2, A2) > 1, "duplicate", "")

COUNTIF($A$2:$A$7, A2) > 1 marks every copy including the original. The expanding version marks only the second and subsequent ones — which is the difference between "this list contains duplicates" and "these are the rows to delete".

7) Copy, Cut, Insert, Delete — Four Verbs, Four Behaviours

Dollar signs control what happens when a formula is copied. They have almost no say in the other three, and this is where people who understand $ perfectly still get surprised.

Copy translates. Cut does not. Copy D2 to D3 and the relative references shift by one row. Cut D2 to D3 — or drag it by its border — and the formula arrives unchanged, still pointing at B2 and C2. Cutting moves a formula; copying re-aims it. Neither is wrong, but "I moved it and now it reads the wrong row" and "I moved it and now it reads the row it always did" are both real complaints from the same afternoon.

Moving the cell a formula points at rewrites the formula — dollars and all. This is the one that catches everyone. =B2*C2*(1-$F$2) looks nailed down. Cut F2 and paste it into F20, and your formula obligingly becomes =B2*C2*(1-$F$20). The dollar sign never promised to keep pointing at cell F2 — it promised not to translate when copied. Excel keeps references aimed at the data, and it will follow that data anywhere you move it.

Inserting and deleting rows shifts references too. Insert a row above row 2 and every $F$2 in the workbook quietly becomes $F$3, which is correct and desirable. Delete the row containing F2 and the formulas that referenced it show #REF! — the one error value that is genuinely good news, because the alternative would have been a silently repointed formula.

Filling, four ways:

That last one is the escape hatch when you need an unchanged copy of a formula one row down and cannot face adding dollars. The other escape hatch: select the formula text in the formula bar, copy it as text, press Esc, and paste it into the target cell. Text has no references to translate.

Paste Special. Ctrl+Alt+V then Formulas pastes the formula without the formatting, translating as usual. Values pastes the results and throws the references away entirely, which is the right ending for a working column nobody should recalculate again.

8) Other Sheets, Other Workbooks

The dollar rules do not change across a sheet boundary; they just get more text in front of them.

=Rates!$B$2                       another sheet in this workbook
='Q3 Rates'!$B$2                  quotes required — the name contains a space
=SUM(Jan:Mar!B2)                  a 3-D reference across three sheets
='[Prices.xlsx]Trade'!$B$2        another workbook, while it is open

Three things worth knowing:

The quotes are about the name, not the reference. Single quotes go round the sheet name whenever it contains a space, a hyphen, or starts with a digit. Excel adds them for you when you click the cell; it does not add them when you type, which is why =Q3 Rates!B2 returns #NAME? and looks for a moment like a broken function.

3-D references are relative in a dimension dollars cannot reach. =SUM(Jan:Mar!B2) means "cell B2 on every sheet from Jan to Mar inclusive", and it is defined by sheet position, not by name. Drag a new sheet in between and it joins the sum silently. Move a sheet out and it leaves. There is no $ for sheet order — if that matters, list the sheets explicitly: =SUM(Jan!B2, Feb!B2, Mar!B2).

External links rewrite themselves when the other workbook closes. ='[Prices.xlsx]Trade'!$B$2 becomes ='C:\Users\...\[Prices.xlsx]Trade'!$B$2 the moment Prices.xlsx is shut, and the value freezes at whatever it last read. Send that workbook to somebody else and they get a path that does not exist on their machine, plus a security prompt on open. For anything that leaves the building, paste the values.

9) Tables and Names: The Same Idea Without Dollar Signs

Two features replace dollar signs with something better, and both have a locking rule of their own.

Defined names are absolute by default. Select F2, type TradeDiscount in the Name Box, and the Name Manager stores it as =Sheet1!$F$2 — dollars included, whether you asked for them or not. =B2*C2*(1-TradeDiscount) can be filled anywhere in the workbook and cannot drift. (Excel does allow relative names, defined by removing the dollars in the Refers to box while a specific cell is selected. They work, they are clever, and they are completely invisible to whoever maintains the sheet after you.)

Structured references have no dollars at all, which does not mean they cannot move:

=[@[List Price]] * [@Units]           this row's price times this row's units
=SUM(Orders[Line Total])              the whole column

The @ means "this row", so it does the job of a relative row reference and needs no filling — a formula entered anywhere in a Table column fills the entire column by itself, and keeps filling it as rows are added. But copy =SUM(Orders[Line Total]) one column to the right and you get =SUM(Orders[Assumption]). Structured references translate sideways exactly like ordinary ones.

The lock is a doubling rather than a dollar:

=SUM(Orders[Line Total])                            shifts when copied right
=SUM(Orders[[Line Total]:[Line Total]])             stays on Line Total

Writing the column as a one-column range — from itself to itself — is the structured-reference equivalent of $D$2:$D$7. It looks redundant, and that is exactly what it is: redundancy is what an anchor is made of.

10) INDIRECT and OFFSET: The Reference That Refuses to Move

There is one way to build a reference that truly cannot be repointed by anything: build it out of text.

=INDIRECT("F2")        always cell F2, no matter what happens to the sheet

Insert ten rows above, cut F2 to another continent, delete the column — INDIRECT keeps reading whatever now sits in the cell called F2. Almost always this is a bug wearing a solution's clothes, because the thing you wanted was the rate, and the rate moved to F12 when somebody added rows. $F$2 would have followed it. INDIRECT("F2") reads a customer name and multiplies by it.

Three further costs, in the order they hurt:

• It is volatile. Every INDIRECT and every OFFSET recalculates on every change anywhere in the workbook, along with everything downstream. A few hundred of them is a spreadsheet that pauses when you type.
• It cannot see a closed workbook. INDIRECT pointing at another file returns #REF! the moment that file is closed. A plain external link keeps its last value.
• It hides from auditing. Trace Precedents draws no arrow from an INDIRECT, and neither does anything else. The dependency exists, but nothing in Excel can see it.

Use it when the reference genuinely has to be assembled at runtime — a sheet name chosen from a dropdown, say, as =INDIRECT("'" & $A$1 & "'!B2") — and reach for INDEX rather than OFFSET whenever you need a moving range, because INDEX returns a reference without being volatile:

=SUM($D$2:INDEX($D:$D, $H$1))       sum from D2 to the row number in H1 — non-volatile

11) Finding the Missing Dollar Before Someone Else Does

🎯 Scenario: A column of 400 formulas arrives from a colleague. Prove it is one formula, not 399 formulas and a surprise.

Four checks, cheapest first.

Show the formulas. Ctrl + ` (the backtick, top-left of most keyboards) flips the sheet from results to formulas. A correctly filled column shows the same shape 400 times, and a hardcoded number in the middle of it stands out immediately because it is the only entry with no =. Press it again to flip back.

Select the odd ones out. Select the column, then Home → Find & Select → Go To Special → Column differences (Ctrl+Shift+\). Excel compares every cell in the column against the active one, allowing for consistent relative references, and selects only the cells that break the pattern. On a clean fill it selects nothing. On a column somebody has "just fixed one row" in, it takes you straight there. Ctrl+\ does the same across a row.

Let Excel warn you. Formulas → Error Checking → Options → Formulas inconsistent with other formulas in the region is on by default and puts a small green triangle on the offending cell. It is genuinely useful and genuinely easy to switch off in irritation years ago and never switch back on. Check that it is on.

Count the faults in one cell. ISFORMULA and FORMULATEXT turn auditing into arithmetic:

=COUNT(D2:D7) - SUMPRODUCT(--ISFORMULA(D2:D7))                       hardcoded numbers in the column
=SUMPRODUCT(--ISERROR(FIND("$", FORMULATEXT(D2:D7))))                formulas with no absolute reference at all
=FORMULATEXT(D2)                                                     the formula as text, for reading

The first returns 0 for a column that is entirely formulas. The second counts every cell whose formula contains no $ — which, in a column that is supposed to reference an assumption, is a count of your bugs. It also counts blank and non-formula cells, since FORMULATEXT returns #N/A for those; that is a feature, because those are faults too.

And one habit that beats all four: shade your input cells. A conditional formatting rule of =ISFORMULA(A1) applied to the whole sheet, with a pale fill, means every constant on the sheet is a cell without a fill. You find the hardcoded 0.12 that should have been $F$2 by looking at the sheet, from across the room.

12) Mini Exercises

Copy the grid into a blank sheet starting at A1. Every answer is a single formula.

1. The honest column. Write the formula for D2 that prices the line at the trade discount and survives being filled to D7. State what D7 returns.
2. The matrix. Put 0.12, 0.18 and 0.27 in H1:J1. Write the one formula for H2 that fills H2:J7, and say in one line which half of which reference each dollar is protecting.
3. Two wrongs. Write the same matrix formula with both halves of the price reference locked, and then with both halves of the rate reference locked. Describe the visible symptom of each without building them.
4. Share of order. Write the formula for the percentage each line is of the whole order, correct when filled from row 2 to row 7. Then say what the last row would return if you forgot the dollars.
5. Running total, backwards. =SUM($D$2:D2) totals from the top down. Write the version that totals from each row to the bottom instead, and say which reference you had to swap.
6. The immovable one. $F$2 and INDIRECT("F2") both point at F2. Insert a row above row 2 and describe, in one sentence each, what the two formulas now point at — and which one is what you wanted.

Summary

A reference is a direction. Fill a formula down and you are repeating that direction, and a dollar sign is the one instruction that says not this part.

Three things carry nearly all of it. Run the two-question test on every reference in the top-left cell before you fill anything: does it move right, does it move down. Watch for the quiet failure specifically — an unlocked rate that lands on empty cells produces zeroes and gets caught, while an unlocked rate that lands on other rates produces money and does not. And remember that dollars govern copying only; cutting the referenced cell, inserting a row, or deleting a column will repoint your absolute reference without asking, because Excel is following the data rather than the address.

The order at the top of this article went out 803.73 heavy for want of two characters, and every formula on it was working perfectly.