What is an absolute reference in Excel? Definition and $ syntax

The dollar sign is the whole feature, and forgetting one is the most common reason an inherited spreadsheet is correct in row 2 and quietly wrong by row 200. Someone multiplied by a tax rate sitting in a single cell, filled the formula down the column, and the pointer to that rate walked down with the fill — landing on empty cells that Excel happily treats as zero.

What the dollar sign does

Microsoft states the default plainly: "By default, a cell reference is a relative reference, which means that the reference is relative to the location of the cell." A relative reference is not really an address so much as a direction — three cells to my left — and copying the formula keeps the direction, not the destination.

A dollar sign removes that relativity from whatever follows it. $B freezes the column, $2 freezes the row, $B$2 freezes both. Exceljet's glossary puts it the same way: an absolute reference is "a reference that is 'locked' so that rows and columns won't change when copied." Microsoft's worked example is =$B$4*$C$4, which "stays exactly the same" when copied from D4 to D5.

The behaviour is easiest to see side by side. Say B2 holds a VAT rate and column C holds net amounts:

Formula written in D2	Filled down to D3	Copied right to E2
=C2*B2	=C3*B3	=D2*C2
=C2*$B$2	=C3*$B$2	=D2*$B$2
=C2*B$2	=C3*B$2	=D2*C$2
=C2*$B2	=C3*$B3	=D2*$B2

Only the second row survives both moves. That is what "absolute" buys you: one cell, referred to identically from anywhere in the sheet.

Mixed references: locking one half

The third and fourth rows above are mixed references — one coordinate pinned, the other free. Microsoft describes the case directly: "You might want to mix absolute and relative cell references by preceding either the column or the row value with a dollar sign ($)—which fixes either the column or the row (for example, $B4 or C$4)."

Mixed references exist for one job above all others: filling a single formula across a rectangle. A margin grid with prices down column A and quantities along row 1 needs =$A2*B$1 in B2 and nothing else — dragged right and down, the column reference stays welded to A while the row reference stays welded to 1. Written with full absolute references it would return the same number in every cell; written with none, it would fall apart in both directions.

Typing four dollar signs by hand gets old, so both spreadsheets cycle them for you. Microsoft: "Press F4 to switch between the reference types." Starting from A1, the cycle runs $A$1 → A$1 → $A1 → A1 and back. To change an existing formula, Ablebits' sequence is press F2 or double-click the cell, put the cursor inside the reference, then press F4.

When to lock — and when it gets you nothing

Lock a reference when the formula's target should not move: a constant cell such as a rate, an FX conversion or a cut-off date; the lookup table in a VLOOKUP or XLOOKUP that every row must search; the header row and label column of a two-way grid. Leave it relative when the reference is supposed to travel — the row's own inputs, the cell above in a running total, the neighbouring column in a subtraction. The failure mode nobody warns you about is over-locking: a formula in which every reference is absolute cannot be filled at all, which is how a model ends up with two hundred hand-edited formulas that look identical and are not.

Dollar signs are also weaker protection than they look. Ablebits is blunt about the limit: "Though we have been saying that an absolute reference in Excel never changes, in fact it does change when you add or remove rows and/or columns in your worksheet, and this changes the location of the referenced cell." Excel rewrites $B$2 to $B$3 when you insert a row above it, because the cell you meant genuinely moved — and deleting the target outright turns the reference into #REF! like any other. The dollar sign governs copying, not restructuring.

When a constant is used across many formulas, the more durable answer is to stop pointing at a coordinate at all. Named ranges give the cell a name, and because names are absolute references by default they behave like $B$2 while reading like VatRate. Structured references do the same job for Excel tables.

Excel and Google Sheets

The syntax is identical: $ before the column letter, the row number, or both, in both applications, including across sheets ('Rates'!$B$2). The F4 shortcut carries over too — Google's keyboard-shortcut reference lists "Absolute/relative references (when entering a formula): F4", and on macOS Fn + F4.

The differences show up around the edges rather than in the syntax. Sheets has no Name Manager dialog, so the named-range route to the same outcome lives under Data → Named ranges instead.

How Formula Foundry handles this

Formula Foundry does not add a new kind of reference, and it does not fill formulas down your sheet for you. What it changes is whether you can see the dollar signs before they cost you a quarter's numbers. The editor opens the selected cell's formula on multiple lines with indentation that follows the nesting, and colour-codes it by token type: functions, ranges, constants and named references each get their own colour. A stray B$2 where you meant $B$2 sits in a highlighted token rather than in the middle of a 200-character string.

For the case that drives most locking — one constant reused everywhere — the add-on offers a different route. Global @@variables let you define a value once in the sidebar and write @@TaxRate inside any formula; Formula Foundry substitutes the value when the formula goes into the cell, and changing the variable updates every formula that uses it. That is not a replacement for a named range, which is a workbook object your spreadsheet manages and your colleagues can see. It is the tool for the constants that would otherwise be typed straight into the formula, where no dollar sign can help you.

Be clear about the limit: Formula Foundry does not evaluate your workbook. It parses, formats, explains and translates formulas — it cannot tell you that a missing dollar sign produced a wrong total, because only the sheet knows what the number should have been. What it can do is make the mistake visible while you are still writing it. The features overview shows the editor, the variables panel and the AI assistant in the sidebar of Google Sheets and Excel.

FAQ