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 F4ReferenceWhat is locked
Once$H$1Column and row
TwiceH$1Row only
Three times$H1Column only
Four timesH1Nothing (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:

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

Summary

Back to: all Excel tutorials.