Last updated July 2026. An absolute reference in Excel is a cell address locked with dollar signs, like $B$1, so it does not change when you copy the formula to other cells. A normal (relative) reference like B1 shifts as you copy; adding the dollar signs pins it in place. Type the reference and press F4 (Fn+F4 on many laptops, or Cmd+T on a Mac) to add the dollar signs instead of typing them by hand. Absolute references are what let you copy one formula down a whole column and have every row point at the same rate, tax percentage, or lookup table.
This is the single concept that decides whether a copied formula works or fills a column with wrong answers. When you convert a PDF and then apply a tax rate, a currency multiplier, or a VLOOKUP against a reference table, the moment you drag that formula down, relative references drift off target while absolute references stay put. Knowing which to use is the difference between a report that ties out and one that quietly miscalculates.
What is an absolute reference in Excel?
An absolute reference is a cell address that stays fixed when a formula is copied, written with a dollar sign before both the column letter and the row number: $B$1. If cell C2 contains =A2*$B$1 and you copy it down, the A2 part moves to A3, A4, and so on, but $B$1 stays pointing at B1 in every row. The dollar signs are a lock: they tell Excel do not adjust this part.
You use an absolute reference whenever many formulas need to read the same single cell, a rate in one box, a total to divide by, an exchange rate, a threshold. Without the lock, copying the formula drags the reference away from that cell and the math breaks one row down.
How do I make a cell reference absolute?
Click into the formula, put the cursor in the reference you want to lock, and press F4. Excel inserts both dollar signs, turning B1 into $B$1. You can also type the dollar signs yourself, but F4 is faster and avoids typos. Do this before you copy or fill the formula, so the locked reference is in place when it propagates.
F4 cycles through all four states each time you press it, which is how you also get mixed references: press once for $B$1 (fully locked), twice for B$1 (row locked), three times for $B1 (column locked), and four times to return to B1 (unlocked).
| Reference | Press F4 | What is locked | Copies |
|---|---|---|---|
| B1 | 0 (start) | Nothing (relative) | Column and row both shift |
| $B$1 | 1 time | Column and row | Never moves |
| B$1 | 2 times | Row only | Column shifts, row stays |
| $B1 | 3 times | Column only | Row shifts, column stays |
What is the difference between absolute and relative references?
A relative reference changes when copied; an absolute reference does not. Relative is the default, and it is what you want most of the time: =A2*C2 copied down becomes =A3*C3, =A4*C4, so each row multiplies its own two cells. That automatic adjustment is the whole point of filling a formula down a column, and it is correct for anything that should track its own row.
You switch to absolute only for the parts that must not move. In =A2*$B$1, the A2 is relative because each row has its own quantity, and the $B$1 is absolute because every row multiplies by the same price. Getting this split right, some parts relative, one part locked, is what a working column formula almost always looks like.
What is a mixed cell reference?
A mixed reference locks either the column or the row, but not both: $B1 keeps the column fixed while the row moves, and B$1 keeps the row fixed while the column moves. You reach for these when you fill a formula in two directions at once, across and down, such as a multiplication grid or a rate table where the row headers and column headers each need to stay attached to their own edge.
The rule of thumb: lock the part that points at a fixed row or column of inputs, and leave free the part that should track with the cell. If you fill only down, a fully absolute $B$1 is usually enough; mixed references earn their keep when you fill down and right together.
How do I lock a cell in a formula?
Locking a cell in a formula means making it absolute with dollar signs so a fill or copy cannot move it. Select the reference in the formula bar, press F4 to get $B$1, and press Enter. From then on you can drag the fill handle down the whole column and that reference stays welded to B1 in every row. This is different from locking a cell to prevent editing, which is Protect Sheet; here locking means fixing the address inside a formula.
A common use is a named lookup table: when you write =VLOOKUP(A2,$E$2:$F$20,2,FALSE) and copy it down, the A2 moves to each row but the $E$2:$F$20 table stays anchored, so every row searches the same list. Forgetting to lock the table range is the most frequent reason a copied VLOOKUP returns #N/A partway down a column.
Why do my formulas change when I copy them?
Formulas change on copy because their references are relative, which is Excel working as designed: it shifts each address by the same distance you moved the formula. If that shift is dragging a reference off a cell it should stay on, the fix is to make that reference absolute with F4 before you copy. Relative movement is a feature for row-by-row math and a bug only when it hits a cell that was supposed to hold still.
If a formula reference instead turned into #REF!, the cell it pointed at was deleted, which is a different problem from copying. When you rearrange a converted sheet, paste as values before deleting the source so live references do not collapse into #REF! errors.
How do I use F4 to lock a reference on a Mac?
On a Mac, the absolute-reference shortcut is Cmd+T, and on many Mac keyboards F4 also works if you hold the Fn key (Fn+F4) because the function keys default to hardware controls. Put the cursor in the reference inside the formula and press Cmd+T to cycle through $B$1, B$1, $B1, and B1, exactly the way F4 cycles on Windows. If neither shortcut responds, type the dollar signs manually, the result is identical.
On Windows laptops the same Fn caveat applies: if F4 changes your screen brightness instead of the reference, press Fn+F4 or find the F Lock key. The dollar signs are what matter, not how you insert them, so hand-typing $B$1 is always a valid fallback.
Cleaner formulas start with cleaner data
Absolute references keep your math pointed at the right inputs, but they cannot fix inputs that came in wrong. When a converted column arrives as text instead of numbers, even a perfectly locked formula returns zero or #VALUE!, so pair this with converting text to numbers before you build the calculation. A clean PDF to Excel conversion gives you real numbers and dates to lock onto, and a tool that pulls each line item into a spreadsheet automatically saves the retyping that introduces those errors in the first place.
Reference locking is one habit in a larger cleanup routine. For the full sequence on a freshly converted file, follow the guide to cleaning up data after a PDF conversion.