Anyone who uses a spreadsheet will sooner or later copy a formula down a column and find that the results are wrong, or that zeros and errors appear where they should not. In the vast majority of cases the problem is not the formula itself, but the type of reference it contains. Understanding the difference between relative, absolute and mixed references is the foundation for everything else: calculation tables, lookup functions, conditional formatting.
What happens when you copy a formula
A spreadsheet does not really store "cell B2". It stores a relative position: "the cell one column to the left, on the same row". If you type =B2*2 in C2 and copy the formula to C3, the program keeps the same distance and turns it into =B3*2. This behaviour is called a relative reference, and it is the default in Excel, LibreOffice Calc and Google Sheets.
Most of the time this is exactly what you want: the total of each row, the percentage of each item, the VAT-inclusive price of each product. The trouble starts when part of the formula must stay put.
The absolute reference
Imagine a column of net prices in B2:B50 and a VAT rate written once in cell F1. In C2 you type =B2*(1+F1). When you copy it down, the formula in C3 becomes =B3*(1+F2), but F2 is empty and the result equals the net price. No error message, just wrong numbers: the most dangerous kind of error.
The fix is to lock the reference to the rate with the dollar sign: =B2*(1+$F$1). A dollar before the letter locks the column; a dollar before the number locks the row. With both, the reference stays the same wherever you copy the formula.
Mixed references
Between the two extremes are mixed references, where only one of the two coordinates is locked:
| Notation | Column | Row | Typical use |
|---|---|---|---|
A1 |
moves | moves | row-by-row calculations |
$A$1 |
fixed | fixed | single parameter (rate, exchange rate) |
$A1 |
fixed | moves | value in a header column |
A$1 |
moves | fixed | value in a header row |
Mixed references become essential when a single formula has to fill an entire two-dimensional table.
Example: a multiplication or discount table
Suppose B1:F1 holds a series of discount percentages and A2:A20 a series of prices. You want to fill the grid with the discounted price for every combination. In B2 you type:
=$A2*(1-B$1)
The price is always in column A, so you lock the column; the discount is always in row 1, so you lock the row. Now you can copy B2 across the whole grid and every cell will read the right price and the right discount. One formula, a hundred correct results.
The key that switches reference type
You do not need to type the dollars by hand. In Excel, while editing a formula, put the cursor on a reference and press F4: the reference cycles from A1 to $A$1, then A$1, then $A1 and back to A1. In LibreOffice Calc the same function is Shift+F4. Google Sheets uses F4 like Excel. On some laptops you also need to hold the Fn key.
Common mistakes
- Locking too much. People who have just discovered the dollar sign tend to put it everywhere. If you also lock B2 in the VAT formula, every row will compute VAT on the first price.
- Forgetting the dollar in lookup ranges. In functions such as VLOOKUP the table range must be absolute; otherwise it slides down with the formula and the last rows no longer find their data.
- Moving cells instead of copying them. If you drag (cut and paste) a formula, its references do not change, relative or not. Relative behaviour only applies to copying.
- Inserting rows above the parameter. Absolute references still update when you insert rows or columns:
$F$1becomes$F$2if you add a row at the top. This is correct behaviour, but it surprises those who think "absolute" means "unchangeable".
A more readable alternative: names
For single parameters such as a tax rate or an exchange rate, it is often better to give the cell a name, for example VAT_Rate. The formula becomes =B2*(1+VAT_Rate): it reads better and the name behaves like an absolute reference. In my work I use names for every value a colleague might need to change, so it is clear where to intervene without touching the formulas.
How to check whether you got it wrong
A quick method: select a cell at the bottom of the copied column and press F2 (or double-click). The program highlights the cells the formula refers to in different colours. If the coloured box for the rate has moved onto an empty cell, you have found the error. Alternatively, you can switch on formula view (in Excel with Ctrl+` on an English keyboard, or from the Formulas menu) and scroll down the column to see at a glance where the references change.
In short
- A relative reference adapts when you copy the formula; it is the default behaviour.
- The dollar sign locks the column (
$A1), the row (A$1) or both ($A$1). - Mixed references let you fill a two-dimensional table with a single formula.
- F4 (Shift+F4 in LibreOffice) quickly switches the reference type.
- For single parameters, cell names are more readable than dollar signs.
