How to Make References Absolute in Excel (All at Once)
F4 cycles the reference under the cursor. One. When a lookup range should have been locked before the formula was filled down, that means visiting every formula in turn — and the symptom is a column of results that goes quietly wrong further down, because the range walked off the bottom of the table.
Last updated: October 2026
| Before | → | After (absolute) |
|---|---|---|
| =VLOOKUP(A2,F1:G50,2,0) | → | =VLOOKUP($A$2,$F$1:$G$50,2,0) |
| =SUM(Sheet2!B2:B9) | → | =SUM(Sheet2!$B$2:$B$9) |
The four forms, and when each is right
- $A$1 — absolute. A lookup table, a tax rate, anything that must not move when the formula is copied.
- A1 — relative. The default, and what you want when a formula should follow the row it is on.
- $A1 — column locked. Fill across without leaving the column: rate cards, comparison grids.
- A$1 — row locked. Fill down against a fixed header row.
What it will not touch
Text inside quotes is left exactly as it is — a formula containing "Total for A1" must not acquire dollar signs inside its label. Sheet names are kept and only the cell part is changed, both ends of a range are treated the same way, and a cell that merely looks like a formula because someone typed =A1 as text is left alone.
The 1-click way (Tellsheet)
Reference Style
Select the formulas and run Reference Style, choosing absolute, relative, column-locked or row-locked. Every formula in the selection is rewritten at once, the preview shows exactly which ones change, and the summary says how many were already in the style you asked for.
Frequently asked questions
Isn't that what F4 does?
Yes — for one reference at a time.
Does it change text in quotes?
No, quoted text is left exactly as it is.
What about other sheets?
Sheet names are kept; only the cell part changes.
Does it touch values?
No — only cells the host reports as formulas.
Related Excel guides
F4, for the whole sheet
Reference Style rewrites every formula at once — with a preview of exactly what changes.
Get Tellsheet free See pricing