Three Things People Mean When They Say Lock

Type "how to lock a row in excel" into any search bar and you will get three very different answers depending on who is writing the tutorial. That is because the word lock means three completely different things in spreadsheet land, and mixing them up is the single most common reason a perfectly good Excel workbook breaks at 4:55 pm on a Friday.

The first kind of lock is the dollar-sign lock inside a formula. You write =A1*$B$1 instead of =A1*B1, and now when you drag the formula down a column the reference to B1 stays put while A1 walks down with the row. This is called an absolute reference, and the dollar signs are the locks. Press F4 on a reference and Excel cycles through the four lock states for you. We will cover the full cycle in the next section.

The second kind of lock is the protection lock that stops someone from editing a cell. You go to Format Cells, flick on the Locked checkbox, then turn on Protect Sheet. Without that second step the first one does nothing, which surprises almost every beginner who tries it for the first time. It is a two-step dance and missing either step leaves the door wide open.

The third kind of lock is the freeze. View tab, Freeze Panes, pick Top Row or First Column. Now when you scroll, your headers stay glued to the top while the data slides underneath. Freezing does not protect anything. It is purely a viewing convenience for tables longer than your screen.

One word, three jobs. This guide walks through all three, in plain language with worked examples you can rebuild from scratch, and shows you when to reach for each one. By the end you will know which lock the person at the next desk actually means when they ask, and how to fix it in under a minute.

Lock 1: Absolute References With F4 and the Dollar Sign

This is the lock that shows up in every formula-heavy spreadsheet. You build a small calculation in row 2 that references a tax rate sitting in cell B1. You drag the formula down a hundred rows. Suddenly the references in row 30 point to B29 instead of B1, and the math goes haywire. The fix is to lock the reference to B1 so it does not drift.

Excel gives every reference four possible states. The default is fully relative, written plain like A1. Hit F4 once and the reference becomes fully absolute, both row and column nailed down, written $A$1. Hit F4 again and only the row is locked, written A$1. One more press locks only the column, written $A1. A fourth press cycles you back to plain A1.

The full F4 cycle on PC goes A1 → $A$1 → A$1 → $A1 → A1. Memorize that sequence and you will stop typing dollar signs by hand forever. To use it, click inside a formula bar, place the cursor on a reference, then tap F4. On a Mac the equivalent is Cmd+T in older versions and Fn+F4 in newer ones because the F-row often runs media controls by default.

A worked example pins it down. You have prices in column A starting at A2 and a single sales-tax rate sitting in cell B1. You want column C to show the price plus tax. Write =A2*(1+$B$1) in C2. Drag down. C3 becomes =A3*(1+$B$1), C4 becomes =A4*(1+$B$1). The reference to A walks because it is relative. The reference to B1 stays put because it is locked. The whole column calculates correctly using one rate.

Now flip the use case. You have a multiplication table where row 1 holds factor headers and column A holds factor rows. Inside the grid you want each cell to multiply its row label by its column label. The formula in B2 is =$A2*B$1. Lock the column on the left reference, lock the row on the top reference. Drag in every direction and the table fills out perfectly. Mixed references are a power move and worth practicing.

Where do absolute references show up in real production formulas? Inside VLOOKUP the table_array argument should almost always be absolute so dragging the formula down a column does not slide the lookup range off the bottom of your data. Same for INDEX/MATCH ranges, SUMIFS criteria ranges, and any place a constant table is being referenced.

Constants are the other big use. Tax rates, exchange rates, commission percentages, fiscal-year-end dates. Anything you stored in a single cell to make it editable later wants an absolute reference everywhere it is used. Editing one cell then propagates to every dependent formula automatically, which is the whole point of structured spreadsheets.

Lock 2: Stop People From Editing Cells

This is where most users get stuck. They right-click a cell, choose Format Cells, tick the Locked box, hit OK, and then try to edit the cell. It still works. Nothing has changed. They conclude that Excel is broken. Excel is not broken. The Locked checkbox is only half the story.

