Absolute vs Relative Cell References in Excel

Introduction

You write a formula once, copy it down a column, and every row calculates correctly. That's relative references doing the work. But sometimes copying a formula breaks it because a cell that should stay fixed keeps shifting. That's where absolute references and the $ sign come in.

The Dataset

Here's a simple commission tracker. Each salesperson has a revenue figure, and the company pays a flat commission rate stored in cell B1.

	A	B
1	Commission Rate	10%
2
3	Salesperson	Revenue
4	Sarah	8500
5	James	6200
6	Maria	9400
7	David	5100
8	Priya	7800

We want to calculate each person's commission in column C. This example will show exactly why reference types matter.

Relative References

By default, every cell reference in Excel is relative. That means when you copy a formula, Excel adjusts the references based on where you paste it.

In cell C4, enter:

=B4 * B1

This multiplies Sarah's revenue by the commission rate. The result is 850. So far, so good.

Now copy C4 down to C5 through C8. Here's what Excel produces:

Cell	Formula After Copy	Result
C4	`=B4 * B1`	850
C5	`=B5 * B2`	0
C6	`=B6 * B3`	#VALUE!
C7	`=B7 * B4`	43,350,000
C8	`=B8 * B5`	48,360,000

Something went wrong. The B4 part shifted correctly to B5, B6, B7, B8. But B1 also shifted to B2, B3, B4, B5. Excel moved both references down by the same amount because both are relative. B2 is empty (so C5 returns 0), B3 contains the text "Salesperson" (so C6 throws #VALUE!), and B4 and B5 hold revenue values (so C7 and C8 multiply two revenues together, producing wildly wrong numbers).

This is the most common formula mistake in Excel. The fix is to make the commission rate reference absolute.

Absolute References

An absolute reference uses dollar signs to lock a cell address. When you copy the formula, the locked reference stays exactly where it is.

The syntax is $B$1. The $ before the column letter locks the column. The $ before the row number locks the row.

Go back to C4 and change the formula to:

=B4 * $B$1

Now copy it down:

Cell	Formula After Copy	Result
C4	`=B4 * $B$1`	850
C5	`=B5 * $B$1`	620
C6	`=B6 * $B$1`	940
C7	`=B7 * $B$1`	510
C8	`=B8 * $B$1`	780

Every row now multiplies its own revenue by the fixed commission rate in B1. The B4 part shifts as expected (B5, B6, B7, B8), but $B$1 stays locked in every copy.

Key takeaway: Use absolute references when a formula points to a fixed value, like a tax rate, exchange rate, or target number, that every row should reference.

Mixed References

Sometimes you need to lock just the row or just the column, not both. These are called mixed references.

$B4: The column is locked (always column B), but the row adjusts when copied.
B$1: The row is locked (always row 1), but the column adjusts when copied.

Mixed references are most useful in multiplication tables and two-dimensional formulas. Here's an example.

You're building a price matrix. Row 1 has quantities, column A has unit prices, and you need every cell to calculate quantity times price.

	A	B	C	D
1		5	10	20
2	12
3	25
4	50

In cell B2, enter:

=B$1 * $A2

B$1 locks row 1 (the quantity row). When copied down, the row stays at 1. When copied right, the column shifts from B to C to D.
$A2 locks column A (the price column). When copied right, the column stays at A. When copied down, the row shifts from 2 to 3 to 4.

Copy B2 across and down to fill the entire grid:

	A	B	C	D
1		5	10	20
2	12	60	120	240
3	25	125	250	500
4	50	250	500	1000

One formula, copied nine times, fills the whole table correctly. Here's what Excel actually wrote in each cell:

Cell	Formula
B2	`=B$1 * $A2`
C2	`=C$1 * $A2`
D2	`=D$1 * $A2`
B3	`=B$1 * $A3`
C3	`=C$1 * $A3`
D3	`=D$1 * $A3`
B4	`=B$1 * $A4`
C4	`=C$1 * $A4`
D4	`=D$1 * $A4`

Every formula correctly picks its own row's price from column A and its own column's quantity from row 1.

The F4 Shortcut

You don't have to type dollar signs manually. Click on a cell reference in the formula bar and press F4 to cycle through the four reference types:

Press	Reference	Type
Once	`$B$1`	Absolute (row and column locked)
Twice	`B$1`	Mixed (row locked)
Three times	`$B1`	Mixed (column locked)
Four times	`B1`	Relative (nothing locked)

This works while editing any formula. Click on the reference you want to change, press F4 until it shows the type you need, then press Enter.

Quick Reference

Reference	Column	Row	When to Use
`B1`	Shifts	Shifts	Most formulas (default behavior)
`$B$1`	Locked	Locked	Fixed values like rates, targets, constants
`B$1`	Shifts	Locked	Row of headers in a 2D table
`$B1`	Locked	Shifts	Column of labels in a 2D table

Common Mistakes

Forgetting to lock a rate cell. This is the commission rate problem from above. If a formula references a fixed value that every row should use, make it absolute.
Locking everything. Not every reference needs dollar signs. If you lock a reference that should shift, the formula will pull from the same cell in every row instead of moving down with the data.
Locking the wrong part. In mixed references, locking the row when you should lock the column (or the reverse) produces wrong results that can be hard to spot because the formula still returns numbers.

Conclusion

Relative references shift when you copy a formula. Absolute references stay fixed. Mixed references lock one dimension and let the other shift. Once you know which type to use, you can write a formula once and copy it across an entire table without errors.

For more on building formulas with conditions, check out our guide on IF, AND, and OR, or see our guide on COUNTIF and SUMIF for conditional counting and summing.