Locked cells in Excel are a quiet kind of frustration. You open a spreadsheet, click into a cell, start typing, and nothing happens. Excel pops up a message about a protected sheet, or the cell simply refuses to accept your input. Sometimes you set the protection yourself months ago and forgot. Sometimes a colleague did. Sometimes the file came from a vendor, a template, or an old finance system, and nobody alive remembers the password. The frustration is real, but the fix is almost always straightforward once you understand how Excel protection actually works.

This guide walks you through every legitimate method for unprotecting cells and sheets in Excel: the standard Review tab approach, password-protected sheets, partial unprotection for specific ranges, workbook-level protection, VBA techniques, and the common errors people hit along the way. We will cover what to do when you know the password, what your options are when you do not, and how to set up protection cleanly so future-you is not stuck again.

A quick note on scope. Everything here assumes you own the file or have explicit authority to modify it. Unprotecting spreadsheets you have no right to edit crosses ethical and legal lines and is not what this article is about. With that out of the way, let us look at how Excel cell protection actually works under the hood, because once you understand the mechanism, the unprotect steps make instant sense.

How Excel cell protection actually works

People assume that locking a cell is one action. It is actually two, and missing that distinction is the single biggest reason users get confused. Every cell in every Excel sheet has a Locked attribute switched on by default. You can see it for any cell by right-clicking, choosing Format Cells, and looking at the Protection tab. The Locked checkbox sits right there.

By default, every cell in a fresh workbook is locked. That sounds dramatic until you realise nothing happens because of it. The Locked attribute only takes effect when the entire sheet is protected. Until you click Review and then Protect Sheet, the Locked flag does nothing at all. Both layers have to be active for a cell to actually resist editing.

This explains the most common surprise: you turn off Locked on a cell, you press Enter, and the cell is still locked. The reason is that the sheet is still protected and the change to the Locked attribute does not take effect for cells already inside a protected sheet. To toggle Locked on or off in a meaningful way, you usually unprotect the sheet first, change the attribute, and then re-protect the sheet.

Knowing this two-layer model turns every unprotect problem into a clear sequence. If a cell will not accept input, ask yourself two questions: is the cell's Locked attribute on, and is the sheet currently protected? If the answer to both is yes, you need to lift one or both of those conditions, depending on what you are trying to achieve.

Step one: unprotect a sheet the standard way

This is the method you will use ninety percent of the time. Open the workbook, click the tab of the sheet you want to unprotect, and head to the Review tab on the ribbon. Look for the Unprotect Sheet button. If the sheet is currently protected, the button label reads Unprotect Sheet. If it is not protected, the same button reads Protect Sheet — Excel toggles the label based on current state, which doubles as a quick way to check whether a sheet is locked without opening any dialogs.

Click Unprotect Sheet. If the original person who protected the sheet did not set a password, the sheet unprotects instantly with no prompt. Cells now accept edits, formatting can be changed, rows and columns can be inserted or deleted — full editing rights are restored.

If a password was set during the original Protect Sheet step, Excel pops up a small dialog asking you to type it. Enter the password, click OK, and the sheet unprotects. Note that the password field is masked, so you cannot see what you are typing. Excel is also case-sensitive on passwords, so check Caps Lock before you fire off three wrong guesses and start wondering whether the password has changed.

That is the entire workflow for the most common scenario. One click, type a password if needed, and the sheet is open. Now any cell on that sheet can be edited regardless of its Locked attribute, because the sheet-level protection layer has been removed.

Step two: unprotect specific cells while keeping the rest of the sheet locked

Often you do not want a free-for-all. You want to leave most of the sheet locked but free up a handful of input cells where users can type their answers, data, or selections. This is exactly what the Locked attribute was designed for, and it is the cleanest way to set up forms, templates, and shared worksheets.

The trick is the order of operations. Start with the sheet unprotected. Select the cells you want users to be able to edit — drag, or hold Ctrl and click multiple ranges. Right-click anywhere in your selection and choose Format Cells. Switch to the Protection tab. Uncheck the Locked checkbox. Click OK. Now those specific cells are flagged as unlocked.

Next, head to the Review tab and click Protect Sheet. Set a password if you want one, leave the default checkbox options that allow users to select unlocked cells, and click OK. The sheet is now protected, but the cells you flagged as unlocked accept edits while everything else refuses. This is the standard pattern for any Excel form or template that has to be filled in by someone other than the author.

For maintenance, reverse the steps. Unprotect the sheet through Review, toggle Locked on or off for the cells you want to change, then re-apply Protect Sheet. Save the file. Users will not see any difference except that their editable region has changed.

Step three: dealing with passwords you do not have

This is where the conversation gets careful. If you do not own the file and cannot reach the original author, you are in territory where you should stop and confirm you have the right to modify it. Asking the file owner, your IT department, or whoever sent the file is almost always faster than any workaround. Many organisations keep a documented password vault for exactly this situation.

If you do own the file — a personal workbook from years ago, a template you built yourself, a file inherited inside a business unit where authority is clear — there are legitimate routes. Microsoft offers no official password recovery for sheet protection because the passwords are hashed, not stored. The hash is one-way: Excel can check whether the password you type matches what it stored, but cannot reverse the stored hash back into the original characters.

For modern Excel files (.xlsx format, which is essentially a zip of XML files), some users edit the underlying XML to remove the sheet protection element entirely. This is a technical procedure and beyond the scope of a general guide, but it exists and is legitimate when used on files you own. For older .xls files, third-party password recovery tools exist but vary widely in legitimacy and effectiveness.