Every cell in a new worksheet is born with its Locked attribute already set to true. The catch is that the attribute only takes effect once you turn on worksheet protection. Until you hit Review > Protect Sheet, every cell is editable regardless of what the Locked checkbox says. The checkbox is a flag waiting for protection to be enabled before it does anything at all.

The two-step workflow for locking specific cells from editing goes like this. First, select all the cells you want to leave editable, right-click, choose Format Cells, go to the Protection tab, and uncheck Locked. Second, go to Review > Protect Sheet, optionally add a password, and click OK. From that point forward every cell with Locked still ticked refuses edits, while the cells you unticked accept input as normal.

It is counterintuitive because you start by unlocking the cells you want to leave editable rather than locking the cells you want to protect. Think of it the other way around. The default state of every cell is locked, and you are carving out the few editable cells before flipping the master protection switch. Read that sentence twice if it sounds weird, then try it on a junk sheet.

You can also do this in VBA when building macros or templates. The property you want is Range.Locked. Set it like this: Sheets("Input").Range("B2:B20").Locked = False to leave a range editable. Then call Sheets("Input").Protect Password:="abc" to switch protection on. To unlock again in code use Sheets("Input").Unprotect Password:="abc" before changing anything programmatically.

The Protect Sheet dialog has a long list of optional permissions that fine-tune what users can still do even on locked cells. Letting people select locked cells, format columns, insert rows, sort, or use AutoFilter are all options. Tick what fits your audience. For a typical input form template you uncheck everything except Select unlocked cells, which traps users on the data-entry fields and stops them from poking around the calculation engine in the background.

Lock 3: Freeze Panes So Headers Stay Visible

Freezing has nothing to do with security and everything to do with not losing your mind on long tables. When you scroll past row 25 in a table with column headers in row 1, the headers disappear and you forget which column is which. Freezing fixes that by pinning chosen rows or columns in place so they remain visible no matter how far you scroll.

There are three quick options on the View tab. Freeze Top Row pins row 1. Freeze First Column pins column A. Freeze Panes reads where your selected cell sits and pins everything above and to the left of it. To freeze the first three rows, click cell A4 then choose Freeze Panes. To freeze the first two columns, click C1 then Freeze Panes. The selection determines the split.

To freeze both two top rows and one left column at once, click cell B3 first, then choose Freeze Panes. Everything above row 3 and to the left of column B locks into place. This combo is essential for cross-tab reports where both column headers and row labels need to stay onscreen as you navigate a large grid of numbers.

To unfreeze, go back to View > Freeze Panes and the top option will now read Unfreeze Panes. Click it. The split lines disappear and the entire sheet scrolls normally again. There is no password required and unfreezing happens instantly with no risk to your data.

Freeze Panes is not the same as Split. Split divides the sheet into two or four independently scrolling regions, useful for comparing far-away rows at the same time. Freeze locks one region in place while the other scrolls. Most users mean Freeze when they say lock. Split is a more specialized tool that you will rarely need on day one.

If your freeze a row in excel attempt is not working, double-check two things. First, you may be in Page Layout view, which disables freezing. Switch to Normal view on the View tab. Second, the freeze applies to the active window only. If you opened the same workbook in a second window via View > New Window, the freeze setting lives per window, not per file. Set it again in each window if needed.

Locking Specific Cells While Leaving Others Editable

The most common real-world scenario is a calculation template where a few input cells should be open and the rest of the workbook should be untouchable. Maybe it is a loan calculator with three inputs and a wall of formulas. Maybe it is a quote builder with line-item quantities entering a totals block. Either way, the recipe is the same.

Start by selecting every cell that should remain editable. Hold Ctrl and click each one to multi-select. Right-click, choose Format Cells, go to the Protection tab, untick Locked, and click OK. You have not actually changed anything visible yet. The cells still look identical. Excel is just keeping track of which cells will be free once you turn protection on.

