Skip to content

Stockertim Dictionary · Technology

Relative reference

Also called relative references, relative cell reference, relative addressing

In short

A cell reference without dollar signs, such as B2, that moves by the same number of rows and columns as the formula when it is copied elsewhere.

  • Beginner
  • Concept
  • Checked

In detail

A plain reference such as B2 is stored by the spreadsheet as a position relative to the cell holding the formula, not as a fixed address. If the formula sits in D2 and refers to B2, the program records it as two columns to the left on the same row. Copy that formula to D3 and it now refers to the cell two columns left on row 3, which is B3.

This behaviour is what makes a spreadsheet fast to build. One formula written for the first row of a table, such as =B2*C2 for price times quantity, can be copied or filled down a thousand rows and each copy calculates its own row correctly. The same applies sideways: a formula copied one column to the right shifts each column letter by one.

Relative references are the default, so they are also the default source of copying mistakes. A formula that should always read one fixed input, such as an exchange rate or a tax rate in a single cell, drifts away from it when copied, pointing at empty cells or at the wrong figures. Empty cells count as zero in arithmetic, so the copied results are often zero or small rather than obviously broken.

Programs also offer a notation that makes the idea visible. In the R1C1 style, the reference above appears as RC[-2], meaning the same row and two columns to the left, and every copy of the formula reads identically.

Worked example

A shop in Leeds writes =B2*C2 in D2 to work out the value of its first stock line, then fills it down to D4.

  1. D3 becomes =B3*C3 and D4 becomes =B4*C4 without any editing
  2. Row 3 holds 12 units at 4: 12 × 4 = 48
  3. Row 4 holds 30 units at 2: 30 × 2 = 60

Each copied formula uses the price and quantity on its own row.

In practice

Almost every column of calculations in a working spreadsheet relies on relative references: line values, running totals, differences between columns and flags for each row. Reviewers often press the key that shows formulas instead of results to confirm that each row follows the same pattern.

Watch out

  • A single input cell, such as a rate at the top of the sheet, needs an absolute reference; a relative one slides away from it when copied.
  • Copying a formula to a different layout can make it point at the wrong columns without any error.

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