Skip to content

Stockertim Dictionary · Technology

Absolute reference

Also called absolute references, absolute cell reference, mixed reference, anchored reference

In short

A cell reference fixed with dollar signs, such as $B$1, so that it keeps pointing at the same cell when a formula is copied or filled.

  • Beginner
  • Concept
  • Checked

In detail

A dollar sign in a reference locks the part that follows it. $B$1 locks both the column and the row, so every copy of the formula reads B1. Two mixed forms lock only one part: $B1 keeps column B but lets the row change, and B$1 keeps row 1 but lets the column change. In spreadsheet programs such as Excel on Windows, pressing F4 while editing a reference cycles through the four forms.

The full lock is used for single inputs that many rows share: a commission rate, a currency conversion rate, a VAT rate or a target. A formula such as =C2*$B$1 can be filled down a long column and every row multiplies its own amount by the one rate. Changing that rate cell then updates every result together, which is the point of keeping assumptions in one place.

Mixed references matter in grids where one input runs along the top and another down the side, such as a table of loan payments by rate and term or a price list by product and region. One formula with a locked column for the side labels and a locked row for the top labels can fill the whole grid.

Errors come from locking too much or too little. A missing dollar sign lets the reference drift; an extra one freezes a reference that should move, so every row returns the first row's answer. Both mistakes produce plausible numbers, so testing a few rows by hand is worthwhile. A named cell, such as Rate, is an alternative that behaves like an absolute reference and reads more clearly.

Worked example

A sales team in Nairobi pays 4% commission. The rate sits in B1 and sales are in C2 to C4; D2 holds =C2*$B$1, filled down.

  1. D3 becomes =C3*$B$1: the rate reference stays on B1
  2. Sale of 5,000 in C3: 5,000 × 4% = 200
  3. Sale of 12,000 in C4: 12,000 × 4% = 480

Every row uses the single rate in B1, and changing B1 updates all the commissions.

In practice

Model builders keep rates, targets and other assumptions in a clearly marked input area and reach them with absolute references or names, so a reviewer can find and change each assumption once.

Watch out

  • If every row of a filled column shows the same answer, a reference that should move may have been locked.
  • Typing a number such as 4% straight into each formula instead of referring to one cell hides the assumption and makes updates error-prone.

Learn it properly

Official sources

Facts last checked on 9 November 2026.

Education, not advice: the Stockertim Dictionary explains what terms mean. It never recommends buying or selling anything.

English