Now go to Review > Protect Sheet. Type a password if the workbook is sensitive. In the options list, leave Select unlocked cells ticked so users can click into the input fields, and untick Select locked cells if you also want to prevent the cursor from even landing on the protected formula cells. Click OK and confirm the password if asked.

The result is a clean input form. Tab moves from one input cell to the next, skipping the protected calculation cells entirely. Trying to edit a formula cell pops up a polite "the cell is protected" message. The math behind the scenes runs as soon as inputs change, and users have no way to accidentally overwrite a formula.

For more sophisticated needs, use Allow Edit Ranges on the Review tab. This lets you carve out named ranges where specific users (or anyone with a specific range password) can edit even after the sheet is protected. It is the equivalent of giving different users different keys to different rooms in the same locked house. Useful for shared templates in larger teams.

Allow Edit Ranges is set up before you call Protect Sheet. Define each range, name it, optionally give it a password or hook it to Active Directory groups, and only then enable sheet protection. Users without the right password get the standard "cell is protected" message. Users with the password edit freely within their range only.

Password Protect a Whole Workbook

The locks we have covered so far protect individual cells or sheets. To stop people from opening the file at all, you need workbook-level encryption. This is a separate setting and it is the heavy artillery. Forget this password and there is no built-in recovery. Microsoft has no master key. Set this only when you understand that risk.

The path is File > Info > Protect Workbook > Encrypt with Password. Type the password twice. Save the file. Anyone trying to open the workbook now gets a password prompt before they see a single cell. The contents of the file are encrypted on disk using AES-256, which means stealing the file is no good without the password.

Workbook-open passwords are different from sheet-protection passwords. The sheet password only stops cell edits once the file is already open. The workbook-open password stops the file from even loading. For sensitive financial models or HR data you usually want both: encrypt the file to control access, then protect the sheets inside to control behaviour for the users who do get in.

A third related setting is Mark as Final, which adds a yellow banner suggesting that the file is locked but does not actually block editing. It is a politeness banner more than a security feature. Use it only as a workflow signal, not as protection.

If a user has forgotten the workbook-open password and the file is critical, you are looking at third-party password-recovery tools that brute-force attempt millions of guesses per second. For modern AES-256 encrypted files those tools are slow and not guaranteed to succeed. For older xls files that used weaker encryption, recovery is usually quick. Either way, prevention by writing the password to a password manager is the only reliable answer.

To remove a workbook-open password later, open the file with the correct password, go back to File > Info > Protect Workbook > Encrypt with Password, and clear the password field. Save. The file is now encryption-free and opens for anyone with disk access. Reverse the protection-sheet password the same way from Review > Unprotect Sheet.

Troubleshooting When Locks Misbehave

Most lock problems boil down to one of five recurring patterns. Run through the list before you assume the workbook is corrupted.

One: protection is on but cells still accept edits. Check whether the cells were unlocked in Format Cells before Protect Sheet was called. A common mistake is unlocking everything by accident with a Ctrl+A then Format Cells flow. Re-tick Locked on the formula cells and try protection again.

Two: drag-down corrupts formulas. Symptom: a working formula in row 2 turns into nonsense by row 30. Cause: you forgot to lock the table-array or criteria-range references. Edit the original formula, tap F4 on the reference that should stay put until you see two dollar signs, then drag again.

Three: a #REF! error appears after you delete a row. This is not really a lock issue but it is often misdiagnosed as one. The formula was pointing at a cell that no longer exists. Restore the deleted row with Ctrl+Z if you can. Otherwise rewrite the formula with an absolute reference to a stable cell.

Four: Protect Sheet greyed out. The workbook itself is in shared mode, which disables most sheet-level protection options in older Excel versions. Turn off sharing under Review > Share Workbook, apply your protection, then re-enable sharing if needed. In recent versions of Microsoft 365 the shared-workbook feature has been replaced by co-authoring, which removes this conflict.

Five: freeze pane resets every time you reopen the file. The freeze setting is stored per saved workbook. If it keeps disappearing, you may be opening a template (.xltx) rather than the saved working copy. Save the file as a fresh .xlsx and the freeze will persist across sessions as expected.

