Relative, Absolute and Mixed Cell References in Excel (the $ Sign) | Free Excel Course: From Beginner to Advanced

What you'll learn

Understand why a copied formula sometimes returns nonsense.
Use relative, absolute ($A$1) and mixed references.
Decide which kind of reference you need with the two-direction question.
Use named cells and references to other sheets.
Calculate each row's share of a total without getting #DIV/0!.

📥 Practice this lesson

Fill the whole two-way table with a single formula that you copy across and down. Check: the last cell (F9) should show $4,330.00, which is 20 × $200 × (1 + 8.25% sales tax).

practice-excel.xlsx · References sheet

This lesson covers the concept that separates people who "use Excel" from people who are good at it: relative, absolute and mixed references. Once it clicks, you'll write a formula once and copy it to hundreds of cells without breaking it.

4reference styles: A1, $A$1, A$1 and $A1

F4the key that cycles through all four while you type (Mac: Cmd + T)

1well-anchored formula is enough to fill an entire table

See the problem
Relative references
Absolute with $
Mixed references
Names and other sheets

The problem: a copied formula returns nonsense

A freelance consultant in Denver bills clients by the hour. Her hourly rate, $85, sits in cell F1, and each project's hours are in column B:

	A	B	C	E	F
1	Project	Hours	Amount	Hourly rate	85
2	Website redesign	12	`=B2*F1`
3	SEO audit	5	?
4	Staff training	8	?

In C2 the formula works: 12 × $85 = $1,020. But drag it down and C3 becomes =B3*F2 and C4 becomes =B4*F3. F2 and F3 are empty, so both invoices come out to $0. What happened?

Relative references (the default)

When you type B2 in a formula, Excel doesn't store "cell B2." It stores "the cell one column to the left, in the same row." So when you copy the formula one row down, the reference moves down one row too. That's exactly what you want for the hours (B2 → B3 → B4), but not for the hourly rate, which has to stay in F1.

Relative references are what let you type =B2*C2 once and fill a thousand rows. They're the norm, not a problem.

Absolute references: the $ sign

To "pin" a reference so it doesn't change when you copy it, add a $ before the column letter and before the row number:

=B2*$F$1

Now dragging down gives =B3*$F$1, =B4*$F$1… The hours move; the rate stays put.

Relative: B2Copied down it becomes B3, B4…: each formula uses the hours in its own row.
Absolute: $F$1Every copy points to the same hourly-rate cell.
Results12 × $85 = $1,020; 5 × $85 = $425; 8 × $85 = $680.

Raise the rate in F1 to $90 and all three amounts update at once.

Shortcut: F4

While typing or editing a formula, put the cursor on a reference and press F4. Each press cycles through the four styles: F1 → $F$1 → F$1 → $F1 → F1. On many laptops you need Fn + F4; on a Mac, use Cmd + T.

1. You copy the formula =B2*$E$1 down the column. What happens to $E$1?

It changes to E2, E3, E4…
It returns an error
It stays locked on E1

A $ before both the column and the row locks the reference: ideal for inputs like a tax rate, an hourly rate or an exchange rate.

Mixed references: lock only the row or only the column

Sometimes you need one part to move and the other to stay:

F1 · relativeNothing locked

Copied down: The row changes: F2, F3…
Copied right: The column changes: G1, H1…
Use it for: Data in the same row, like quantity × price.

The default

$F$1 · absoluteColumn and row locked

Copied down: Doesn't change.
Copied right: Doesn't change.
Use it for: A single input: rate, price, grand total.

Inputs

F$1 · row lockedMixed

Copied down: Doesn't change: always row 1.
Copied right: The column changes: G$1, H$1…
Use it for: The header row of a two-way table.

Column headers

$F1 · column lockedMixed

Copied down: The row changes: $F2, $F3…
Copied right: Doesn't change: always column F.
Use it for: The first column of a two-way table.

Row labels

The classic case is a two-way table. Say you want the sale price of several items (rows) at different discount levels (columns):

	A	B	C	D
1	Price \ Discount	5%	10%	15%
2	100	`=$A2*(1-B$1)`
3	250
4	400

One formula in B2, copied to the whole range B2:D4, fills the table:

$A2 always looks at column A (the price) but moves down the rows.
B$1 always looks at row 1 (the discount) but moves across the columns.

The result in D4 is =$A4*(1-D$1) = $400 × 0.85 = $340. One formula, nine correct results.

