Lock Columns in Excel — Complete Guide (2026)

Lock Columns in Excel — Complete Guide (2026)

"Lock columns in Excel" means two completely different things. Most people mix them up, and the wrong fix wastes ten minutes. The first meaning is visual — you want column A or columns A through C to stay on screen while you scroll right. That's the freeze panes feature. The second meaning is structural — you want a column to be uneditable. Maybe it has formulas, maybe it has IDs you shouldn't touch. That's cell protection plus sheet protection, and it usually involves a password. Practice with the free Excel practice test on PracticeTestGeeks (500+ questions with answer explanations).

Here's the quick test. If you're scrolling and labels disappear off the left edge, you need freeze panes. If a coworker keeps overwriting your formulas, you need protection. They work independently. You can freeze a column without locking it, and you can lock a column without freezing it. Most polished spreadsheets use both — frozen headers up top, locked formula columns down below, an input area everyone can type into in the middle.

This guide walks both routes end to end. You'll see exactly where the menus live in Excel 365, Excel 2019, Excel 2016, the web version, and Mac. You'll learn how to lock only certain cells while leaving the rest editable — which sounds backwards but is how Excel was built. You'll also see how to how to unprotect an excel sheet if you inherit a workbook with someone else's password, and what to do when shared workbooks block protection commands.

One quick warning. Excel's protection isn't a security feature. It's a UX feature. The password stops accidental edits. It does not encrypt the file. If you need actual security — like financial records or PII — that's a separate setting (File > Info > Protect Workbook > Encrypt with Password). We'll cover both, but don't confuse them.

One more framing piece before the steps. Locking in Excel is layered. Three layers, actually. There's the view layer (freeze panes — what stays on screen). There's the cell layer (the Locked checkbox on Format Cells — what gets blocked when protection is on). And there's the sheet layer (Protect Sheet — the actual switch that activates blocking). Almost every "my lock didn't work" support ticket comes down to a missing layer.

Set the cell property, forget the sheet switch — nothing happens. Flip the sheet switch on a sheet where every cell is still marked Locked by default — now everything is read-only, including the cells you wanted editable.

The mental model that works: think of the Locked property as a sign on each cell that says "if protection turns on, freeze me." Protection itself is the master power switch. Until that switch flips, the signs do nothing. Once it flips, every sign activates. That's why the standard workflow is always: clear every sign first, place signs only where you want them, then flip the switch.

Ready? Pick the path that matches your problem.

If you want the full picture, the lock columns in excel guide walks through it step by step.

If you want more targeted practice, the excel exam practice page covers the same material with additional questions and explanations.

If you want more targeted practice, the xcel co-authoring in real time page covers the same material with additional questions and explanations.

Microsoft Excel Exam at a Glance

Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.

Free Excel Study Tools

Interactive Excel resources on PracticeTestGeeks, free to start:

• Free Excel Questions and Answers — 16 questions with instant scoring and answer explanations
• Excel flashcards — review key terms and concepts card by card
• Excel study guide — topic-by-topic overview of what the exam covers
• Excel cheat sheet — one-page printable summary for last-minute review

Method 1: Freeze Columns Visually (View > Freeze Panes)

This is the easier route. Freeze the column you want to stay visible, then scroll right as much as you need. Excel keeps the frozen column glued to the left side of the screen. No password. No setup. About four clicks total.

Freeze just the first column. Open your workbook. Click the how to freeze panes in excel button on the View tab — it's near the middle of the ribbon. From the dropdown, pick Freeze First Column. Done. Column A now stays in place when you scroll right. You'll see a thin gray vertical line marking the frozen boundary. To remove it, go back to the same menu and choose Unfreeze Panes.

Freeze multiple columns. Excel doesn't have a "freeze first three columns" button. Instead, you pick the column after the ones you want frozen, then choose Freeze Panes. So to freeze A, B, and C, click on column D's header letter first, then View > Freeze Panes > Freeze Panes. Excel freezes everything to the left of your selection.

The same logic works for rows — see how to freeze a row in excel if you want headers and a side column locked at once. The mental shortcut: click the cell that should be the first scrollable cell, then pick Freeze Panes. Everything above and to the left of that anchor cell freezes.

Freeze rows and columns at the same time. Click the cell that sits one row below and one column to the right of where you want the freeze to start. So to freeze row 1 plus column A, click cell B2. Then View > Freeze Panes > Freeze Panes. Now your header row and ID column both stay visible. This is the setup most polished dashboards use — date column anchored on the left, header row pinned up top, the middle of the screen free to scroll in any direction.

