Absolute Reference in Excel: How to Lock a Cell
· 7 min read
To lock a cell in a formula, put a $ in front of its column letter and its row number. $F$1 is an absolute reference Excel never shifts when you fill or copy the formula, while a plain F1 moves along with it:
=B2*(1-$F$1)The quickest way to add the signs: click inside the reference in the formula bar and press F4 (on Windows). On a Mac, use Cmd+T. Each press cycles through $F$1, F$1, $F1 and back to F1.
Relative and absolute reference: what changes when you fill
By default, references are relative. When you fill a formula down one row, every row number in it goes up by one. When you fill it right one column, every column letter moves one step. That is exactly what you want for the row's own data, and exactly what you do not want for a single fixed cell like a rate or a total.
| Reference | Type | Fill down | Fill right |
|---|---|---|---|
F1 | Relative | Changes to F2 | Changes to G1 |
$F$1 | Absolute | Stays F1 | Stays F1 |
F$1 | Mixed: row locked | Stays F1 | Changes to G1 |
$F1 | Mixed: column locked | Changes to F2 | Stays F1 |
The $ locks whatever comes right after it. $F locks the column, $1 locks the row.
Worked example: one discount rate for every order
Four orders and a discount rate of 5% typed once in F1. Column C has the formula with the rate locked, filled down. Column D has the same formula without the $ signs, filled down the same way.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Amount | After discount | Without $ signs | Discount rate | 5% |
| 2 | 2001 | 240.00 | 228.00 | 228.00 | ||
| 3 | 2002 | 180.00 | 171.00 | 180.00 | ||
| 4 | 2003 | 615.00 | 584.25 | 615.00 | ||
| 5 | 2004 | 92.00 | 87.40 | 92.00 |
In C2, write the formula with the rate locked, then fill it down to C5:
=B2*(1-$F$1)C3 becomes =B3*(1-$F$1), C4 becomes =B4*(1-$F$1), and so on. The amount moves with each row, the rate stays on F1. The results are in column C of the sheet above: 228.00, 171.00, 584.25 and 87.40.
What happens without the $ signs
Write =B2*(1-F1) and fill it down, as in column D. D3 becomes =B3*(1-F2). F2 is empty, which Excel treats as 0, so the "discount" is zero: D3, D4 and D5 show the full amounts of 180.00, 615.00 and 92.00. Only D2 gets the discount.
No error appears. The first row is right, so a quick glance looks fine. This is why you should always click the last cell of a filled range and read its formula.
The same idea is behind the classic share-of-total formula, where every row divides by one locked total cell. We walk through that case in percentage of total in Excel.
How to lock a cell in an Excel formula: three ways
- Type the
$signs yourself. Fine once you know where they go. - Press F4 while editing. Put the cursor in or next to the reference and press F4 until you see the version you want. On a Mac, Cmd+T does the same.
- Use a named cell. Select F1, type a name such as
DiscountRateinto the Name Box left of the formula bar, and press Enter.=B2*(1-DiscountRate)always points at that cell and is easier to read.
If your data is formatted as an Excel table (Insert > Table), formulas inside the table use column names like [@Amount] instead of cell addresses, and you rarely need $ signs there.
Locking ranges, not just cells
Lookups and conditional sums filled down a summary table need their data ranges locked, while the criteria cell moves. Here is a region summary built with SUMIF:
=SUMIF($B$2:$B$20, E2, $C$2:$C$20)E2 is relative, so it becomes E3, E4 and E5 as you fill down through the region names. The two ranges are absolute, so every row checks the same 19 data rows. Our SUMIF with multiple criteria guide has more on building these summaries, and how to use VLOOKUP shows the same pattern for lookups.
Mixed reference: lock only the row or only the column
A mixed reference locks one part and lets the other move. It is what you need when one formula fills a whole grid. Here is a price grid: list prices down column A, discount rates across row 1. B2:D4 is one formula filled right and down. F2:H4 is the same grid filled from a version with $A$2 fully locked.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | List price | 5% | 10% | 15% | 5%, with $A$2 | 10%, with $A$2 | 15%, with $A$2 | |
| 2 | 50.00 | 47.50 | 45.00 | 42.50 | 47.50 | 45.00 | 42.50 | |
| 3 | 120.00 | 114.00 | 108.00 | 102.00 | 47.50 | 45.00 | 42.50 | |
| 4 | 300.00 | 285.00 | 270.00 | 255.00 | 47.50 | 45.00 | 42.50 |
Write one formula in B2 and fill it right and down:
=$A2*(1-B$1)$A2locks the column, so every cell takes its price from column A, but the row moves to pick up 120 and 300.B$1locks the row, so every cell takes its rate from row 1, but the column moves to pick up 10% and 15%.
With $A$2 or B1 instead, the grid fills with wrong numbers. In F2:H4 above, $A$2 keeps every row on the 50.00 price, so the 120.00 and 300.00 rows show 47.50, 45.00 and 42.50 again. Mixed references take a little practice, so test them on a grid like this before you use them on real data.
Common mistakes
AI formulas filled down without $ signs. This is one of the most common mistakes in AI-written spreadsheets. An assistant writes a correct SUMIF in the first row of a region summary, then fills it down with relative ranges. The second row checks rows 3 to 21, the third checks rows 4 to 22, and data at the top drops out. Every row still shows a number. The quick check: the region totals should add up to the grand total. If they do not, click the last row and read its ranges.
A shifted range that is locked in place. $ signs do not make a range correct. =SUMIF($B$2:$B$20, E2, $C$3:$C$21) is locked, but the sum range starts one row below the criteria range, so every value is paired with the row underneath. Check that ranges you pair together start and end on the same rows.
Locking the cell that should move. =SUMIF($B$2:$B$20, $E$2, $C$2:$C$20) filled down returns the first region's total in every row. Only lock what must stay fixed.
Typing $ outside the reference. $=F1 or F1$ is not valid. The sign goes directly in front of the column letter or row number.
Forgetting that cut and paste is different. If you move cells by cutting and pasting, Excel updates references to follow them, absolute or not. The $ only matters when you copy or fill.
FAQ
What is an absolute reference in Excel?
A cell reference with $ signs, such as $F$1, that stays the same when you copy or fill the formula. A relative reference like F1 changes as it moves.
How do I lock a cell in an Excel formula?
Add $ before the column letter and the row number: $F$1. While editing, press F4 on Windows or Cmd+T on a Mac to add them.
What is the difference between relative and absolute references?
A relative reference moves with the formula, which suits the row's own values. An absolute reference stays fixed, which suits a single rate, a total or a lookup table.
What is a mixed reference?
A reference with only one part locked: F$1 locks the row, $F1 locks the column. Use them when one formula fills a grid across rows and columns.
Why does F4 not work on my laptop?
On many laptops F4 is a media or brightness key by default. Try Fn+F4, or type the $ signs by hand.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Each region’s share” →