Absolute vs relative references in Excel: what the $ sign does

The four reference types

• A1, relative: column and row both move when the formula is copied. Used for "the cell next to me" logic, such as adding the two quarters in the same row.
• $A$1, absolute: nothing moves. Used for a single input that every formula needs, such as a rate, a tax factor or a total.
• A$1, mixed, row locked: the column moves, the row stays. Used for a header row of values that every row below refers to.
• $A1, mixed, column locked: the row moves, the column stays. Used for a column of values that every column to the right refers to.

Excel stores a relative reference as an offset: in D4, B4 really means "two columns to the left, same row". Copying keeps the offset, so the formula in D5 points to B5. The $ sign switches the offset off for the column, the row or both. Moving a cell with cut and paste, by contrast, never changes its references; only copying does.

Step by step: share of total and commission

Column D adds the two quarters with purely relative references, =B4+C4, which copy down to =B5+C5 and so on. The total of all four reps, 92,800, sits in D8.

For each rep's share of the total, the rep's own total must move but the grand total must not: =D4/$D$8. For Kim that is 27,000 / 92,800 = 29.1%. Copied down, it becomes =D5/$D$8, =D6/$D$8 and so on, and the four shares add up to exactly 100%. Written as =D4/D8, the copy in row 5 would be =D5/D9; D9 is empty, so every copy below the first would show #DIV/0!.

The commission table works the same way. =B4*$B$1 in B11 can be copied across and down to fill all twelve cells: the sales reference moves with the formula, the rate stays in B1. Kim's Q1 commission is 480.00, the total of all reps is 92,800 × 4% = 3,712.00. Change the rate in B1 once and every commission updates.

Mixed references: one formula for a whole grid

A price table with units down the side (A17:A19) and prices across the top (B16:E16) needs both directions at the same time. The formula in B17 is =$A17*B$16: the units always come from column A, the price always from row 16.

Copied one cell to the right, it becomes =$A17*C$16, because the column of the price may move but the units column is locked. Copied one row down, it becomes =$A18*B$16. One formula, copied into twelve cells, fills the whole grid: 25 units at 7.50 = 187.50, 50 units at 12.00 = 600.00. With $A$17*$B$16 every cell would show the same 25.00; with A17*B16 the copies would multiply neighbouring results with each other.

Worked example: shares, commissions and a price grid with $ references

The yellow cells are inputs: the commission rate, the sales per quarter and the units and prices of the grid. Every formula in a block is the same formula copied; hover over the green cells to see how the references change.

Worked example: shares, commissions and a price grid with $ references

	A	B	C	D	E
1	Commission rate	4%
2
3	Rep	Q1 sales	Q2 sales	Total	Share of total
4	Kim	12,000	15,000	27,000	29.1%
5	Diaz	9,500	11,000	20,500	22.1%
6	Lee	14,000	13,500	27,500	29.6%
7	Patel	8,000	9,800	17,800	19.2%
8	Total	43,500	49,300	92,800	100.0%
9
10	Rep	Q1 commission	Q2 commission	Total commission
11	Kim	480.00	600.00	1,080.00
12	Diaz	380.00	440.00	820.00
13	Lee	560.00	540.00	1,100.00
14	Patel	320.00	392.00	712.00
15
16	Units ↓ × price →	2.50	4.00	7.50	12.00
17	10	25.00	40.00	75.00	120.00
18	25	62.50	100.00	187.50	300.00
19	50	125.00	200.00	375.00	600.00
20
21	Checks				Result
22	Total commission (every formula points to $B$1)				3,712.00
23	Sum of the four shares (must be 100%)				100.0%
24	Largest value in the price grid				600.00
25	25 units at 7.50, read from the grid				187.50

Fictional reps and figures. The share formula in column E is the same formula in every row; only the relative part D4, D5, D6 changes.

The F4 shortcut and other ways to lock references

While editing a formula, put the cursor in or right after a reference and press F4 (on a Mac Cmd+T, or Fn+F4 on some keyboards). Each press cycles through $A$1, A$1, $A1 and back to A1. Select a whole formula first to change all its references at once.

For inputs used across a model, a named range is often clearer than $B$1: =B4*CommissionRate explains itself and behaves like an absolute reference. Inside Excel tables, structured references such as [@Q1] handle the row automatically. In dynamic array formulas such as SORT or FILTER, you often write one formula that spills instead of copying, so locking matters less, but it still matters as soon as such a formula is copied to another row or column.

How to decide which reference you need

Ask two questions for every reference before you copy: when I copy this formula down, should this reference move to the next row? and when I copy it to the right, should it move to the next column? Two yes answers mean relative, two no answers absolute, and one of each a mixed reference with the $ in front of the part that must not move.

Check the result on the last copied cell, not the first one: the first cell is always right, errors only appear in the copies. Formulas, Show Formulas (Ctrl+`) displays every formula in the sheet so you can scan a column for references that drifted.

Common mistakes

• Forgetting the $ on a single input. =B4*B1 works in the first cell and then points to B2, B3 and so on. Lock rates, totals and other single inputs with $B$1.
• Locking too much. =$B$4*$B$1 copied down returns Kim's commission in every row. Only lock what must not move.
• The $ in the wrong place. $A$17*B$16 locks the units row as well, so every row of the grid uses 10 units. For a grid you need $A17 and B$16.
• Checking only the first cell. The original formula is always right. Errors show up in the copies, so check the last row and the last column.
• Confusing copy and move. Cut and paste or dragging a cell keeps all references as they are. Only copying adjusts relative references.