That's it. No password, no menu diving. Freeze panes is purely cosmetic — anyone can still edit any cell. If you also need to stop edits, that's the next method.

Pros and Cons at a Glance

Locking Only Specific Cells Inside a Column

What if you want column B mostly editable, except for cells B5, B12, and B30 — which hold formulas you don't want anyone to break? Same flow, different selection. Start with the all-unlock step. Press Ctrl+A, Ctrl+1, Protection tab, uncheck Locked, OK. Now hold Ctrl and click each cell you want frozen: B5, B12, B30. With them selected, Ctrl+1 again, this time check Locked, OK. Then Review > Protect Sheet to activate.

Three cells locked, everything else editable. You can mix and match across the whole worksheet — lock A1:A10 and C5:C20 and the entire E column, all in one protect operation. Just keep selecting and checking Locked before you turn on protection. The selection trick scales without limits. You can have 200 locked cells scattered across 10 columns, and Excel will treat them all identically.

One handy trick: use Find & Select > Go To Special > Formulas to select every cell containing a formula in one click. Then Ctrl+1, check Locked, protect the sheet. That locks every formula automatically, leaves every typed value editable. Most accountants set their workbooks up this way. The pattern also pairs well with excel formulas when you want to demo a sheet that calculates but doesn't let users break the math.

Want users to see the formula bar contents but not edit them? That's the default — locked cells display their formula in the bar but block typing. To hide the formula entirely so users only see the result, check the Hidden box on the Protection tab too. Once protection is on, clicking that cell shows nothing in the formula bar. Useful when you don't want collaborators reverse-engineering your business logic.

For data-entry forms — say, an invoice template — flip the pattern. Lock everything by default, then unlock only the input cells where users should type. Same Ctrl+1 dialog, opposite checkbox state on the cells you want editable. Now Tab moves the cursor between unlocked cells automatically once you click Protect Sheet with "Select unlocked cells" enabled but "Select locked cells" disabled. The user can't even click into the header or formula areas. That's the gold-standard form workflow.

One more selection trick worth knowing: named ranges. If you've named a range like "Inputs" or "Formulas" via Formulas > Define Name, you can type the name into the Name Box (top-left, next to the formula bar) and instantly select every cell in it. Then Ctrl+1 to lock or unlock as a group. This is how big workbooks stay manageable — you don't manually re-select dozens of cells every time you tweak protection.

Excel Version Differences — 365, 2019, 2016, Mac, Web

The good news: the core flow hasn't changed in fifteen years. The menus are in the same places. Format Cells > Protection has had the Locked checkbox since Excel 2003. Freeze Panes lives on View in every modern version. If you're on a corporate Excel 2016 install or the latest Microsoft 365 subscription, the steps in this guide work the same way.

Small differences worth knowing. Excel for Mac uses Cmd instead of Ctrl — so Cmd+1 to open Format Cells, Cmd+A to select all. The Protect Sheet button lives on the Review tab in both, but the dialog has slightly different default permissions. Mac Excel 2016 had bugs around password dialogs hanging; updating to a current version fixes it.

The web version (Excel Online / Excel for the Web) supports Freeze Panes but has limited Protect Sheet — you can apply protection in the desktop app and the web version will respect it, but creating new protection from the browser is restricted.

Mobile Excel (iOS and Android) lets you edit cells inside protected sheets if you have the password, but the Protect Sheet command itself is hidden in most builds. If you need to set up protection, do it on desktop first. Other column tools like how to move columns in excel and how to unhide columns in excel work the same across all versions, with the caveat that mobile versions sometimes nest the commands deeper in menus.

One real change worth noting: Excel 365's co-authoring (live multi-user editing) replaced the old Shared Workbook feature. Protection now plays better with co-authoring than it did pre-2018 — but it's still not perfect. Test with a second user before you trust it for anything that matters.

Common Errors and How to Fix Them

"The cell or chart you're trying to change is on a protected sheet." This is the message you want to see for cells you locked — and the message that's confusing when you didn't realize the sheet was protected. Go to Review > Unprotect Sheet. If you set the password, enter it. If you inherited the file, you may be stuck — Excel doesn't expose a forgot-password flow.

"Cannot enter formulas in a protected sheet." Same root cause — the sheet is protected and the cell you clicked is locked. Either unprotect, or click a different (unlocked) cell. The fix for inherited files is documented at how to unprotect an excel sheet.