Lock With VBA for Templates and Automation

When you build templates that other teams use, doing the lock setup by hand each time is tedious. Drop a short macro into the file and the workbook configures itself on open or save. The two key properties are Range.Locked for cell-by-cell control and Worksheet.Protect for activating the sheet-wide protection.

Here is a working example. Open the VBA editor with Alt+F11, double-click ThisWorkbook in the Project pane, and paste this snippet. It runs every time the workbook opens. It unlocks the input range, locks everything else, and turns sheet protection on with a known password.

Private Sub Workbook_Open(): With Sheets("Input"): .Unprotect Password:="abc": .Cells.Locked = True: .Range("B2:B20").Locked = False: .Protect Password:="abc", UserInterfaceOnly:=True: End With: End Sub. Replace the sheet name, range, and password with your own. The UserInterfaceOnly flag lets other macros still edit locked cells programmatically even though the user is blocked at the keyboard.

The other macro worth keeping in your toolbox is a quick unlock-everything routine for when you need to rework a template you protected six months ago and have forgotten the exact configuration. Sub UnlockAll(): For Each ws In ThisWorkbook.Worksheets: ws.Unprotect Password:="abc": ws.Cells.Locked = False: Next ws: End Sub. Save the file as a macro-enabled .xlsm.

For shared templates distributed across a wider audience, consider signing the macros with a code-signing certificate so corporate antivirus filters do not strip them out silently. Unsigned VBA is increasingly blocked by default in newer Office builds with no warning to the end user, which can leave you wondering why your perfect macro suddenly stopped working in production.

Macros also let you build conditional locking. For example, unlock a cell only when another cell has a certain value. Use a Worksheet_Change event handler to react to user input, flip the Locked attribute on the relevant cells, and re-call Protect with the same password. The user experiences it as a form that grows new editable areas as they fill in the earlier ones. Powerful for stepwise workflows.

Locking Inside Real Lookup Formulas

Locks earn their keep inside the formulas that move data around. Three patterns come up so often you should be able to type them in your sleep.

VLOOKUP with a locked table. Suppose you have a product price list in $F$2:$G$200 and you are looking up codes from column A in column B. The formula in B2 is =VLOOKUP(A2, $F$2:$G$200, 2, FALSE). The dollar signs around the table range mean you can drag B2 down a thousand rows and the lookup keeps pointing at the same price list. Without them, by row 100 your lookup is reading rows 100 to 298, which slides past the actual data.

SUMIFS with a locked criteria range. A monthly report sums sales by salesperson. Names live in $D$2:$D$5000, amounts in $E$2:$E$5000, and the salesperson you are summing for sits in cell A2 of the report. The formula is =SUMIFS($E$2:$E$5000, $D$2:$D$5000, A2). Drag down to compute each salesperson. The two big ranges stay locked, only the criterion cell A2 walks down one cell at a time.

INDEX/MATCH with mixed locks. A two-way cross-tab where row headers are dates and column headers are products. INDEX returns the value at the intersection. =INDEX($B$2:$M$200, MATCH($A2, $A$2:$A$200, 0), MATCH(B$1, $B$1:$M$1, 0)). Notice the mixed reference on $A2 and B$1. Column locked on row lookups, row locked on column lookups. The classic two-way lookup pattern.

Reading those formulas the first time looks intimidating. The trick is to ask, for each reference: do I want this part to walk when I drag, yes or no? Lock the parts that should stay put. Leave the parts that should walk plain. F4 makes flipping a reference between states a single keypress at a time, which is why typing absolute references by hand is something you should stop doing immediately.

Lookups are also the place where unlocked references silently produce wrong answers rather than visible errors. The formula does not throw #N/A; it just returns a number that is plausibly close to the right answer but actually points at the wrong row. Always sanity-check the first few results of a freshly dragged lookup. If the absolute references are wrong, the whole column is wrong, and you would never know without a manual spot check.