You've built a spreadsheet that finally works. The formulas are clean, the totals match, and you're proud of it. Then you copy a cell down — and the answer in row 12 is wrong. Or you hand the file to a teammate and they accidentally type over the formula in cell H3. Same root problem, two different fixes. "Locking" a formula in Excel is one phrase that hides two completely separate techniques.
The first kind of lock is the absolute reference: pinning a cell address with dollar signs so the formula doesn't shift when you copy it. $A$1 means "always cell A1, no matter where I drag this". The F4 key cycles through the lock combinations in a heartbeat. That's the lock you use while you're still building.
The second kind of lock is sheet protection: telling Excel which cells users are allowed to edit and which ones are off-limits. The formula's contents stay safe behind a checkbox in Format Cells > Protection, but only once you've turned on Review > Protect Sheet. That's the lock you use when you ship the file.
This guide unpacks both — when to use which, how the F4 toggle works, the unlock-inputs-first pattern that lets reviewers type in some cells but not others, the Hidden checkbox that conceals the formula bar text, and a few traps that have caught even seasoned spreadsheet veterans. By the end you'll know exactly which lock to reach for the next time someone says "lock the formula". And you'll never paste a formula into row 12 and wonder why the answer's wrong again.
Ask ten Excel users "how do I lock a formula?" and you'll get two camps of answers. Camp one talks about dollar signs: $A$1, $B$2, the F4 key. Camp two talks about Format Cells, the Locked checkbox, and Protect Sheet. They're both right. They're also doing very different things.
An absolute reference locks the formula's references. When you copy a formula with $A$1 down a column, every copied cell still points at A1. Drag =A1*B1 (no dollar signs) from C1 to C2 and it becomes =A2*B2. Drag =$A$1*B1 instead and C2 reads =$A$1*B2 — the A1 stays put because the dollars pinned it.
Sheet protection locks the formula's cell. Once you Protect Sheet, users can't delete, overwrite, or even click into a locked cell (depending on the options you choose). The formula itself is unchanged — what changes is who's allowed to touch it.
Here's the trap most people fall into. They want both. They want their VLOOKUP to not break when they copy it and they want to stop the boss from accidentally overwriting it on Monday morning. That needs both techniques applied together, in the right order, with the right cells unlocked first so users can still enter data. We'll walk through that exact recipe in a moment.
Quick clarifier on terminology. Some people use the word "freeze" for what we're calling "lock". Freeze formula, freeze a cell in a formula, freeze reference — they all mean the same thing as absolute reference. Don't confuse it with Freeze Panes, which is a totally different feature that pins rows or columns on screen as you scroll. Different commands, different keyboard paths, different goals. The terminology overlap is unfortunate, but in this guide "lock" and "freeze" mean the dollar-sign kind unless we say sheet protection.
$A$1 absolute reference stops the formula from changing when you copy it. Use it while you're writing the spreadsheet.
Protect Sheet stops users from editing the cell. Use it when you're sharing the spreadsheet.
You usually want both — applied in this order: write formulas with absolute refs, unlock the input cells, then Protect Sheet.
Type =A1 in a cell. That's a relative reference. Copy it anywhere and Excel adjusts both the column letter and the row number relative to the move. Down one row? A1 becomes A2. Right two columns? A1 becomes C1. Excel is being helpful — that's the whole reason formulas can be dragged in the first place.
Sometimes you don't want help. You're calculating tax for a list of prices, and the tax rate sits in cell B1. Every formula needs to multiply its price by B1, exactly. Drag =A2*B1 down one row and you'll get =A3*B2, which now multiplies the next price by an empty cell. The total goes haywire.
The fix is dollar signs. Type =A2*$B$1 instead. The $B$1 is absolute — it never changes when you copy. Drag down and you get =A3*$B$1, =A4*$B$1, =A5*$B$1 — every formula keeps reaching for the same rate. The price reference (A2, A3, A4) still walks down with you. That's the win.
The dollar sign can lock either part independently. Four combinations exist for any reference:
That last two are called mixed references, and they're the secret to multiplication tables, two-way lookups, and conditional-format formulas that need to reach the same row or the same column across a grid.
You don't have to type the dollars by hand. Click into the cell address inside your formula bar — say you're sitting on A1 — and press F4. Excel inserts both dollars, making it $A$1. Press F4 again and it cycles to A$1 (row locked only). Again to $A1 (column locked only). One more press and you're back to plain A1. Four states, one toggle, no typing.
Both column and row shift when you copy. Use for normal arithmetic where every row of the formula points at the data on its own row.
Neither column nor row shifts. Pin tax rates, exchange rates, constants — anything stored in one cell that all formulas in a range need to reach.
Column stays put; row walks. Useful when you copy a formula across columns but want the reference to keep reading from column A.
Row stays put; column walks. The pattern for multiplication tables where you multiply each column header by the same top row.
Click into the reference inside the formula bar, then press F4. Each press steps through A1 → $A$1 → A$1 → $A1 and back to A1.
Name cell B1 "TaxRate" via the Name Box. Then write =A2*TaxRate — it behaves like an absolute reference without needing dollars.
The F4 key is the fastest way to apply or remove dollar signs without retyping the reference. The motion is small but precise: you must click inside the reference text in the formula bar first, then press F4. If your cursor is on the cell itself rather than inside the formula text, F4 in some contexts repeats the last action instead of toggling reference type. That's a different (and useful) feature, but it's not what you want here.
The exact workflow looks like this. Click the cell containing your formula — say it's =A2*B1. Press F2 (or double-click) to enter edit mode. The references inside the formula now have colored borders around the source cells. Click the cursor anywhere inside or next to the B1 text. Press F4. Excel turns it into $B$1, and the formula bar updates immediately. Press F4 again to get B$1, again for $B1, again for B1 (back to start).
The F4 cycle doesn't care which way you came. Whether B1 became $B$1 because you pressed F4 once or because you typed the dollars manually, the next F4 always advances to the next state. So you can use it for quick fixes: spot an absolute reference that you actually wanted to be relative? Click into it, F4 a few times, you're done.
On Mac, F4 is sometimes mapped to system actions (Mission Control, Launchpad, brightness). In that case you'll need fn+F4 instead. Or change the toggle entirely to Cmd+T, which is the Mac Excel built-in alias for the same reference-cycle behaviour. Test both in your environment — different Mac models map the F-row differently, and some keyboards need the System Preferences "Use F keys as standard" option enabled.
One trick advanced users love: F4 works on entire selections, not just single references. Highlight a range like A1:B10 inside your formula and press F4 — both ends get locked to $A$1:$B$10 in one move. Saves four F4 presses (one per dollar sign) every time you have a range argument in VLOOKUP, INDEX, SUMIFS, and so on.
Step 1: Click the cell containing the formula.
Step 2: Press F2 (or double-click) to enter edit mode.
Step 3: Place the cursor inside the reference text (e.g. inside "B1").
Step 4: Press F4. Each press cycles A1 → $A$1 → A$1 → $A1 → A1.
Tip: Select a whole range like A1:B10 first to lock both ends in one F4 press.
Primary: fn+F4 if your function keys are set as media keys (default on most MacBooks).
Alternate: Cmd+T does the same cycle and avoids the fn-key hassle.
System fix: Apple menu > System Settings > Keyboard > "Use F1, F2, etc. keys as standard function keys" lets F4 work without fn.
Standalone keyboards: Many third-party Mac keyboards already pass F4 straight through.
F4 works in Excel for the web in most browsers — click into the reference inside the formula bar and press F4.
Browser conflict: Chrome and Edge use F4 for the address bar in some contexts. If F4 misfires, click directly inside the spreadsheet grid first to grab focus.
Manual fallback: Type the dollar signs yourself if F4 keeps getting hijacked.
Absolute references handle the math. Sheet protection handles the humans. The pattern: lock the formula cells, leave the input cells unlocked, then turn on Protect Sheet. Users can type into the inputs, the formulas update, but the formulas themselves are untouchable.
Here's what most people miss on their first attempt. Every cell in a fresh Excel workbook is already set to Locked — yes, every one. The Locked checkbox in Format Cells > Protection has zero effect, though, until you also turn on Protect Sheet. That two-step design lets you mark cells as locked or unlocked at your leisure during the build, then flip the master switch when you ship.
The implication: if you Protect Sheet without unlocking input cells first, the user can't type anywhere. The whole sheet becomes read-only. That's why the standard recipe is unlock inputs first, then protect sheet, not the other way around.
The full recipe in order:
That's it. Users can now type into the cells you unlocked. They can't change the formulas, can't delete them, can't even read them if you ticked Hidden. The protection holds until someone goes to Review > Unprotect Sheet and enters the password (or just clicks OK if you didn't set one).
Want password protection for the workbook structure too — stopping anyone from adding, deleting, or hiding sheets? That's Review > Protect Workbook, a separate button with its own password. Use both for the full belt-and-braces.
The default-locked design of Excel cells trips up almost every new user. You'd expect that unlocking a cell would mean "the user can edit this" and locking would mean "they can't". But the Locked checkbox does nothing on its own. It's the combination of that checkbox and Protect Sheet that decides who edits what.
Picture this. A budget spreadsheet with three columns: Item, Cost, Total. The Total column has a formula =B2*1.2 in every row. You want the user to be able to type into Cost (column B) but not touch Total (column C). The build steps go like this:
Select column B (the cost inputs). Format Cells > Protection > uncheck Locked > OK. Now column B is marked unlocked, but Protect Sheet isn't on, so it still looks identical to every other column. That's fine — the marking is set, ready for protection.
Now Review > Protect Sheet. Set a password if you like. Click OK. The moment protection is active, the cells you marked unlocked become the only ones a user can click into. Try clicking into column C and you get an Excel beep and a message: "The cell or chart you're trying to change is on a protected sheet." Exactly what you wanted.
The pattern scales. Got 20 input cells scattered across the sheet? Ctrl-click them to multi-select before opening Format Cells. Got 1,000 input cells in a column? Select the whole column, do the protection toggle once. Got a complex form with inputs in odd places? Use F5 (Go To) Special > Constants to grab everything that's not a formula in one go, then run the unlock step on that selection.
One refinement worth knowing: the Protect Sheet dialog has a list of allowed actions. By default users can select unlocked cells and select locked cells (so they can read formula results) but can't do much else. Check the boxes for sort, autofilter, format columns, etc., if you want power users to keep some flexibility while still being blocked from editing the formula cells themselves.
Format Cells > Protection has two checkboxes, not one. The first is Locked, which we just covered. The second is Hidden. Tick Hidden on a formula cell, then Protect Sheet, and the formula bar shows nothing when a user clicks that cell. The result still appears in the cell itself — they can read the number — but the underlying =VLOOKUP(...) or =A2*B1 is invisible.
This matters for two reasons. Reason one: your formula contains proprietary logic. Maybe it's a pricing model your company doesn't want competitors reverse-engineering. Maybe it's a lookup against a private rate table. Hidden gives you a paper-thin layer of obscurity. (It's not real encryption — anyone with the file can unprotect the sheet if they're determined, and the formula returns. Treat Hidden as a deterrent, not a vault.)
Reason two: clean user interface. Spreadsheets shared with non-technical users look more polished when the formula bar doesn't spew complex syntax every time the user clicks a result cell. Hidden makes the spreadsheet feel like an application, not a calculator.
To unhide later: Review > Unprotect Sheet (with the password if you set one). Click into the formula cell. Format Cells > Protection > uncheck Hidden. Done. The formula reappears in the bar the next time you protect-unprotect the sheet.
Note that Hidden only suppresses the formula text in the formula bar. The result still shows in the cell, and a determined user can copy the cell value, paste it as text into another cell, or use Show Formulas (Ctrl+`) — though Show Formulas itself is also blocked by Hidden when protection is active. The protection isn't perfect. It's a privacy gesture, not a security control. If the data is truly sensitive, the whole workbook needs encryption with File > Info > Protect Workbook > Encrypt with Password — that's a different feature with real cryptography behind it.
Three patterns catch people more often than any others when locking formulas in Excel. Knowing them up front saves the rebuild.
Trap 1: forgetting that copying preserves dollar signs. You write =$B$1*A2 in column C and drag it down. The B1 lock works exactly as intended. But then you also copy that formula to another sheet — and the $B$1 still points at this sheet's B1, which might be empty on the destination. Absolute references lock the cell address, not the sheet. To pin a reference across sheets, you need =Sheet1!$B$1 with the sheet name explicit. Drag or copy that, and it stays on Sheet1 no matter where you paste.
Trap 2: protecting before unlocking. Already covered, but worth repeating because it's the single most common help-desk question about sheet protection. Order matters: unlock inputs first, then Protect Sheet. If you've already protected and forgotten the inputs, Unprotect Sheet, fix the Locked checkboxes, then Protect Sheet again.
Trap 3: mixing up freeze panes with freeze formula. A user searches "how to freeze a formula in Excel" and ends up at View > Freeze Panes, which pins rows or columns on screen but does nothing to lock formula references. Different feature, different menu, different purpose. If you want absolute references, the answer is dollar signs and the F4 key — never the View tab.
Two minor ones worth knowing. First, conditional formatting formulas need mixed references almost always. If you write a conditional format with the formula =$A2="Done", the $A locks the column so the format reads from column A on each row, but the relative 2 walks row-by-row across the selection.
Get the dollar placement wrong here and your conditional formatting either lights up every row or no rows. Second, arrays and spill formulas in modern Excel (the dynamic array stuff with =SORT, =FILTER, =UNIQUE) have their own rules — the spill range is treated as one block, and locking parts of it gets weird. Test before you ship if you're working with spills.
Picture a sales commission tracker. Column A has rep names. Column B has monthly sales figures (the user types these in). Column C calculates commission as =B2*$E$1, where E1 holds the commission rate (8%). Column D shows the running total. You want reps to enter their sales but not touch the commission formulas or the rate. Here's the build.
First, the absolute reference. Click C2 and type =B2*$E$1. The $E$1 is the lock. Drag C2 down through C100. Every row's formula stays =B(thisrow)*$E$1 — the rate never drifts, but the sales reference walks down with you. Test by changing E1 from 0.08 to 0.10 and watch every commission number update. That's absolute references doing their job.
Next, the input unlock. Select B2:B100 (the sales column where reps type). Format Cells > Protection > uncheck Locked. Click OK. Sales cells are now marked unlocked, ready for protection.
Now decide about Hidden. Maybe you don't want reps to see the rate or the multiplication logic. Select C2:C100 and E1. Format Cells > Protection > tick Hidden > OK. The formulas will become invisible in the formula bar once protection's active.
Finally, protect the sheet. Review > Protect Sheet. Enter a password (write it down — you'll need it if you want to add a new rep next month). Leave the default allowed actions (Select locked cells, Select unlocked cells). Click OK.
The sheet is now locked down. Reps can type into B2:B100. They can't edit the formulas in C2:C100. They can't even see what's in the formulas — they just see the calculated number. If they try to click into a commission cell and type, Excel beeps and shows a message. They can read the result, copy the value, sort the data, but they can't break the math.
When commission time rolls around next month, you Unprotect Sheet (with the password), add new rows or change the rate, re-protect the sheet, and you're done. The same recipe scales to invoicing systems, budget templates, scorecards, anywhere users need a managed window of editability. Build once, protect, and let the spreadsheet do the rest.