Freeze Panes grayed out. Usually means you're in cell-edit mode (you double-clicked into a cell and never pressed Enter or Esc). Press Esc, then try again. Other causes: Page Layout view (switch to Normal view first), or the workbook is open as read-only.

Protection applied but column still editable. The Locked checkbox is set per-cell, not per-column. If you clicked the column letter but Excel only selected the visible cells (filtered list, hidden rows), unhide everything first, then re-apply. Also confirm you actually clicked Review > Protect Sheet — applying Locked without activating protection is the most common mistake.

"You can't paste here." Paste into a protected sheet fails the same way typing does, but the error wording is different. Excel blocks paste targeting any locked cell, even if your clipboard came from inside the same workbook. Unprotect, paste, re-protect. Same workflow for fill-handle drags that land in locked territory.

Freeze Panes works but disappears on save. Older .xls format sometimes lost freeze settings during round-trips through Google Sheets or LibreOffice. Save as .xlsx natively and the issue goes away. Worth checking if you inherited a legacy file from a different platform.

"This cell is protected and therefore read-only." Same family of errors. The wording varies by Excel version but the fix is identical — unprotect the sheet via Review > Unprotect Sheet. If you're inside a protected workbook structure (Review > Protect Workbook), that's a different lock layer — it blocks adding or renaming sheets, not editing individual cells. Both can be on at once, which means you might need to unprotect twice to get full edit access back.

Mac, Touch Bar, and Keyboard Shortcuts Worth Memorizing

On a Mac, every shortcut in this guide swaps Ctrl for Cmd. Cmd+1 opens Format Cells. Cmd+A selects all. The menu paths are identical — View > Freeze Panes, Review > Protect Sheet. If your MacBook has a Touch Bar, the Format Cells dialog shows up there too once you're inside it. Mac Excel also adds a small dot indicator next to protected sheets in the tab bar — a tiny lock icon you'll spot once you know to look for it.

Worth memorizing for Windows users: Alt+W+F+F toggles Freeze Panes (alt menu sequence). Ctrl+1 opens Format Cells. Alt+R+P+S walks Review > Protect Sheet. Once you've protected a few sheets the muscle memory takes over and the whole flow takes about eight seconds. Similar shortcuts also speed up related tasks — see how to freeze cells in excel for the row-and-column-together pattern.

If you build the same protection setup repeatedly — say, lock columns A through D on every monthly report — record a macro. Developer tab > Record Macro > do the steps once > Stop Recording. Assign it to a button. Next month, one click protects the sheet exactly the same way. The recorded VBA also gives you a starting point if you want to tweak the protection options programmatically — say, applying the same lock pattern to twenty sheets in one workbook with a small loop.

Another time-saver: Excel templates. Build your protected workbook once, save as a .xltx template file, then start every new month's report from it. Protection, freeze settings, named ranges, and unlocked input regions all come along for the ride. Way faster than redoing the same setup every period.

The deeper you get, the more it becomes a workflow, not a feature. Treat freeze panes as the visual layer and sheet protection as the integrity layer. Most polished workbooks use both, often with a clear input region that everyone can type in while the calculation and reference regions stay locked down. That's the pattern to copy. Once you've set up two or three workbooks this way, it stops feeling like extra work — it's just how a finished spreadsheet looks.

Sample Excel Practice Questions

Try these questions from our free Excel practice tests. The correct answer and an explanation follow each question.

1. How can the column width be adjusted to fit the contents?

   • A. Double-click the boundary to the right of the column heading.
   • B. Single-click the boundary to the left of the column heading.
   • C. Press Fn and double-click anywhere in the column.
   • D. Press ALT and single-click anywhere in the column.

Answer: A. Double-click the boundary to the right of the column heading.

Double-clicking the boundary on the right edge of a column heading triggers auto-fit, resizing the column to its widest content. The other actions—single-clicking the left boundary or using Fn/ALT key combinations—do not perform this auto-resize.

Answer: A. MAX

The `MAX` function in Excel is used to find the largest numerical value within a specified range of cells. You simply provide the cell range as an argument to the function, for example, `=MAX(A1:A10)`. Excel will then return the highest number found within that range.

Answer: B. Refresh data when opening the file

This setting on the Data tab of PivotTable Options refreshes the cache at file open.

Answer: A. Returns the absolute (positive) value of a number

ABS returns the absolute value of a number, removing any negative sign.

Take the full Excel practice test