2. In a multiplication table, the numbers across the top are in row 1 and the numbers down the side are in column A. What do you type in B2 to copy across the whole table?

=$A2*B$1
=A2*B1
=$A$2*$B$1

$A2 locks column A (the row can change) and B$1 locks row 1 (the column can change). It's the textbook case for mixed references.

How to decide: the two-direction question

Before you copy a formula, ask yourself about each reference:

When I copy the formula down, should this reference change rows? If not, put a $ before the number.
When I copy it to the right, should it change columns? If not, put a $ before the letter.

"No" to both means absolute ($F$1). "Yes" to both means relative (F1). Anything else is mixed.

This is the most common use of $ at work. A furniture retailer has five stores in the Dallas–Fort Worth area. March sales are in B2:B6 and the total is in B7 (=SUM(B2:B6)). Management wants each store's share.

The first attempt is =B2/B7 in C2, copied down. In C3 you get =B3/B8: B8 is empty, so the result is #DIV/0!. The total has to stay put:

=B2/$B$7

Store	March sales	% of total (`=B2/$B$7`)
Dallas	$412,000	34.3%
Fort Worth	$356,500	29.7%
Plano	$198,300	16.5%
Arlington	$143,200	11.9%
Frisco	$90,000	7.5%
Total	$1,200,000	100%

March sales by store: two locations bring in 64% of the total.

A quick sanity check: add up the percentage column. If it isn't 100%, a reference moved. And if a sixth store opens, insert its row between two existing stores (not right above the total): SUM(B2:B6) expands on its own and $B$7 becomes $B$8 automatically.

3. You type =B2/B7 in C2 to get the share of the total and copy it down. What appears in C3?

The correct share for the second store
#DIV/0!, because the formula becomes =B3/B8 and B8 is empty
#REF!, because B7 no longer exists

B7 was relative, so it moved down a row. An empty cell counts as zero, and dividing by zero returns #DIV/0!. The fix is =B2/$B$7.

Before you copy a formula, check this

I identified the inputs (rates, totals, prices) and locked them with $ or a name.
If I'm copying in two directions, I asked the two-direction question for every reference.
I pressed F2 in the last copied cell: the colored boxes highlight the right cells.
I changed one input as a test and every result updated.

A more readable alternative: named cells

You can give a cell a name. Select F1, type HourlyRate in the Name Box (left of the formula bar) and press Enter. From then on you can write:

=B2*HourlyRate

A name behaves like an absolute reference and makes the formula understandable to anyone who opens the file. Names can't contain spaces. To edit or delete them, go to Formulas → Name Manager.

References to other sheets and workbooks

To use a value from another sheet, type the sheet name followed by an exclamation point:

=January!D20 + February!D20
='Sales 2026'!B5      ← single quotes when the sheet name has spaces

The easiest way is not to type it at all: start the formula with =, click the other sheet's tab, click the cell and press Enter. Excel builds the reference for you.

Be careful with links to other workbooks

A formula that points to another file (=[Budget.xlsx]Sheet1!$B$3) breaks if that file is moved or renamed, or if someone opens your workbook without access to it. For reports you send out, paste external data as values (Paste Special → Values).

4. How do you refer to cell B5 on a sheet named Sales?

=Sales.B5
=B5(Sales)
=Sales!B5

Sheet name, exclamation point, cell. If the name has spaces, wrap it in single quotes: ='Sales 2026'!B5.

Practice exercise: a commission table

A sales team earns commission based on each rep's tier. Set up this structure:

In A2:A6, five sale amounts: 1000, 2500, 4000, 7500, 12000.
In B1:D1, three commission rates: 3%, 5%, 8%.

Write one formula in B2 that, copied to B2:D6, calculates the commission for every sale at every rate.

See solution

=$A2*B$1

Check: D6 should show 12,000 × 8% = $960, and B2 1,000 × 3% = $30.

Summary

Relative (A1): adjusts when copied. It's the default.
Absolute ($A$1): never changes. Use it for inputs like rates.
Mixed ($A1 or A$1): locks only the column or only the row. Use it for two-way tables.
F4 (Mac: Cmd + T) cycles the styles; named cells make formulas readable.
For a share of the total, lock the total: =B2/$B$7. A #DIV/0! after copying means a missing $.

Next lesson: Cell Formatting, Conditional Formatting and Excel Tables. You'll format numbers, highlight what matters automatically and turn lists into tables that grow on their own.