Absolute and Relative Cell References in Excel ($A$1 Explained)
Last updated: November 2026 · Works in all versions of Excel
You write a formula, it works perfectly, and then you copy it down the column and the results go wrong. Nearly every time, the cause is cell references moving when they should have stayed still. Understanding the difference between relative, absolute and mixed references, and the dollar sign that controls them, is one of the most important steps from beginner to confident Excel user. This guide explains how each type behaves, with simple examples you can try.
Relative references: the default
When you type =B2*C2 in D2, Excel does not really store "B2" and "C2". It stores the idea "the cell two columns to the left, times the cell one column to the left, on the same row". That is a relative reference. When you copy the formula from D2 to D3, it becomes =B3*C3; copy it to D10 and it becomes =B10*C10.
This is exactly what you want most of the time. A quantity times price formula needs to look at its own row, and relative references make that happen automatically when you double-click the fill handle to copy the formula down.
Absolute references: locking a cell with $
Now suppose the VAT rate of 5% is stored in a single cell, H1, and you want every row to calculate VAT using it. In E2 you write =D2*H1. It gives the right answer in E2, but when you copy it down, E3 becomes =D3*H2, E4 becomes =D4*H3, and so on. H2 and H3 are empty, so every result after the first is zero.
The fix is to make the reference to H1 absolute by putting a dollar sign before both the column letter and the row number:
=D2*$H$1
The dollar sign means "do not change this part when copying". Copy the formula down and D2 changes to D3, D4 and so on, while $H$1 stays fixed. The same principle applies to lookup tables: in =VLOOKUP(A2,$K$2:$M$200,3,FALSE) the table range is locked so it does not slide down as the formula is copied. See the VLOOKUP guide.
The F4 shortcut
You do not have to type the dollar signs. While writing or editing a formula, place the cursor in or just after a reference and press F4. Each press cycles through the four options:
| Press F4 | Reference | What is locked |
|---|---|---|
| Once | $H$1 | Column and row |
| Twice | H$1 | Row only |
| Three times | $H1 | Column only |
| Four times | H1 | Nothing (relative again) |
On some laptops you need to press Fn + F4. On a Mac, use Cmd + T.
Mixed references: locking only the row or column
A mixed reference locks one part and leaves the other free:
$A2locks the column. Copy it across and it still points to column A; copy it down and the row changes.A$2locks the row. Copy it down and it still points to row 2; copy it across and the column changes.
Mixed references matter when one formula is copied both across and down, which is common in summary tables, price lists and budgets.
Example: a multiplication grid
Put the numbers 1 to 10 in A2:A11 and again in B1:K1. In B2, type:
=$A2*B$1
Copy B2 across to K2, then copy the row down to row 11. Every cell multiplies the number at the start of its row by the number at the top of its column. $A2 always looks at column A but moves down the rows; B$1 always looks at row 1 but moves across the columns.
The same pattern builds real reports. A grid of sales by branch and month uses SUMIFS with the branch name locked to column H and the month locked to row 1, so a single formula fills the whole table. A price list that applies different discount percentages across the top to different list prices down the side works in exactly the same way.
References to other sheets and workbooks
When you click a cell on another sheet while writing a formula, Excel writes a reference such as =Rates!B2. If the sheet name contains spaces, it adds quotes: ='VAT Rates'!B2. These references follow the same rules: add dollar signs with F4 if they must stay fixed. References to other workbooks include the file name in square brackets and are absolute by default. Learn more about linking sheets in our worksheet guide.
Named ranges and tables: the alternative to dollar signs
Instead of $H$1 you can give the cell a name. Select H1, click the Name Box to the left of the formula bar, type VAT_Rate and press Enter. Now =D2*VAT_Rate works and is always absolute. Names make formulas self-explanatory and are ideal for rates, targets and settings used across a workbook.
Columns in an Excel Table use structured references like Sales[Amount], which also stay fixed when copied down and expand automatically as data is added.
Common mistakes to avoid
- Locking everything.
=$D$2*$H$1copied down gives the same answer on every row. Only lock what must stay fixed. - Locking the wrong part. When copying across a row, lock the column you want to keep, not the row.
- Cutting and pasting instead of copying. Cutting (Ctrl + X) moves a formula without changing its references; copying (Ctrl + C) adjusts relative references.
- Not checking after copying. Click a few copied cells and press F2; Excel colours each reference and outlines the cell it points to, which makes errors easy to spot.
Summary
- Relative references (A1) change when a formula is copied.
- Absolute references ($A$1) never change.
- Mixed references lock only the column ($A1) or only the row (A$1).
- Press F4 to cycle through the options while editing.
- Named ranges and table references are readable alternatives to dollar signs.
Back to: all Excel tutorials.