Cell references: relative, absolute and mixed
A cell reference is the address a formula reads. Dollar signs decide whether it moves when you copy the formula, and getting that right is what lets one formula fill a whole column or table.
Last updated: 14 October 2026
Explains: reference, absolute reference, relative reference, mixed reference
What it is: A cell reference is the address of a cell or range in a formula, in one of four forms: relative (B2), absolute ($B$2) and two mixed forms ($B2 and B$2).
Why it matters: Almost every mistake in a copied formula is a reference that moved when it should have stayed, or stayed when it should have moved. Excel does not warn you: the formula still calculates and the figure looks plausible. This page also explains how to set references with F4 and what happens to them when you copy, insert or delete.
What a cell reference is
A cell reference is the address of a cell, such as B2, or of a block of cells, such as B2:B9. A formula with B2 in it does not store the number that is in B2. It stores the instruction "read whatever is in B2", so when the cell changes, every formula that refers to it recalculates.
What makes references worth learning is what they do when you copy a formula. Excel does not store B2 as a fixed address. In a formula in C2 it stores "the cell one column to the left".
Copy that formula one row down and it reads the cell one column to the left of C3, which is B3. Dollar signs switch this shifting off, for the column, for the row, or for both.
The four forms
The column letter and the row number of a reference are relative or locked independently of each other. A dollar sign in front of a part locks that part. That gives four forms, and the table shows what each one becomes when you copy the formula one row down or one column to the right.
Read the dollar sign as an anchor: what stands directly behind it does not move. In $A1 the dollar sits before the A, so the column is anchored and the row moves. In A$1 it sits before the 1, so the row is anchored and the column moves.
A reference with one dollar sign is called a mixed reference. All four forms point at the same cell until you copy the formula.
| Form | Name | Copied one row down | Copied one column right |
|---|---|---|---|
A1 | Relative | A2: the row moves | B1: the column moves |
$A$1 | Absolute | $A$1: nothing moves | $A$1: nothing moves |
$A1 | Mixed, column locked | $A2: the row moves | $A1: the column stays |
A$1 | Mixed, row locked | A$1: the row stays | B$1: the column moves |
Typing the dollar signs with F4
Type or click a reference in the formula bar and press F4. Excel puts the dollar signs in and, with every further press, moves to the next form. After the fourth press you are back at the relative form.
On many laptops the function keys control volume or brightness by default, and then you press fn+F4.
F4 works on the reference where the cursor stands. With the cursor inside B2:B9, F4 changes both ends of the range at once, so you get $B$2:$B$9.
Outside editing mode F4 repeats your last action, which is a different feature, so check that the cell is in edit mode before you press it.
| Press | You get | What is locked |
|---|---|---|
| Once | $A$1 | Column and row |
| Twice | A$1 | Row only |
| Three times | $A1 | Column only |
| Four times | A1 | Nothing |
What happens when you copy a formula
When you copy a formula, Excel moves every relative part of every reference by the same number of rows and columns as the copy moved. Parts with a dollar sign stay.
The table shows a share-of-total column: the formula in C2 is typed once and copied down, the amount reference moves with each row, and the total in B10 stays put.
Copying is not the same as moving. Cut and paste, or dragging the border of a cell, moves the formula without changing its references. Formulas elsewhere that pointed at the moved cell follow it to the new address.
Double-clicking the fill handle or pressing Ctrl+D copies a formula down in the same way as copy and paste.
| Cell | Formula | Reads |
|---|---|---|
C2 | =B2/$B$10 | Amount in B2, total in B10 |
C3 | =B3/$B$10 | Amount in B3, total in B10 |
C4 | =B4/$B$10 | Amount in B4, total in B10 |
C5 | =B5/$B$10 | Amount in B5, total in B10 |
Mixed references for a two-way table
A two-way table has one input down the side and another across the top, for instance amounts in column B and rates in row 1. One formula has to serve every cell.
Anchor the amount to its column ($B2) so it keeps reading column B when you copy right, and anchor the rate to its row (C$1) so it keeps reading row 1 when you copy down.
To choose the form for each reference, ask two questions: when I copy across, must this column change, and when I copy down, must this row change?
Where the answer is no, put a dollar sign in front of that part. A reference where both answers are no becomes $A$1, and one where both are yes stays A1.
=$B2*C$1
=$B2*C$1
Typed once in C2 and copied over the whole table.
=$B4*E$1
=$B4*E$1
The same formula after copying it two rows down and two columns right.
References to other sheets and workbooks
To refer to a cell on another sheet, put the sheet name and an exclamation mark in front: Journal!B2. When the name contains a space or another special character, or starts with a digit, it goes between single quotes: 'GL 2025'!B2.
Excel adds the quotes itself when you click the cell, but you need to type them when you write the reference by hand.
The sheet name does not shift when you copy. Only the cell part follows the usual rules, so 'GL 2025'!B2 copied down becomes 'GL 2025'!B3 and 'GL 2025'!$B$2 stays as it is.
A reference to another workbook adds the file name in square brackets, as in '[Veldhuis 2025.xlsx]Journal'!B2. When that file is closed, Excel shows the full path as well.
Links to other workbooks break when someone moves or renames the source file, and they show the last stored value until the links are updated (Data, Edit Links). For working papers, copy the source data into the workbook itself and record where it came from.
='GL 2025'!B2
='GL 2025'!B2
A sheet name with a space needs single quotes.
=SUM('GL 2025'!B2:B9)
=SUM('GL 2025'!B2:B9)
The sheet name applies to the whole range.
='[Veldhuis 2025.xlsx]Journal'!$B$2
='[Veldhuis 2025.xlsx]Journal'!$B$2
A locked cell in another workbook.
Whole columns and rows: A:A
A reference such as B:B stands for the whole column, and 2:2 for the whole row. The attraction is that rows added at the bottom of a journal export are included without touching the formula. SUM, SUMIF and COUNTIF only read the part of the column that holds data, so they stay fast.
The trade-offs are these:
- the header and anything else in the column is included, such as a total row or a note at the bottom, which can count amounts twice
- a formula placed in the same column that it reads creates a circular reference
- functions that process every row as an array, such as SUMPRODUCT, work through more than a million rows and slow the workbook down
When any of these apply, use an exact range, a table or a named range.
=SUMIF(B:B,4000, C:C)
=SUMIF(B:B;4000; C:C)
Fine for a plain journal export, as long as column C holds no total or note below the data.
=SUMPRODUCT((B2:B5000=4000)*C2:C5000)
=SUMPRODUCT((B2:B5000=4000)*C2:C5000)
An exact range keeps an array formula to 4,999 rows instead of over a million.
Inserting and deleting rows and columns
Watch out: A row added directly under the range is outside it: the third row in the table is the one that catches people out.
Excel keeps references pointed at the cells they were pointed at. When you insert or delete rows or columns, it rewrites the references in every formula. The table uses a total in =SUM(B2:B9) to show the results.
Deleting a row that holds a cell a formula refers to leaves the formula with nothing to point at, and Excel writes #REF! in its place. In Dutch Excel this is #VERW!. The formula then shows #REF! inside it and every result that depends on it turns into an error as well.
Press Ctrl+Z straight away: once the file has been saved and closed, you have to retype the reference.
References built from text, such as INDIRECT("B" & 5), do not follow insertions and deletions, because Excel does not read inside the text. After a row is added they keep pointing at row 5, whatever is in it by then.
| Action | Formula afterwards | What happened |
|---|---|---|
| Insert a row above row 2 | =SUM(B3:B10) | The whole range moves down |
| Insert a row between rows 3 and 4 | =SUM(B2:B10) | The range grows, the new row counts |
| Insert a row directly under row 9 | =SUM(B2:B9) | Unchanged: the new row is outside the range |
| Delete row 5 | =SUM(B2:B8) | The range shrinks by one row |
| Delete rows 2 to 9 | =SUM(#REF!) | The range no longer exists, the result is #REF! |
Worked examples
Example2 of 4Intermediate
Recompute VAT per invoice at three rates
Veldhuis Installatie BV issues sales invoices at 0% (reverse charge), 9% and 21% VAT. You want a check table with the net amounts of four invoices down column B and the three rates across row 1.
You type one formula in C2 and copy it over C2:E5. The example shows the formula as it reads in E4.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Invoice | Net amount | 0%0% | 9%9% | 21%21% | |
| 2 | 25-1101 | €1,250.00€ 1.250,00 | ||||
| 3 | 25-1102 | €8,400.00€ 8.400,00 | ||||
| 4 | 25-1103 | €3,250.00€ 3.250,00 | €682.50€ 682,50=$B4*E$1=$B4*E$1 | |||
| 5 | 25-1104 | €22,000.00€ 22.000,00 | ||||
| 6 |
Swipe sideways to see all columns
Formula in E4
=$B4*E$1
=$B4*E$1
Result: €682.50€ 682,50
Step by step
- In
C2you type =$B2*C$1. The column of the amount is locked ($B) and its row is free. The row of the rate is locked (1) and its column is free.C2gives 1,2501.250 x 0% = 0. - Copy
C2one column right, toD2. The column of$Bis locked and does not move, andC$1becomesD$1.D2reads =$B2*D$1 and gives 1,2501.250 x 9% = 112.50. - Copy
D2one row down, toD3. Now$B2becomes$B3, andD$1stays because its row is locked.D3reads =$B3*D$1 and gives 8,4008.400 x 9% = 756.00. - In
E4, two rows down and two columns right ofC2, the formula reads =$B4*E$1.B4holds 3,2503.250 (invoice25-1103) andE1holds 21%. - 3,2503.250 x 21% = 682.50. The other 11 cells of the table follow from the same formula.
Watch out: With =$B$2*$C$1, locked on both sides, every cell of the table shows the same number as C2. With =B2*C1, relative on both sides, D2 multiplies C2 by D1, and the table fills with nonsense without an error message.
More on this example: why it works, 1 more pitfall, 1 variation
Why it works
Each reference needs to move in one direction only: the amount moves down the rows but never across, and the rate moves across the columns but never down. A mixed reference locks exactly the direction that must stay fixed. With fully relative or fully absolute references no single formula can fill the table.
More to watch out for
- Compare the booked VAT with the rounded figure (see the variation), not the unrounded one. Check how the client's system rounds (per line or per rate on the invoice total) and use the same rounding.
Variations
=ROUND($B4*E$1,2)
=ROUND($B4*E$1;2)
Rounds each VAT amount to cents, the way an invoice states it.
Example3 of 4Intermediate
Build a running balance for the bank account
You have the movements on the bank account of Veldhuis Installatie BV for the last week of the year, with the opening balance as the first line. You want the balance after every movement in column D.
The first cell of the range is locked and the end is not, so the range grows as you copy down. The example shows the formula in D6.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Entry no. | Description | Movement | Balance | |
| 2 | 25-3001 | Opening balance | €48,200.00€ 48.200,00 | ||
| 3 | 25-3002 | Supplier payments | -€12,750.00€ -12.750,00 | ||
| 4 | 25-3003 | Customer receipts | €30,400.00€ 30.400,00 | ||
| 5 | 25-3004 | Payroll tax payment | -€8,900.00€ -8.900,00 | ||
| 6 | 25-3005 | VAT payment | -€15,300.00€ -15.300,00 | €41,650.00€ 41.650,00=SUM(C$2:C6)=SUM(C$2:C6) | |
| 7 | 25-3006 | Customer receipts | €21,000.00€ 21.000,00 | ||
| 8 |
Swipe sideways to see all columns
Formula in D6
=SUM(C$2:C6)
=SUM(C$2:C6)
Result: €41,650.00€ 41.650,00
Step by step
- In
D2you type =SUM(C$2:C2). The firstC$2has a locked row, so it always starts on row 2. The secondC2is relative. The range is one cell, andD2gives 48,20048.200. - Copied to
D3, the start stays atC$2and the end moves toC3. The formula reads =SUM(C$2:C3): 48,20048.200 + (-12,75012.750) = 35,45035.450. D4reads =SUM(C$2:C4), which adds 30,40030.400 and gives 65,85065.850.D5reads =SUM(C$2:C5), which adds -8,9008.900 and gives 56,95056.950.- In
D6the formula reads =SUM(C$2:C6). It adds the five amounts inC2:C6: 48,20048.200 - 12,75012.750 + 30,40030.400 - 8,9008.900 - 15,30015.300 = 41,65041.650. D7would give 41,65041.650 + 21,00021.000 = 62,65062.650, which is the closing balance to agree with the bank statement.
Watch out: Locking neither end, =SUM(C2:C2), gives a balance that is only the movement of that row. Locking both ends, =SUM(C$2:C$7), shows the closing balance, 62,65062.650, in every row instead of a balance per row.
More on this example: why it works, 1 more pitfall, 1 variation
Why it works
The two ends of the range follow different rules. The start is anchored by the dollar sign in the row part, so every copy begins on the same line. The end is relative, so each copy ends one row lower than the previous one. The range grows by one cell per row.
More to watch out for
- If you sort the rows, the balance column changes with them, because the balance depends on the order. Sort before you add the balance formulas, or keep the original order.
Variations
=SUM($C$2:C6)
=SUM($C$2:C6)
Locks the whole first cell instead of only its row. Copied down it gives the same result. Copied to the right the end moves to D6, so use =SUM($C$2:$C6) if you also copy sideways.
Example4 of 4Advanced
Total the journal per account with a locked range
The journal extract of Veldhuis Installatie BV lists eight lines on three expense accounts. In E2:E4 you list the accounts and in column F you want the total per account.
You type the SUMIF in F2 and copy it down to F4. The criteria range and the sum range must stay put while the account to look for moves one row at a time. The example shows F4, the account 4200.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Entry no. | Account | Amount | Account | Total | ||
| 2 | 25-2001 | 42004200 | €880.00€ 880,00 | 40004000 | |||
| 3 | 25-2002 | 41004100 | €3,150.00€ 3.150,00 | 41004100 | |||
| 4 | 25-2003 | 40004000 | €12,400.00€ 12.400,00 | 42004200 | €2,120.00€ 2.120,00=SUMIF($B$2:$B$9, E4, $C$2:$C$9)=SUMIF($B$2:$B$9; E4; $C$2:$C$9) | ||
| 5 | 25-2004 | 41004100 | €2,975.00€ 2.975,00 | ||||
| 6 | 25-2005 | 40004000 | €12,400.00€ 12.400,00 | ||||
| 7 | 25-2006 | 42004200 | €1,240.00€ 1.240,00 | ||||
| 8 | 25-2007 | 40004000 | €12,600.00€ 12.600,00 | ||||
| 9 | 25-2008 | 41004100 | €4,120.00€ 4.120,00 | ||||
| 10 |
Swipe sideways to see all columns
Formula in F4
=SUMIF($B$2:$B$9,E4, $C$2:$C$9)
=SUMIF($B$2:$B$9;E4; $C$2:$C$9)
Result: €2,120.00€ 2.120,00
Step by step
- In
F2you type =SUMIF($B$2:$B$9, E2, $C$2:$C$9). The two ranges are locked on both parts. The criterionE2is relative, because it has to move toE3andE4. - You copy
F2down. InF4, two rows lower, onlyE2has moved and has becomeE4. The formula reads =SUMIF($B$2:$B$9, E4, $C$2:$C$9). E4holds 4200. InB2:B9the account 4200 is inB2(entry25-2001) and inB7(entry25-2006).- SUMIF adds the matching amounts from
C2:C9:C2is 880 andC7is 1,2401.240. 880 + 1,2401.240 = 2,1202.120. - The same copy of
F2gives 37,40037.400 for 4000 inF2(12,40012.400 + 12,40012.400 + 12,60012.600) and 10,24510.245 for 4100 inF3(3,1503.150 + 2,9752.975 + 4,1204.120). The three totals add up to 49,76549.765, the total ofC2:C9.
Watch out: Without the dollar signs, =SUMIF(B2:B9, E2, C2:C9) in F2 is right, but copied to F4 it reads =SUMIF(B4:B11, E4, C4:C11). The ranges have slid down two rows: they miss B2:B3, include empty rows 10 and 11, and give 1,2401.240 instead of 2,1202.120. No error appears.
More on this example: why it works, 1 more pitfall, 1 variation
Why it works
Both ranges have to stay the same in every copy, and a locked reference does that. The criterion has to change in every copy, and a relative reference does that.
One formula therefore serves every row, and a new account added in E5 only needs the formula copied one row further.
More to watch out for
- A total that is too low on only some rows is the typical sign of ranges that were not locked. Compare the sum of the totals with the sum of the amounts.
Variations
=SUMIFS($C$2:$C$9,$B$2:$B$9, $E4)
=SUMIFS($C$2:$C$9;$B$2:$B$9; $E4)
The same with SUMIFS. Locking the column of the criterion ($E4) lets you copy the formula to the right as well, for a table with more than one criterion column.
Common mistakes
| Symptom | Cause | Fix |
|---|---|---|
| The first row is right, the rows below give #DIV/0! or a wrong figure | You copy a formula down and the first row is right but the rows below give #DIV/0! or the wrong figure. | A reference that had to stay put was left relative, so it slid down with the copy. In =B3/B11 the divisor B10 has become B11. Lock it before copying: =B2/$B$10. Press F4 while the cursor is in the reference. |
| Every cell of the table shows the same number after copying | Every cell of a table shows the same number after you copy the formula. | Too many dollar signs: =$B$2*$C$1 cannot move in either direction. Lock only the direction that must stay fixed: =$B2*C$1. |
| The total skips a row added at the bottom of the list | The total does not include a row you added at the bottom of the list. | Insert new rows inside the range, or use a whole-column reference, a table or a named range, so the total grows with the data. |
| #REF! after deleting rows or a sheet | A formula shows #REF! (#VERW! in Dutch Excel) after you deleted rows or a sheet. | The cell the reference pointed at is gone. Undo with Ctrl+Z straight away. If that is no longer possible, retype the reference. To avoid it, clear the contents of rows that other formulas use instead of deleting them. |
| A circular reference warning, or a total that is double | =SUM(B:B) typed in cell B20 shows a circular reference warning. Typed elsewhere, it gives a total that is double the real one when column B already holds a total row. | A whole-column reference includes the cell that holds the formula and any total rows. Put the formula outside the column it reads, or use an exact range such as B2:B19. |
| A reference to another sheet gives an error or is refused | A reference to another sheet gives an error or Excel refuses the formula: =GL 2025!B2. | A sheet name with a space needs single quotes: ='GL 2025'!B2. Click the cell on the other sheet while you build the formula and Excel adds the quotes for you. |