Relative vs Absolute vs Mixed References in Excel

The difference between a relative vs absolute reference is what happens when you copy a formula. A relative reference (A1) moves with the formula, an absolute reference ($A$1) stays locked on one cell, and a mixed reference ($A1 or A$1) locks just the column or just the row. This guide is for anyone who has copied a formula down and watched it break, and for anyone preparing for an Excel test, where this skill comes up again and again.

Relative vs absolute reference vs mixed: the differences at a glance#

All three point at a cell. They only differ in which part of the address is allowed to change when the formula is copied.

A useful way to read the dollar sign: $ locks whatever comes right after it. In $A1 the $ sits before the column letter, so the column is locked. In A$1 it sits before the row number, so the row is locked.

Why does my formula break when I copy it down?#

It breaks because a relative reference moves to a cell that doesn't hold what you meant. Here is a small price list with a tax rate of 8% in F1:

In C2 you type =B2*(1+F1) and get 970.92. Then you fill down with Ctrl+D (⌘D on a Mac). The formula in C3 becomes =B3*(1+F2). Both references moved down one row. B3 is right, but F2 is empty, so Excel treats it as zero and the monitor shows 249, with no tax at all. There is no error message, which is what makes this mistake dangerous.

When to use a relative reference#

Use a relative reference when every row (or column) should read its own cells. That is most formulas.

• =C2*D2 for hours times rate, filled down so row 3 uses C3*D3.
• =B2-C2 for revenue minus cost on each line.
• =SUM(B2:B5) filled right, so the next column totals C2:C5.

Relative is the default because it is what you want most of the time. You only add dollar signs for the parts that must not move.

When to use an absolute reference#

Use an absolute reference when every copy of the formula must read the same single cell or the same fixed range.

Fix the tax example by locking F1:

=B2*(1+$F$1)

Fill this down and C3 becomes =B3*(1+$F$1): the price moved, the tax rate stayed. The monitor now shows 268.92. Change the rate in F1 to 10% and every row updates, because they all point at that one locked cell.

Absolute references also matter for ranges inside functions. When you fill a lookup or a SUMIFS down a column, the data range must stay put:

=SUMIFS($C$2:$C$13,$B$2:$B$13,E2)

The data ranges are locked, and only the criteria cell E2 moves. This is the pattern behind formula-based reports, which we compare with pivot tables in Pivot Table vs SUMIFS. The same locked-range habit applies when you total a list of distinct values made with UNIQUE.

When to use a mixed reference#

Use a mixed reference when you fill one formula both down and across, and the inputs sit in one column and one row.

Say prices run down column A and discount rates run across row 1:

In B2, type:

=$A2*(1-B$1)

• $A2 keeps the column fixed on A (the prices) but lets the row change as you fill down.
• B$1 keeps the row fixed on 1 (the discounts) but lets the column change as you fill across.

Fill right to D2, then fill B2:D4 down. C3 now holds =$A3*(1-C$1), which is 80 × 0.9 = 72. One formula fills the whole grid correctly.

A fully absolute version, =$A$2*(1-$B$1), would put the same number in every cell. A fully relative version, =A2*(1-B1), would drift away from the labels after the first cell. Summary grids built with COUNTIFS or SUMIFS use exactly this idea, with labels like $G8 and H$7.

How to add $ signs fast with F4#

You don't have to type the dollar signs. While you edit a formula, put the cursor on (or just after) a reference and press:

• Windows: F4. On some laptops you hold Fn as well.
• Mac: ⌘T, or fn+F4 on a laptop keyboard. (In a web browser, ⌘T opens a new tab, so in ExcelReady's practice grid use fn+F4 or type the $ yourself.)

Each press moves to the next type:

1. A1 becomes $A$1 (absolute)
2. then A$1 (row locked)
3. then $A1 (column locked)
4. then back to A1 (relative)

You can press it on a whole range too: with the cursor in B2:B13, one press gives $B$2:$B$13.

Common mistakes with relative and absolute references#

Most reference bugs are one of these:

1. Forgetting to lock a single input cell. The first row is right and every other row is wrong, often with no error. Always check the second row of a filled formula.
2. Locking the cell that should move. =$B$2*(1+$F$1) filled down gives the laptop's price in every row. Only lock what must stay the same.
3. Locking the wrong half. When you fill down, the row must be locked. =B2*(1+$F1) still breaks, because $F1 lets the row move. F$1 or $F$1 works.
4. Not locking lookup ranges. =VLOOKUP(E2,A2:C10,3,FALSE) filled down becomes A3:C11, then A4:C12, and the top rows drop out of the search. Lower rows may return #N/A.
5. Thinking $ means currency. In a reference, $ only means "locked". The currency format is a separate setting.
6. Expecting $ to matter when you move cells. If you cut and paste the cell a formula points to, Excel updates the reference whether it has $ signs or not. The dollar signs only control what happens when you copy or fill the formula.

Is this asked in Excel interviews?#

Yes. "What is the difference between a relative and an absolute reference?" is one of the most common Excel interview questions, and practical tests often hide it inside a fill-down task. You might be asked to add tax to 20 rows or build a commission table, and the only way to get every row right is a locked reference. See our list of Excel interview questions with answers for how it is usually phrased.

In finance work, models are built around input cells (growth rates, exchange rates, assumptions) that dozens of formulas point to, so locking references correctly is a basic expectation. Our guide to the Excel skills finance jobs test covers what else comes up.

Our recommendation#

Start every formula relative, then ask one question before you copy it: "Which part of this must stay the same?" Lock only that part.

• One input cell, like a rate: lock both parts, $F$1.
• A data range inside SUMIFS, COUNTIFS, VLOOKUP or XLOOKUP: lock both parts, $B$2:$B$13.
• A grid with labels down the side and across the top: use mixed references, $A2 and B$1.
• Everything else: leave it relative.

Then check the second row and the last cell of anything you fill. If those are right, the rest usually is.

Practise it#

Try the mixed-reference grid exercise on this page first. Then work through the relative vs absolute references lesson, where you break a tax formula, fix it with $F$1 and fill a discount row with a locked price. When that feels easy, the summary grid skill check has you fill a whole COUNTIFS and SUMIFS grid with one mixed-reference formula, the way a job test would.