Excel Cell References: Relative, Absolute, Mixed

An Excel cell reference is the address of a cell, such as B2, that a formula uses to read that cell’s value. A relative reference (B2) changes when you copy the formula, an absolute reference ($B$2) never changes, and a mixed reference ($B2 or B$2) locks only the column or only the row. Press F4 while editing a formula to add the dollar signs.

Almost every formula mistake that gives a wrong number without an error message is a reference mistake, so this is the first thing worth getting right. Below, every result was produced by running the formula in Microsoft Excel, and every grid is a real sheet.

What a cell reference is

Every cell has an address made of its column letter and its row number: the cell in column B and row 2 is B2. When a formula contains B2 it does not store the number that is in B2 today, it points at the cell, so the formula gives a new answer whenever B2 changes.

A reference is stored in a formula in one of four styles, and the whole topic comes down to which one you choose:

Relative references: they move when you copy

A relative reference does not really remember an address, it remembers where the cell is compared with the formula. In D2, =B2*C2 means “multiply the cell two to the left by the cell one to the left”. Copy it down and every copy applies that same pattern to its own row. This is why dragging the fill handle works:

What the sheet shows

What is inside the cells (Show Formulas)

The top grid is what you see. The second grid is what is really stored in each cell (in Excel you can toggle this view with Ctrl + `). Row 3 holds =B3*C3 although we typed =B2*C2 only once.

Absolute references: the dollar sign locks a cell

Relative behaviour is wrong when every formula must read the same cell. Suppose the GST rate is typed once in G1 and each price must become price * (1 + rate). The obvious formula in C2 is =B2*(1+G1). Copy it down and look at the column called Without $:

What the sheet shows

What is inside the cells (Show Formulas)

Row 2 is right, and then the column quietly goes wrong: the Book and the Bag show no tax at all. When the formula moved down, G1 became G2 and then G3, which are empty cells and count as 0%. Excel shows no error, the numbers are simply wrong. In the With $ column the formula uses $G$1. The dollar signs lock the column and the row, so every copy still reads G1.

A useful pattern: the running total

You can lock only the start of a range. $A$2 stays fixed while the second half of A2 is relative, so the range grows by one cell on every row:

What the sheet shows

What is inside the cells (Show Formulas)

Mixed references: lock only the row or the column

Sometimes the formula must stay fixed in one direction and move in the other. A price table is the classic case: the prices run down column A and the discount rates run along row 1. Every cell needs “the price from column A of my row” and “the discount from row 1 of my column”. That is $A2 (column locked, row free) and B$1 (row locked, column free), and one formula fills the whole block:

What the sheet shows

What is inside the cells (Show Formulas)

Look at D4 in the second grid: =$A4*(1-D$1). The price still comes from column A, and the discount still comes from row 1, whichever way the formula was copied. To choose the lock, ask two questions: will this reference move sideways when I copy across? and will it move down when I copy down? Put a dollar sign in front of the part that must not move.

The F4 key

You do not have to type the dollar signs. While you are editing a formula, put the cursor on a reference (or just after it) and press F4. Every press moves to the next form, and after four presses you are back where you started. On a Mac use Cmd + T, and on many laptops you need Fn + F4.

Ranges: A1:B2, whole columns and more

A range is two addresses joined by a colon. It covers every cell of the rectangle between them. There are also whole-column and whole-row forms, a comma to combine separate cells, and a space that returns only the cells two ranges have in common. Here they are, each run on the same 3 by 3 block:

Range references follow the same lock rules as single cells: $A$2:$B$9 never moves, and A2:B9 shifts as a block when copied. Whole-column references such as A:A are handy when the data keeps growing, but read the mistakes section before you use them.

Copy versus cut

Copying a formula and moving it are different operations. Copy and paste rebuilds the relative pattern at the new place. Cut and paste moves the cell as it is, so its formula keeps pointing at the same cells. In both cases C1 held =A1:

There is a third case: cutting a cell that other formulas point at. Excel follows the move, so nothing breaks. Here B1 held =A1*2 and A1 was cut and pasted into D1:

What happens when you insert or delete rows

References are adjusted when the sheet changes shape, but not always the way you would guess. Every row below was run in Excel. The first two use the numbers 10, 20, 30 and 40 in A1:A4 with a total underneath, and the others are small sheets with a single formula:

Three lessons. A range grows when you insert a row inside it, but not when you add a row at its edge, so the new number is silently left out of the total. Deleting a row that a formula points at leaves #REF!, which means “the cell this formula referred to no longer exists”. And absolute references still follow inserted rows, only INDIRECT, which builds the address from text, does not.

References to other sheets

To read a cell from another sheet, put the sheet name and an exclamation mark before the address: =Sheet2!A1. If the sheet name contains a space or other special characters, wrap it in single quotes: ='Sales 2024'!A1. You never have to type the quotes yourself, because clicking the cell makes Excel write them. When a sheet is renamed, Excel rewrites every formula that points at it, so nothing breaks:

A reference to another workbook adds the file name in square brackets, for example ='C:\Reports\[Budget.xlsx]Sheet1'!A1. Avoid those when you can, because they break when the file is moved.

Adding the same cell on many sheets

If you keep one sheet per month or per branch, a 3D reference adds the same cell across a run of sheets: =SUM(Sheet1:Sheet3!A1) means “A1 on every sheet from Sheet1 to Sheet3”. Any sheet you place between the two end sheets is included automatically.

Give a cell a name

A cell can have a name that you use instead of an address. Select G1, type GST in the Name Box (the small box to the left of the formula bar) and press Enter, or use Formulas, Define Name. A name always points at the same cell, exactly like $G$1, and the formula reads like a sentence:

What the sheet shows

What is inside the cells (Show Formulas)

Names cannot contain spaces, cannot start with a digit and cannot look like a cell address. Excel accepted GST and Tax_Rate here, and refused Tax Rate, 1Rate and GST1 (that one is a real cell, column GST row 1). Using a name means that if the rate cell ever moves, or you need to check where it is, you change or look in one place.

Common mistakes

Mistake 1: forgetting the lock

The single most common one, shown above: =B2*(1+G1) is right for the first row and wrong for all the others, with no warning. Whenever a formula will be copied and one of its inputs lives in a single cell, ask whether that reference must be locked.

Mistake 2: typing the number instead of pointing at it

=B2*1.18 typed into 500 rows works today. When the rate changes you must find and edit 500 formulas, and you will miss some. Put the rate in a cell and refer to it, with $ or a name.

Mistake 3: a whole-column reference in the same column

=SUM(A:A) is convenient because it covers every row, including rows you add later. But type it into a cell of column A and it includes itself. Excel warns about a circular reference and the cell shows 0:

Put the total in another column, or use an exact range such as A1:A4.

Mistake 4: adding a row at the edge of a range

As the insert and delete table showed, a new row typed directly under the range is not part of =SUM(A1:A4). Insert new rows in the middle of the data, or use an Excel Table, which grows its formulas for you.

Cheat sheet

Try it yourself

Work out each answer first, then open the solution. You can type all of them into an Excel or Google Sheets worksheet.

1. Column B holds the sales of four regions (10, 20, 30, 40) and B6 holds their total. In C2 write one formula that shows North’s share of the total, then copy it down to C5.

Show solution

What the sheet shows

	A	B	C
1	Region	Sales	Share
2	North	10	10%
3	South	20	20%
4	East	30	30%
5	West	40	40%
6	Total	100

What is inside the cells (Show Formulas)

	A	B	C
1	Region	Sales	Share
2	North	10	=B2/$B$6
3	South	20	=B3/$B$6
4	East	30	=B4/$B$6
5	West	40	=B5/$B$6
6	Total	=SUM(B2:B5)

The formula is =B2/$B$6. The numerator must move down with each row, so B2 stays relative. The total must not move, so it is locked. B$6 works too, because copying down only changes the row. (Format the C column as Percentage.)

2. Principals are in A2:A4, three interest rates are across B1:D1, and the number of years is in G1. Write one formula for B2 that gives simple interest (principal x rate x years) and fill it over B2:D4.

Show solution

What the sheet shows

	A	B	C	D	E	F	G
1	Principal	5%	6%	7%		Years	2
2	1000	100	120	140
3	2000	200	240	280
4	3000	300	360	420

What is inside the cells (Show Formulas)

	A	B	C	D	E	F	G
1	Principal	5%	6%	7%		Years	2
2	1000	=$A2*B$1*$G$1	=$A2*C$1*$G$1	=$A2*D$1*$G$1
3	2000	=$A3*B$1*$G$1	=$A3*C$1*$G$1	=$A3*D$1*$G$1
4	3000	=$A4*B$1*$G$1	=$A4*C$1*$G$1	=$A4*D$1*$G$1

All three kinds of reference in one formula: =$A2*B$1*$G$1. The principal is column-locked, the rate is row-locked and the years cell is fully locked.

3. A1:A4 hold 1, 2, 3 and 4 and A5 holds =SUM(A1:A4), which shows 10. You delete row 2. What does the total show now, and what does its formula say?

Show solution

After deleting row 2 (the total moved up to A4)

	A
1	1
2	3
3	4
4	8
5

What is inside the cells (Show Formulas)

	A
1	1
2	3
3	4
4	=SUM(A1:A3)
5

The total is now 8, because the 2 is gone. The range shrank with the deleted row, so it says =SUM(A1:A3) and there is no error.

4. A1 holds 5 and B1 holds =A1*2. You cut A1 and paste it into D1. What does B1 say and show?

Show solution

B1 now says =D1*2 and still shows 10. Cutting moves the cell, and every formula that pointed at it follows it. This is the opposite of copying, which would leave B1 pointing at the old, now empty, A1.

Frequently asked questions

What does the dollar sign ($) mean in an Excel formula?

It locks part of a cell reference so it does not change when the formula is copied. $A$1 locks the column and the row, $A1 locks only the column and A$1 locks only the row.

What is the difference between relative and absolute reference in Excel?

A relative reference such as B2 changes when you copy the formula to another cell, because it is stored as a position relative to the formula. An absolute reference such as $B$2 always points at the same cell.

How do I lock a cell in an Excel formula?

Put dollar signs in the reference, or click inside it while editing and press F4. To lock a cell for good, give it a name and use the name in your formulas.

What does F4 do in Excel?

In a formula it cycles the selected reference through $A$1, A$1, $A1 and A1. Outside a formula, F4 repeats your last action. On a Mac use Cmd + T for the reference cycle.

What is a mixed reference?

A reference with one part locked and one part free, like $A2 (column locked) or A$2 (row locked). Use it when a formula must fill both down and across, as in a price and discount table.

How do I reference a cell in another worksheet?

Write the sheet name, an exclamation mark and the address: =Sheet2!A1. Use single quotes when the name has spaces, for example ='Sales 2024'!A1.

How do I refer to a whole column or a whole row?

Write A:A for the whole of column A, A:C for three columns, and 2:2 for the whole of row 2. Do not put a formula that uses one inside that same column or row, or it becomes a circular reference.

Do the same rules work in Google Sheets?

Yes. Relative, absolute and mixed references, the $ sign and the F4 toggle work the same way in Google Sheets.