Cell references are the backbone of every Excel formula. When you copy a formula, Excel decides which parts of it should shift to match the new location and which parts should stay fixed. Getting this wrong is one of the most common sources of formula errors in spreadsheets — you copy a formula down a column and suddenly every row is calculating against the wrong value. This module explains the three reference types in full: relative, absolute, and mixed. Once you understand them, copying formulas correctly becomes effortless.
A cell reference is the address of a cell used inside a formula. When Excel evaluates the formula, it reads the value from the referenced cell and uses it in the calculation.
| Reference | Refers To | Example Formula |
|---|---|---|
B2 | The single cell at column B, row 2 | =B2*1.15 — multiplies the value in B2 by 1.15 |
B2:B51 | The range from B2 to B51 (50 cells) | =SUM(B2:B51) — sums all 50 cells |
Sheet2!B2 | Cell B2 on Sheet2 | =Sheet2!B2+A1 |
There are three types of cell references in Excel, and understanding the difference is critical for copying formulas correctly:
| Type | Notation | Behaviour When Copied |
|---|---|---|
| Relative | B2 | Shifts to match the new position — both column and row adjust |
| Absolute | $B$2 | Stays fixed — column and row do not change regardless of where the formula is copied |
| Mixed | $B2 or B$2 | One part fixed, one part shifts: either the column is locked ($B2) or the row is locked (B$2) |
A relative reference (e.g., B2) describes the position of the referenced cell relative to the formula cell. When you copy the formula to another cell, Excel does not copy the literal address — it copies the relationship (e.g., "the cell one column to the left and same row").
In this example, copying C2 downward works perfectly because each row has its own price and VAT rate, and the relative reference correctly adjusts to each row.
When the VAT rate is in a single cell (B1) shared across all rows, a relative reference to B1 breaks when copied down because it shifts to B2, B3, B4 — which are empty. The solution is an absolute reference.
An absolute reference (e.g., $B$1) is fixed — the dollar signs lock both the column (B) and the row (1) so they never change when the formula is copied in any direction.
$B$1 — the $ before B locks the column B; the $ before 1 locks the row 1. Both are fixed.$B = "column B is padlocked", $1 = "row 1 is padlocked"Now $B$1 always refers to the 15% VAT rate cell, no matter how far down the formula is copied.
| Scenario | Formula | Why Absolute |
|---|---|---|
| VAT rate in B1 applied to all prices | =A2*$B$1 | B1 must stay fixed; A2 shifts down with each row |
| Each value as % of total in B10 | =B2/$B$10 | B10 (total) is fixed; B2 shifts down through the data |
| Lookup table for VLOOKUP | =VLOOKUP(A2,$F$2:$G$50,2,0) | The lookup table $F$2:$G$50 stays fixed; only A2 shifts |
| Commission rate in a single cell | =C2*$H$1 | H1 holds the commission rate; C2 shifts per salesperson |
| Pass mark used in IF for all students | =IF(B2>=$E$1,"Pass","Fail") | E1 holds the pass mark; B2 shifts per student |
A mixed reference locks only one part of the address — either the column or the row — while the other part remains relative. There are two types:
The most instructive example is a multiplication table where row headers and column headers both feed into each cell:
A single formula =$A2*B$1 in B2, copied to the entire table, correctly fills every cell with the right product. Without mixed references, this would require 16 different formulas.
| Scenario | Formula in B2 | How It Copies |
|---|---|---|
| Monthly budget table: rows = departments (A2:A10), columns = months (B1:M1) | =$A2*B$1 |
Copy right: month column shifts (B1, C1, D1…); column A stays fixed. Copy down: department row shifts (A2, A3, A4…); row 1 stays fixed. |
| Cumulative sum that starts at a fixed row | =SUM($B$2:B2) |
Copy down: first B2 stays fixed (start of range), second B2 shifts (B3, B4…) — the range expands for a running total |
| Commission table: rows = salesperson, columns = commission tier | =$A2*C$1 |
Column A (names) locked; row 1 (tier rates) locked. Copy anywhere and each cell references the correct name and tier. |
Manually typing dollar signs is slow and error-prone. The F4 key cycles through all four reference types automatically while you are editing a formula — it is the most important productivity shortcut for formulas.
=A2*B1| F4 Press | Reference Becomes | Type |
|---|---|---|
| 1st press | $B$1 | Absolute — both column and row locked |
| 2nd press | B$1 | Mixed — row locked, column relative |
| 3rd press | $B1 | Mixed — column locked, row relative |
| 4th press | B1 | Relative — back to the original |
=A2* → click cell B1 → press F4 once (becomes $B$1) → press Enter=A2*$B$1 — A2 is relative, B1 is absolute. Fast, accurate, no manual $ typing.
When a copied formula produces unexpected results, the cause is almost always a reference type mismatch. Here is how to diagnose and fix each scenario.
If you need to change a reference type across many formulas at once:
$B$1 (or whichever reference needs changing)B1 (or the new reference type)Many formula errors in VLOOKUP, SUMIF, COUNTIF, and similar functions come from incorrect reference types in their arguments. Here are the correct patterns:
As covered in Module 14, named ranges always behave as absolute references — without needing any dollar signs. This is a key reason professionals prefer named ranges over $-style absolute references.
| Using $ Absolute | Using Named Range (Equivalent) |
|---|---|
=A2*$B$1 | =A2*VATRate |
=B2/$B$10 | =B2/TotalRevenue |
=VLOOKUP(A2,$F$2:$G$50,2,0) | =VLOOKUP(A2,ProductTable,2,0) |
=IF(B2>=$E$1,"Pass","Fail") | =IF(B2>=PassMark,"Pass","Fail") |
Named ranges offer: readability (you can understand the formula instantly), no $ typing, and global availability across all sheets. When you have a frequently referenced constant or lookup table, name it.
When building a formula that will be copied, ask yourself these three questions for each cell reference in the formula:
| The referenced cell is… | Use This Type | Example |
|---|---|---|
| On the same row, changes per row | Relative B2 | Each row's own price, name, quantity |
| A single shared constant (rate, tax, threshold) | Absolute $B$1 | VAT rate, commission rate, pass mark |
| A lookup or reference table | Absolute $F$2:$G$50 | VLOOKUP table, rate schedule, product list |
| A row header (formula copies across columns) | Column locked $A2 | Department name in a cross-tab table |
| A column header (formula copies down rows) | Row locked B$1 | Month name or period header in a cross-tab |
| A grand total for % calculation | Absolute $B$10 | Total revenue cell used as denominator |
| Start of an expanding cumulative range | Absolute start $B$2 | Running total: =SUM($B$2:B2) |
Q1: You enter =A2*B1 in cell C2, where A2 is a price and B1 is a 15% VAT rate. When you copy C2 down to C3, the result is 0. Why, and how do you fix it?
✓ B1 is a relative reference. When the formula is copied from C2 to C3, Excel shifts all references down by one row: A2 becomes A3 (correct — the next price) but B1 becomes B2, which is a different cell (probably empty, giving 0). The fix: change B1 to $B$1 (absolute). The corrected formula in C2 is =A2*$B$1. When copied to C3 it becomes =A3*$B$1 — A3 shifts correctly to the next price, and $B$1 stays locked on the VAT rate cell. Press F4 after clicking B1 in the formula to add the dollar signs automatically.
Q2: You are building a profit margin table. Row 2 has product prices (B2:F2) and column A has cost percentages (A3:A7). You want each inner cell to calculate price × (1 − cost%). Write the formula for B3 that can be copied to fill the entire table B3:F7.
✓ =B$2*(1-$A3). B$2: the row is locked ($2) because row 2 always contains the price headers; the column (B) shifts right as the formula copies across to C, D, E, F. $A3: the column is locked ($A) because column A always contains the cost percentages; the row (3) shifts down as the formula copies to rows 4, 5, 6, 7. When you copy B3 to F7, Excel correctly picks up each column's price (row 2) and each row's cost percentage (column A) for every cell in the table.
Q3: A VLOOKUP formula in D2 is =VLOOKUP(A2, F2:G50, 2, 0). When copied to D3, it returns wrong results. What is the problem and what is the fix?
✓ The lookup table range F2:G50 is a relative reference. When copied from D2 to D3, it shifts down one row and becomes F3:G51 — missing the first row of the lookup table (which may contain an important value). Copying further down shifts the range further, increasingly missing data. Fix: make the lookup table absolute: =VLOOKUP(A2, $F$2:$G$50, 2, 0). The $F$2:$G$50 stays fixed no matter how many rows the formula is copied. The A2 remains relative (correct — the lookup value shifts down to A3, A4, etc. for each row).
Q4: You have regional sales in B2:B7 and the total in B8. You want column C to show each region's percentage of total. Write the formula for C2 that copies correctly to C3:C7.
✓ =B2/$B$8 formatted as a percentage. B2 is relative — it shifts to B3, B4, B5, B6, B7 as the formula is copied down, picking up each region's sales figure. $B$8 is absolute — the total in B8 stays fixed as the denominator in every row. If $B$8 were relative, copying to C3 would divide by B9 (empty or wrong), C4 would divide by B10, etc. — giving incorrect percentages. The $ signs on B8 ensure it is always the total that every individual region value is divided by.
Q5: Describe exactly what happens to the formula =$A3*B$1 when it is copied from cell B3 to cell D6. What does the formula become?
✓ Moving from B3 to D6 is a shift of 2 columns right and 3 rows down. Apply the shift to each reference: $A3: the column $ is locked, so column A stays A (does not shift right by 2). The row 3 is relative, so it shifts down by 3 → becomes row 6. Result: $A6. B$1: the row $ is locked, so row 1 stays 1 (does not shift down by 3). The column B is relative, so it shifts right by 2 → becomes column D. Result: D$1. The formula in D6 is: =$A6*D$1. This is the correct mixed-reference behaviour that makes cross-tab tables work with a single copyable formula.
Q6: What does pressing F4 four times in a row do to a cell reference, and what is the practical workflow for adding an absolute reference to a formula?
✓ Pressing F4 four times cycles through all reference types: press 1 = $B$1 (absolute), press 2 = B$1 (row locked), press 3 = $B1 (column locked), press 4 = B1 (back to relative). It cycles repeatedly. Practical workflow: begin typing the formula, e.g., =A2* → click on the cell containing the VAT rate (e.g., B1) → before pressing Enter, press F4 once → $B$1 appears in the formula → press Enter. Result: =A2*$B$1 with the VAT rate absolute and the price reference relative. No manual dollar signs needed — F4 is faster and eliminates typos.