The pragmatic approach for almost everyone: keep good notes of passwords you set, store them in a password manager, and document protection schemes in a hidden sheet inside the workbook itself so the future maintainer is not stuck. Prevention is far easier than recovery.

Step four: unprotect a workbook versus a sheet

People mix these up constantly. Workbook protection and sheet protection are completely separate features that solve different problems, and an unprotect action on one does nothing to the other. Knowing which type you are dealing with saves real time.

Sheet protection, which we have covered above, locks the cells, formatting, and structure within a single worksheet. It is per-tab. You protect Sheet1 and Sheet2 stays editable unless you protect that one too. Workbook protection sits one level up: it controls whether users can add new sheets, delete existing sheets, rename tabs, hide tabs, move them around, or unhide hidden sheets. It does nothing to the cells inside any sheet.

To unprotect a workbook, click Review, then Protect Workbook. If the workbook is currently protected, the button is highlighted and clicking it removes the protection (after a password prompt if one was set). If it is not currently protected, clicking the button starts a new Protect Workbook dialog. Same toggle pattern as the sheet button.

Common scenario: you can edit cells but cannot rename a tab or insert a new sheet. The workbook is protected even though the sheets are not. Lift workbook protection through the same Review menu and tab management is restored. The opposite scenario — you can rename tabs but cannot edit cells — means one or more sheets are protected while the workbook itself is not. Unprotect the specific sheet, not the workbook.

Step five: using Allow Edit Ranges for fine-grained control

Allow Edit Ranges is one of the least known but most powerful protection tools in Excel. It lets you carve out specific cell ranges that stay editable, optionally with their own passwords or assigned to specific Windows users, while the rest of the sheet stays locked under the standard sheet protection. This is how professional templates and shared workbooks let multiple teams contribute to the same sheet without stepping on each other.

To set it up, start with the sheet unprotected. Go to Review and click Allow Edit Ranges (sometimes labelled Allow Users to Edit Ranges in older versions). Click New, give the range a descriptive name like SalesInput or BudgetForecast, type or select the cell range, and optionally set a password for that specific range. You can also click Permissions to pick which Windows users have edit rights on that range without needing a password at all.

Repeat for each range you want carved out. Click Protect Sheet from inside the same dialog to apply standard sheet protection on top. The result: users can click into the named ranges, type a password (or just type freely if they are an authorised Windows user), and edit only that range. Everywhere else on the sheet stays locked exactly as the sheet protection dictates.

This is overkill for personal workbooks but pays off in shared business templates. To unprotect a range later, open the same Allow Edit Ranges dialog, select the range, and click Delete. Re-apply or release the sheet protection as needed. The settings persist with the file, so once configured they survive saves, sends, and reopens.

Step six: VBA approach for power users

If you work in Excel daily and have several files to unprotect, doing it through the ribbon gets tedious. The Worksheet.Protect and Worksheet.Unprotect VBA methods let you script the whole thing. Press Alt+F11 to open the VBA editor, insert a module, and a single line like ActiveSheet.Unprotect Password:="yourpassword" unprotects the active sheet. Drop the Password argument entirely if no password was set.

To unprotect every sheet in the active workbook at once, loop through the Worksheets collection. A short routine reads: For Each ws In ActiveWorkbook.Worksheets : ws.Unprotect Password:="yourpassword" : Next ws. Run it once and every sheet in the file opens up. Reverse the verb to ws.Protect and the same loop re-applies protection. Power users build a one-click button on their personal macro workbook so they can lock or unlock entire files instantly during template maintenance.

VBA also exposes the Locked property of every cell or range. Range("B2:D10").Locked = False toggles the attribute on a range without going through the Format Cells dialog. Combined with the Protect and Unprotect methods, this lets you build maintenance macros that reshape the editable area of a sheet on demand — handy for templates that change layout between quarters or seasons.

One caution: VBA macros that change protection state should be saved in a personal macro workbook (Personal.xlsb), not embedded in the data file. That way the macro travels with you, not with the file, and you do not accidentally ship a workbook with a maintenance macro a recipient could run. Keep the data file clean and let the macros live in your personal workspace.

Common errors and how to fix them

A handful of error messages show up repeatedly when people try to unprotect cells. The first is The cell or chart you are trying to change is on a protected sheet. To make changes, click Unprotect Sheet (Review tab, Changes group). This is the message you see when you click into a locked cell on a protected sheet and try to type. The fix is exactly what the message says: head to Review and click Unprotect Sheet. If you are not allowed to unprotect the sheet, you do not have the password and need to ask whoever does.

The second is The password you supplied is not correct. Verify that the Caps Lock key is off. Excel is case-sensitive and ignores trailing spaces but counts leading spaces. Check Caps Lock first, then try the password again with no surrounding whitespace. If you copied the password from elsewhere, watch for invisible characters that copy-paste sometimes introduces — type it manually if in doubt.

The third is Cannot change part of a merged cell. This happens when a locked cell is merged with an unlocked one. Protection sees the whole merged block as locked. Unprotect the sheet, unmerge the cells, set the Locked attributes individually, then re-merge if needed and re-protect. Avoid mixing locked and unlocked states inside merged ranges if you can.

The fourth common scenario: an action is greyed out on a protected sheet — inserting rows, sorting, applying filters, or formatting cells. The Protect Sheet dialog has a checkbox list of allowed actions. By default many are disabled. Unprotect the sheet, re-apply Protect Sheet, and tick the checkboxes for any actions you want users to keep available — sort, autofilter, insert rows, format cells. This is the cleanest way to allow users to interact with the sheet without giving them full edit rights.