Relative and absolute references · Excel

When you copy a formula to another cell, Excel adjusts its references. Usually that's exactly what you want; sometimes it isn't, and that's what $ is for.

Relative references

	A	B	C	D
1	Item	Price	Qty	Total
2	Pen	30	3	`=B2*C2`
3	Notebook	45	2	`=B3*C3`
4	Bag	799	1	`=B4*C4`

Type =B2*C2 in D2, then drag the fill handle (the small square at the cell's bottom-right corner) down to D4. Excel rewrites the formula on each row: =B3*C3, then =B4*C4.

A reference like B2 is relative: Excel stores it as "two columns to the left, same row". Copied one row down, it still points two columns left on the new row.

Absolute references: $

Now add tax, with the rate in a single cell, F1:

A relative reference shifts as you copy; a $ pins the column, the row, or both.

	A	B	C	D	E	F
1	Item	Price	Qty	Total		0.18
2	Pen	30	3	90	`=D2*(1+$F$1)`
3	Notebook	45	2	90	`=D3*(1+$F$1)`

Excel formula

=D2*(1+F1)       → filled down, becomes =D3*(1+F2): an empty cell, so the tax disappears
=D2*(1+$F$1)     → filled down, becomes =D3*(1+$F$1): always the rate cell

The dollar signs lock the reference: $F$1 means "column F and row 1, wherever this formula goes".

Mixed references

A $ can lock just the column or just the row:

Reference	Column	Row
`B2`	moves	moves
`$B$2`	locked	locked
`$B2`	locked	moves
`B$2`	moves	locked

The classic use is a multiplication table. Put =$A2*B$1 in B2 and fill it across and down:

	A	B	C	D
1		1	2	3
2	1	`=$A2*B$1`	2	3
3	2	2	4	6
4	3	3	6	9

$A2 always reads from column A (the row numbers down the side), and B$1 always reads from row 1 (the numbers across the top). One formula fills the whole table.

Ranges

Excel formula

=SUM(B2:B10)          → a block of cells in one column
=SUM(B2:D10)          → a rectangle: columns B to D, rows 2 to 10
=SUM(B:B)             → the whole of column B
=SUM(2:2)             → the whole of row 2
=SUM(B2:B10, D2:D10)  → two separate ranges

Other sheets

Excel formula

=Prices!B2                   → cell B2 on the sheet named Prices
='June Sales'!D2             → names with spaces go in single quotes
=SUM(Jan:Mar!D2)             → D2 on every sheet from Jan to Mar

Click on another sheet while typing a formula, and Excel writes the reference for you.

Named ranges

Select F1, type TaxRate in the Name Box (left of the formula bar), and press Enter. Formulas can then say:

Excel formula

=D2*(1+TaxRate)

A name is absolute automatically, and it makes formulas read like sentences. Formulas → Name Manager lists and edits every name in the workbook.