Type $A$1 instead of A1 and Excel stops moving the reference when you drag the formula down. That's the whole trick. The dollar sign is a lock. One $ in front of the column letter locks the column. One in front of the row number locks the row. Both signs lock the entire cell.
Here's why this matters. Copy =B2*C2 down a column and Excel happily shifts both references — =B3*C3, =B4*C4, and so on. That's a relative reference, and most of the time it's what you want. But the moment you need to multiply every row by a single tax rate sitting in cell E1, the relative reference becomes a bug. Excel slides E1 down to E2, then E3 — which are empty. Your formulas return zero or #DIV/0!. Fix: write the rate as $E$1.
Worth knowing: the dollar sign has nothing to do with currency. It's a syntax marker. Excel borrowed the symbol because it was already on every keyboard and rarely used inside formulas.
This guide covers the four reference states (relative, absolute, and two mixed), the F4 keyboard shortcut that toggles between them, the most common formula patterns where you must lock cells, the errors you'll get when you forget, and named ranges as a cleaner alternative for permanent lookups. If you also need a refresher on excel formulas in general, start there first — absolute refs only make sense once you understand how formulas copy.
Short answer: a cell reference that doesn't change when you copy or fill the formula somewhere else.
Excel has four reference modes, and you switch between them by adding or removing dollar signs:
If you've ever searched excel cell reference and walked away confused, that table above is the entire concept. Everything else is just when to use which one.
How it behaves: Both column and row shift when copied. If you write =A1 in cell B1 and copy it to B2, the formula becomes =A2. Copy it to C2 and it becomes =B2.
When to use it: Most of the time. Row-by-row calculations — total = price × quantity, profit = revenue − cost — work naturally with relative refs.
Default state. Excel writes references this way unless you explicitly add dollar signs.
How it behaves: Nothing shifts. $A$1 stays $A$1 no matter where you copy the formula.
When to use it: Tax rates, exchange rates, commission percentages, lookup table boundaries, totals you divide by. Any value that lives in one fixed cell and gets used by many formulas.
Example: =B2*$E$1 multiplies every row in column B by the rate in E1. Drag the formula down — E1 stays put.
How it behaves: Column letter is locked, row number shifts. Copy $A1 down and it becomes $A2, $A3. Copy it sideways and it stays $A1.
When to use it: Lookup formulas that scan a single column. Conditional formatting rules where you want to highlight an entire row based on one column's value.
How it behaves: Row number is locked, column letter shifts. Copy A$1 sideways and it becomes B$1, C$1. Copy it down and it stays A$1.
When to use it: Multiplication tables. Headers that repeat across columns. Any formula that pulls from a single header row.
Highlight a reference inside a formula and press F4. Excel cycles through the four states in order: A1 → $A$1 → A$1 → $A1 → back to A1. Keep tapping until you land on the lock pattern you need.
This is the single most useful keystroke in Excel for anyone who writes formulas more than once a week. Typing dollar signs manually works, but it's slower and you'll fat-finger one eventually. Hit F4 instead. The excel shortcuts cheat sheet lists it alongside the other keys worth memorizing — anchor cell, autosum, fill down.
On Mac, F4 is often grabbed by the operating system. Two workarounds, both reliable:
Pick whichever fits your muscle memory. Cmd+T is what Microsoft documents, but plenty of Mac users prefer Fn+F4 because it matches what they do on Windows.
F4 only works on the reference your cursor is touching. Click the formula bar, place your text cursor anywhere inside the reference you want to toggle (you don't need to highlight all of it), then press F4. If your cursor is somewhere else in the formula — say, on a function name — F4 does nothing useful.
One more trick. Excel remembers which references have been locked, so you can build a formula with mixed lock patterns by clicking through each one and pressing F4 the right number of times. Three taps gets you a column-locked mixed ref. Two taps gets you row-locked.
Tax rate, sales commission percentage, or any single multiplier applied to a column of values. Store the rate in one cell, lock it with $, drag the formula down.
Converting a column of USD prices to EUR or GBP. The rate sits in a header cell — every conversion formula needs to point at the same locked reference.
The lookup table range must never shift. Lock the entire range so VLOOKUP keeps scanning the same data when you copy the formula down a column of lookup values.
Both the range you're scanning and the criteria range get locked. Only the criteria value itself shifts as you copy the formula across summary rows.
Calculating each row's share of a total. The total sits in one cell — every percentage formula divides by that same locked reference.
Mixed references shine here. Lock the row of one factor and the column of the other — the same formula fills an entire grid correctly.
Most spreadsheet errors aren't broken formulas. They're correct formulas with the wrong lock pattern. Below are the four functions where missing dollar signs cause the most damage, with the right pattern spelled out.
This is the classic. You build a lookup formula that works perfectly on row 2, then drag it down. Suddenly row 5 returns #N/A. Why? Excel slid the table range down with the formula. $D$2:$F$100 became $D$5:$F$103 — and the value you're looking for now sits above that range. The lookup key (the first argument) should stay relative because each row has a different key, but the table array must be absolute. The same rule applies to vlookup excel and to the newer XLOOKUP function.
A summary table that sums sales by region or month needs the criteria range locked and the sum range locked. Only the criteria itself stays relative because each summary row references a different region or month name. Forget the lock and your second summary row sums a different chunk of data than your first — the totals look plausible but they're wrong.
Same principle. =INDEX($B$2:$B$100,MATCH(A2,$A$2:$A$100,0)). Both the result array and the search array stay put. The lookup key shifts. This combination is faster than VLOOKUP on big sheets and handles left-of-key lookups, but the lock pattern matters even more because INDEX/MATCH silently returns the wrong row instead of erroring out when ranges slide.
You have a column of sales figures and a single commission rate in cell G1. Formula in C2: =B2*$G$1. Drag down. Every row multiplies its own sales figure by the same locked rate. Change the rate in G1 once and every commission recalculates. This is the cleanest argument for absolute refs over hard-coded numbers — change one cell, change every formula.
Three errors come up over and over. Recognizing them tells you instantly that an absolute reference is missing. They show up in different places — sometimes immediately when you press Enter, sometimes only after you copy a formula 200 rows down. Spotting the pattern is faster than auditing every reference one at a time. Each error type below has a different root cause, but the fix is the same family of changes: add dollar signs where they belong, or use named ranges for anything you reference more than twice.
The reference walked off the edge of the sheet or into a deleted row. Excel can't find the cell anymore, so it returns #REF!. Often happens after a formula was copied down a long column without locking — by row 1,000 the reference points to row 1,103 or wherever, and any sheet rearrangement breaks it. Locking with $ prevents the walk in the first place.
The grand total reference slid down to an empty cell. Excel tries to divide by zero and errors out. Almost always a missing $ on the divisor — your formula =B2/B20 should have been =B2/$B$20.
The most dangerous bug. No error, just wrong numbers. Usually happens with SUMIF or INDEX/MATCH when ranges slide but still hit valid data. Spot-check totals against a manual sum — if a summary row's total doesn't match the obvious answer, look at the lock pattern. The fix is almost always pressing F4 on each range until the dollar signs appear.
If you also need to physically prevent users from editing certain cells (separate concept from absolute references but often confused), read how to lock cells in excel. That's about sheet protection. Dollar signs are about formula behavior. Different problems, different solutions.
Before you drag any formula, look at every reference inside it and ask: "Does this value live in a fixed cell or does it move with the row?" Fixed → lock it with $. Moves with the row → leave it relative. Three seconds of thought saves an hour of debugging later.
If you're not sure, build the formula in one cell, drag it to a second cell, and check whether the result makes sense. If the second result is obviously wrong, you found your missing dollar signs.
Most people learn relative refs, then jump straight to full absolute. Mixed refs get skipped. That's a mistake. Mixed references are the right tool for any two-dimensional grid where one axis stays put and the other shifts.
Build a 10×10 multiplication table. Numbers 1–10 across row 1. Numbers 1–10 down column A. In cell B2, you want the product of B1 (column header) and A2 (row header). Write =$A2*B$1. Drag the formula across row 2 — the column letter of the row header stays locked ($A) while the column letter of the column header shifts. Drag down — the row number of the column header stays locked ($1) while the row number of the row header shifts.
One formula. Fills 100 cells. Every product correct. That's mixed references doing what absolute references can't and what relative references can't.
Project monthly cash flow over 12 months with a single growth rate in cell B1. Month 1 revenue in A2. Formula in B2: =A2*(1+$B$1). That works for one column. But if you want to model growth for multiple starting amounts (column A) across multiple growth-rate scenarios (row 1), use mixed: =$A2*(1+B$1). Drag in two dimensions and the grid populates with every combination.
Highlight every row where column C exceeds 100. Conditional formatting rule: =$C2>100. The column letter stays locked at C (you always check column C), the row number stays relative (you check each row separately). Without the column lock, the rule would shift across to column D, E, F as it evaluates each cell — useless.
If you find yourself locking the same cell or range in formula after formula, give it a name. Name a single cell TaxRate and your formulas become =B2*TaxRate instead of =B2*$E$1. Named ranges are inherently absolute — they don't shift when you copy formulas, ever.
Select the cell or range. Click in the Name Box (top-left, just left of the formula bar). Type a name. Press Enter. Done. The name is now valid across the entire workbook.
Readability. =Revenue*Margin tells you what's happening. =B2*$E$1 doesn't. Names also survive sheet rearrangement — if you cut and paste the rate cell to a different location, the name follows it. A hard-coded $E$1 would still point to the old cell address even after the data moved.
Names are best for stable references that get used many times — tax rates, exchange rates, header cells, lookup tables. They're overkill for one-off formulas. If you're locking a cell only once or twice, just use $ signs. If you're locking it in 50 places across 10 sheets, name it.
The newer xlookup-excel function handles some of the same problems VLOOKUP solves, but you still need absolute references for any lookup array that gets reused across multiple formulas. The function changed. The locking principle didn't.
Real spreadsheets get messy. You inherit a file from someone else, drag a formula down a thousand rows, paste data from a different workbook, and suddenly numbers stop adding up. Here's how absolute references fit into the trickier corners of Excel work.
Move a formula from Sheet1 to Sheet2 and absolute references stay locked to their original sheet by default — =Sheet1!$A$1. If you want the formula to reference cells on the new sheet, edit it manually. Excel won't repoint the sheet name for you. This trips people up when copying summary formulas between monthly tabs.
Paste a formula with an absolute reference into a fresh workbook and Excel preserves the original workbook name in the reference — something like =[OldFile.xlsx]Sheet1!$A$1. The new file now has an external link, which can slow things down and cause #REF! errors if the source file moves. Strip external links via Data → Edit Links, or paste values only if you don't need the formula to keep updating.
If you forgot to lock a reference across dozens of formulas, use Find & Replace. Press Ctrl+H, search for the unlocked reference (say, E1), replace with the locked version ($E$1), and choose Look in: Formulas. Excel updates every matching reference at once. Warning — this also catches partial matches, so test on a copy first.
Dragging the fill handle copies the formula and shifts relative refs while preserving absolute refs — exactly what you want. But double-clicking the fill handle (the trick that auto-fills down to the last row of an adjacent column) can behave oddly with very long ranges. If you see unexpected results, switch to plain copy-paste or use Ctrl+D (fill down) on a selected range.
Modern Excel handles array formulas with dynamic spill ranges — write one formula and it fills multiple cells automatically. The locking rules still apply: any reference that should stay put across the spill range needs dollar signs. With older array formulas entered via Ctrl+Shift+Enter, the same logic holds.
Absolute references are the difference between a spreadsheet that works and one that quietly returns wrong numbers. The mechanic is small — one dollar sign, two if you want full lock — but the impact is everywhere. Every multi-row calculation, every lookup, every percentage of a total, every tax rate or commission formula depends on getting the lock pattern right.
Three habits will save you most of the trouble: press F4 instead of typing dollar signs manually, name any cell you reference more than twice, and always test a formula in row 2 and row 10 before assuming the rest of the column is correct. Do that, and the #REF! errors and silently-wrong totals mostly go away.
An absolute cell reference is a cell address that doesn't change when you copy or fill a formula to other cells. You create one by placing dollar signs before both the column letter and the row number — $A$1 instead of A1. Use it whenever a formula needs to point at the same single cell regardless of where the formula gets copied.
Two ways. Type the dollar signs manually — $A$1 — or click on the reference inside the formula bar and press F4. F4 cycles through all four reference states (relative, absolute, mixed-row, mixed-column) each time you press it. On Mac, use Cmd+T or Fn+F4 instead.
The dollar sign is a lock. It tells Excel "don't shift this part of the reference when the formula gets copied." A $ before the column letter (like $A1) locks the column. A $ before the row number (like A$1) locks the row. Both signs ($A$1) lock the whole cell. It has nothing to do with currency.
Use absolute references for any value that lives in one fixed cell and gets reused by many formulas — tax rates, exchange rates, commission percentages, grand totals you divide by, and lookup table boundaries in VLOOKUP and SUMIF. Use relative references for row-by-row calculations where each row uses its own data, like =B2*C2 for total = price × quantity.
F4 on Windows. On Mac, use Cmd+T or Fn+F4. Click on the reference inside the formula (you don't need to highlight the whole thing — just place your cursor inside it), then press the shortcut. Each press cycles through the four states: A1 → $A$1 → A$1 → $A1 → back to A1.
A mixed reference locks either the row or the column, but not both. $A1 locks the column (A stays put when copied sideways, row shifts when copied down). A$1 locks the row (1 stays put when copied down, column shifts when copied sideways). Mixed refs are essential for two-dimensional grids like multiplication tables and financial models with rows and columns of inputs.
You forgot to lock the table array. When you copy =VLOOKUP(A2,D2:F100,3,FALSE) down a column, Excel shifts the table range to D3:F101, then D4:F102, and so on. By row 5, your lookup data has slid off the original range and the lookup fails. Fix it by writing =VLOOKUP(A2,$D$2:$F$100,3,FALSE) — the dollar signs freeze the table array while letting the lookup key (A2) stay relative.
For values you reuse in many formulas across many sheets — yes. Named ranges are easier to read (=Revenue*Margin vs =B2*$E$1), survive moving the source cell, and update everywhere when you change the named cell. For one-off locks in a single formula, plain $ signs are faster. Both approaches lock the reference — names are just a clearer way to do it for permanent values.