The idea
When you type =B2*C2 in D2, Excel does not store "cell B2". It stores "the cell two columns to my left, in my row", and "the cell one column to my left". Every reference is an offset from the cell holding the formula. Move the formula, and the offset points somewhere new.
That single fact explains fill-down, fill-right, copy and paste, and the most common spreadsheet mistake there is. A formula written once and filled over a column is correct in every row because each copy looks at its own neighbours.
Figure 1
Order 1049: one formula per line, one SUM over the range
| row | quantity | unit_price | line_total | formula_in_D |
|---|---|---|---|---|
| 2 | 2 | 189.00 | 378.00 | =B2*C2 |
| 3 | 2 | 53.99 | 107.98 | =B3*C3 |
| 4 | 3 | 379.00 | 1137.00 | =B4*C4 |
| cell | formula | shows |
|---|---|---|
| D5 | =SUM(D2:D4) | 1622.98 |
| D6 | =AVERAGE(D2:D4) | 540.99 |
- Three lines of order 1049.
D2holds=B2*C2: quantity times unit price, both read from the same row. - Fill
D2down. Excel keeps the relationship (the two cells to my left), so row 3 becomes=B3*C3and row 4 becomes=B4*C4. D2:D4is a range: every cell in the rectangle betweenD2andD4.SUMadds all three line totals: 378.00 + 107.98 + 1137.00 = 1622.98.AVERAGEreads the same range and divides by the number of cells in it: 1622.98 / 3 = 540.99. Add a fourth line inside the range and both results update.
Why it exists
Imagine pricing 5,000 order lines. If every formula named its cells literally, you would write 5,000 different formulas. With offsets you write one and drag. Offsets are the reason a spreadsheet scales from a three-line order to the whole Harbor & Pine catalog.
The price of that convenience is that some pointers must not move. A tax rate, a discount, a threshold for free shipping sits in one cell and is read by every row. Dollar signs are how you tell Excel which pointers to freeze.
There is a second benefit. A formula that points at an input cell documents the rule: anyone opening the sheet can see that the shipping threshold lives in one place, change it, and watch every row respond. A formula with 75 typed inside it hides the rule in hundreds of places, and the next person has to find them all.
How it works under the hood
Relative, absolute, mixed
A $ in front of a part of a reference freezes it when the formula is copied:
B2is relative: both column and row shift with the copy.$B$2is absolute: neither shifts. Use it for a single input cell.$B2is mixed: the column is frozen, the row moves.B$2is mixed the other way: the row is frozen, the column moves.
Pressing F4 with the cursor in a reference cycles through the four forms. In Google Sheets the same key combination works on most keyboards.
Mixed references fill a block with one formula
Mixed references earn their keep when a grid has one input running down and another running across. Write the formula in the top-left cell, freeze the column of the down input and the row of the across input, then fill over the whole block.
Figure 2
One formula for a whole grid: price after a discount
| A_price | B_0pct | C_5pct | D_10pct |
|---|---|---|---|
| 189.00 | 189.00 | 179.55 | 170.10 |
| 53.99 | 53.99 | 51.29 | 48.59 |
| 379.00 | 379.00 | 360.05 | 341.10 |
| cell | formula | reads_price_from | reads_discount_from |
|---|---|---|---|
| B2 | =$A2*(1-B$1) | A2 | B1 |
| C2 | =$A2*(1-C$1) | A2 | C1 |
| C3 | =$A3*(1-C$1) | A3 | C1 |
- Prices 189.00, 53.99 and 379.00 (products 183, 156 and 216) run down column A. Three discounts, 0%, 5% and 10%, run across row 1 (not shown). Every cell needs one price and one discount.
$A2locks the column and leaves the row free. Filled to the right it still reads column A; filled down it follows the row.B$1locks the row and leaves the column free. Filled down it still reads row 1; filled to the right it follows the column:B1, thenC1.- One formula written once, filled over the whole block. Row 3 with 5% off gives 53.99 × 0.95 = 51.29.
The test for any formula you plan to fill: for each reference, ask "should this follow the row, follow the column, both, or neither?" Then place the dollar signs to match.
Ranges are arguments, not sums
D2:D4 is not a number. It is a description of a rectangle, and the function decides what to do with the cells inside. SUM adds them, AVERAGE adds and divides by the count, MIN and MAX pick one. Two details cause real mistakes. First, these functions skip text and blank cells, so AVERAGE divides by the count of numbers it found, not by the number of rows. Second, a range is fixed by its corners: type a new line below the range and the total will not include it. Inserting a row inside the range does grow it.
IF is evaluated per row
IF(test, value_if_true, value_if_false) is a formula like any other, so it fills down and each copy reads its own row. Excel evaluates the test first, then returns one of the two values and ignores the other.
Figure 3
IF decides once per row, on that row's own values
-
C2read the order subtotal -
C2>=75TRUE or FALSE -
0value_if_true -
6.95value_if_false
=IF(C2>=75,0,6.95)inD2. Order 12417 has a subtotal of 682.96 inC2.- The test
C2>=75is TRUE for 682.96. - TRUE picks the first value, so the shipping cell shows 0. The second value is never looked at.
- Filled to the next row, the test reads
C3. Order 12418 has a subtotal of 37.98, so the test is FALSE and the cell shows 6.95.
The test can compare a cell with a number, with a text in quotes, or with another cell. When the threshold might change, put it in its own cell and point at it with $, exactly like the tax rate. A number typed inside an IF is easy to forget when the business rule changes.
Where the formula lands matters
Because references are offsets, cutting and copying behave differently. Copying a formula keeps its offsets, so the references shift. Cutting and pasting (or dragging a cell by its border) moves the cell itself, and Excel rewrites any formula that pointed at it so those formulas still find the same data. If you want the same text in a new place with the same targets, cut it; if you want the same behaviour in a new place, copy it.
How to check a fill-down
Do not trust a filled column because the first row looks right. Click the last row and read the formula in the formula bar: do the references still point where you meant? Then compare one row's result with a calculator. Both checks take ten seconds and catch almost every reference mistake.
Common misconceptions
"Fill-down copies the formula exactly as I typed it"
It copies the relationship. =B2*C2 becomes =B3*C3 one row down. That is what you want for the row's own values and exactly what you do not want for a shared input, so lock the shared input with $.
"If the formula returns a number, it is correct"
A reference that slid one cell sideways returns a number too. In the tax example, row 3 reads an empty cell, counts it as zero and returns the untaxed price with no warning. Excel only raises an error when it cannot calculate, not when you pointed at the wrong thing.
"SUM adds up every number I can see"
SUM adds the numbers inside the range you gave it. Rows outside the range, text that looks like a number and cells that hold something typed with a stray space are not counted. If a total looks low, check the range corners first.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Right. Every relative reference moves one row, E1 included. An empty cell counts as zero, so the result is the price with no tax and no error appears. $E$1 fixes it.
Not quite. Excel cannot tell which cells are meant to be shared. Only dollar signs say so.Try another answer.
Not quite. Fill-down copies the relationship, not the text, so row 2 becomes row 3 in every unlocked reference.Try another answer.
Not quite. Pointing at an empty cell is allowed and counts as zero. That is why this mistake produces a plausible wrong number rather than an error.Try another answer.
Lesson done: it is ticked on the map.