A locked Excel sheet can stop you cold — you click a cell, try to type, and get a message saying the sheet is protected. Whether someone locked it before sending it to you, a colleague set a password and left the company, or you locked your own sheet months ago and forgot the password, the situation is frustrating. The good news is that Excel gives you several ways to remove protection, and the right approach depends on whether you have the password.
Sheet protection in Excel is a feature designed to prevent accidental edits, not to provide serious security. When someone protects a sheet with a password, they're creating a lightweight barrier — not encryption. This distinction matters because it affects how you think about getting back in. If you know the password, unlocking takes seconds. If you don't, the path is a bit more involved but still entirely possible in most cases.
It's worth distinguishing between two types of protection you might encounter. Sheet protection locks the cells on a specific worksheet — clicking protected cells shows the lock message. Workbook protection locks the workbook structure — you can't add, rename, move, or delete sheets. Both are set under the Review tab, and both can be removed if you have the password. This guide focuses primarily on sheet-level protection, which is far more common, but covers workbook protection too.
Understanding what protection actually restricts helps you decide how urgent the unlock is. A protected sheet can still be read — you can see all the data, scroll, sort, and even copy values out. What you can't do is edit cell contents, change formulas, insert rows, or modify any locked cell. If you only need to view the data, you may not need to unlock at all. But if you need to edit, add formulas, or restructure the sheet, you'll need to remove the protection first.
Before attempting any of the methods below, note that unlocking a sheet also removes any input restrictions tied to that sheet. If you re-protect later, you can configure fresh restrictions that better suit your current needs — including which specific actions to permit even in protected mode.
One common setup you'll encounter: sheets protected specifically to allow drop-down list input while locking everything else. In these cases, certain cells are unlocked (the data entry fields with drop-downs), and the rest of the sheet is locked. If you want to learn that pattern from the design side, understanding the excel drop down list system is a useful companion to what's covered here.
If you have the password, removing sheet protection is quick. Go to the Review tab in the ribbon and look for the Protect Sheet button — when a sheet is protected, this button changes to read Unprotect Sheet. Click it. A dialog appears asking for the password. Type it in and click OK. The protection is removed immediately, and all cells on the sheet become editable.
That's the entire process. No settings to configure, no confirmation dialog beyond the password prompt. Once you click OK, every cell on the sheet is fully editable unless individual cell-level locks were set up separately — we'll cover that scenario in Method 3.
If the sheet is protected but without a password (some people protect sheets without setting a password, just to prevent accidental edits), clicking Unprotect Sheet removes protection immediately with no prompt at all. This is surprisingly common in workbooks passed around internally — the protection is more of a warning than a lock.
You can also unprotect via the sheet tab. Right-click the sheet tab at the bottom of the screen and choose Unprotect Sheet from the context menu. Same dialog, same process. This is handy when you want to unprotect without leaving your current position on another sheet.
If you're working in Excel for Mac, the path is the same: Review tab → Unprotect Sheet. The dialog is identical. Excel Online also supports removing sheet protection if you have the password — click Review → Unprotect Sheet in the web ribbon.
If you protect a sheet and later realize you never set a password, removing protection is even simpler. Click Unprotect Sheet and Excel removes it instantly — no dialog appears at all. You can verify whether a sheet has a password set by simply clicking Unprotect Sheet and observing whether a prompt appears. No prompt means no password was used, which is common in workbooks locked only to prevent casual accidental changes.
After unlocking, you can do anything with the sheet: edit formulas, rearrange columns, add rows, paste data in, use vlookup excel formulas that write into previously locked cells, or restructure the layout entirely. All restrictions are lifted when protection is removed.
If you unprotect a sheet and then want to make it editable by default going forward, you don't need to do anything extra — once protection is removed, all cells are editable until you turn protection back on. The only exception is if individual cells have custom lock states set in Format Cells; those states persist but only matter when protection is active.
One thing to watch for: unprotecting the sheet does not remove formatting or cell-level lock settings. If you later re-protect the sheet, the same cells that were locked before will be locked again. The lock/unlock state of individual cells persists independently of whether sheet protection is currently active.
Locks cells on a specific worksheet. Users can still view data and navigate the sheet, but cannot edit locked cells, insert rows, or modify the structure. Set under Review → Protect Sheet.
Locks the workbook structure — prevents adding, renaming, moving, hiding, or deleting sheets. Does not lock cell contents. Set under Review → Protect Workbook.
Encrypts the entire file — requires a password just to open it. Provides real security, not just edit prevention. Set under File → Info → Protect Workbook → Encrypt with Password.
If you don't have the password, you'll need to work around the protection. There are two main approaches: using the XML method (works in all Excel versions) and using a VBA macro (works in older Excel versions). Both are legitimate approaches for recovering access to your own files — use them only on files you own or have authorization to edit.
The XML method works because Excel's .xlsx files are ZIP archives containing XML files. Sheet protection is stored in one of those XML files as a plain text hash value. You can open the archive, delete the protection hash, and reopen the file with no password required. Here's how:
First, make a backup copy of the file. Then rename the file extension from .xlsx to .zip — in Windows, you may need to enable showing file extensions in File Explorer first. Open the ZIP archive using Windows Explorer or a tool like 7-Zip. Navigate to the folder xl/worksheets/ inside the archive. You'll see files named sheet1.xml, sheet2.xml, and so on — these correspond to your worksheet tabs. Open the file for the protected sheet in a text editor like Notepad.
Search the XML content for the word sheetProtection. You'll find an XML tag that looks something like: <sheetProtection password="ABCD1234" sheet="1" .../>. Delete that entire tag from the start of <sheetProtection to the closing />. Save the XML file, update the ZIP archive, then rename the file extension back from .zip to .xlsx. Open the file in Excel — the sheet will no longer be protected.
The VBA method works differently. In older Excel versions (pre-2013), you could run a macro that brute-forces the hash. In Excel 2013 and later, the hashing algorithm changed and this approach became impractical. For modern Excel files, the XML method is more reliable.
If the thought of editing XML makes you uncomfortable, there are also online Excel unprotection tools — upload the file, they remove the protection, you download the result. Use these with caution for any file containing sensitive data. For internal business files, the XML method keeps your data offline.
Once you've removed protection, consider resetting the structure for cleaner future use. You might add proper how to add drop down list in excel controls to the data entry fields before re-protecting the sheet with a password you'll actually remember. A well-designed protected sheet has clear input areas and locked calculation areas — and the password is documented somewhere secure.
Sometimes you don't want to remove all protection — you want to unlock just certain cells so specific users can edit them while the rest of the sheet stays locked. This is the standard pattern for data entry forms: formulas are locked, headers are locked, only the input cells are editable.
To set this up, start by selecting all cells on the sheet. Press Ctrl + A to select everything. Then go to Home → Format → Format Cells (or press Ctrl + 1) and click the Protection tab. Uncheck the Locked box and click OK. This removes the locked state from every cell on the sheet.
Now select only the cells you want to lock — the formula cells, headers, and reference data that should be read-only. Press Ctrl + 1 again, go to the Protection tab, and check the Locked box. Click OK. These cells are now marked as locked; the others are not.
Finally, turn on sheet protection: Review → Protect Sheet → set a password if you want one → OK. Now only the cells you marked as Locked are protected. The input cells are editable even with protection active.
This selective approach is how most well-designed templates work. If you're building a form for your team, the cells where people type answers are unlocked — they can click and type freely. The surrounding structure is locked — clicking it triggers the protection message. Users don't need to know the password or worry about accidentally breaking formulas.
You can also restrict which actions are allowed even in unprotected areas. In the Protect Sheet dialog, a checklist lets you allow specific actions: selecting locked cells, selecting unlocked cells, formatting cells, inserting rows, sorting, using AutoFilter, and more. For example, you can allow sorting and filtering while still preventing formula edits — check the Sort and Use AutoFilter boxes in the protection dialog while leaving other options unchecked.
Another useful setting in the Protect Sheet dialog is allowing users to format cells while keeping content locked. Check the Format cells box in the protection options and users can change fonts, colors, and number formats — but cannot overwrite values or formulas. This is handy for colorful dashboards where team members want to highlight cells but you do not want them accidentally deleting key data.
For input fields, pair this with data validation to guide entry. An unlocked cell with a how to create drop down list in excel validation rule gives users both freedom to enter data and guardrails on what values are accepted. The combination of cell-level unlock plus drop-down validation is the gold standard for robust data entry forms in Excel.
You received a workbook from a colleague or predecessor and can't edit it because sheets are protected. First, check if there's a password documented anywhere — a README tab, a comment in a cell, or a file named passwords.txt in the same folder. Many internal workbooks have the password written somewhere obvious because the goal was to prevent accidental edits, not hide it from authorized users.
If there's no documented password, try common defaults: the company name, the person's name, the year, or simply 'password'. A surprising number of internal workbooks use trivial passwords. If none work, use the XML method described in Method 2. Make a backup first, then strip the sheetProtection tag from the XML. Once you have access, document the situation and consider resetting the sheet with a known password stored in a secure shared location.
A shared template should distinguish between areas users fill in and areas they should not touch. Before distributing a template, decide which cells should be editable: data entry fields, date fields, comment areas. Lock everything else — formulas, totals, headers, instructions. Use Format Cells → Protection to set locked/unlocked status, then enable sheet protection.
Store the protection password in a secure shared credential tool accessible to the team admins. If you use cell-based input controls like how to create a drop down list in excel validation rules on the input cells, make sure those cells are unlocked or users will not be able to interact with the drop-down. Test the protected template yourself before distributing — click through every field and verify that input areas work and formula areas reject edits.
The most common reason to lock a sheet is to protect formulas from accidental deletion. A SUMIF or nested IF formula that someone overwrites with a value — intentionally or not — can silently corrupt all downstream calculations. Locking the formula cells prevents this without restricting the data entry cells that feed those formulas.
To identify which cells contain formulas before locking, use Home → Find & Select → Formulas. Excel selects all formula cells at once. You can then directly apply Format Cells → Protection → Locked to just those cells. Re-protect the sheet and your formulas are safe. Users can still enter data in input cells, but any attempt to overwrite a formula cell triggers the protection message — a helpful reminder that they're touching a calculated field.
Unprotecting multiple sheets one at a time is slow when you have 10 or 20 tabs. A simple VBA macro can loop through all sheets and remove protection. Open the VBA editor (Alt + F11), insert a module (Insert → Module), and paste this code:
Sub UnprotectAll()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="yourpassword"
Next ws
End Sub
Replace yourpassword with the actual password and run the macro (press F5). Every sheet in the workbook is unprotected in seconds. If different sheets have different passwords, you'll need a more complex loop, but for workbooks where all sheets share one password, this approach handles the job instantly. Remember to delete the macro after use to avoid leaving the password visible in the VBA editor.
Workbook protection and sheet protection are separate controls, and you may encounter both at once. Workbook protection prevents structural changes — you can't insert, delete, rename, move, hide, or unhide sheets. Sheet protection prevents cell edits. A file can have both active simultaneously.
To remove workbook protection, go to Review → Protect Workbook. If the button is highlighted (pressed), protection is active. Click it to toggle it off, entering the password if one was set. The dialog looks identical to the sheet protection dialog but controls the workbook structure instead of individual sheets.
One sign that workbook protection is active: right-clicking a sheet tab shows a greyed-out menu. You can't rename, move, or insert sheets. If you need to restructure the workbook — add new tabs, reorganize existing ones — workbook protection must be removed first, separate from any sheet-level unlocking you've done.
When you're building a file for distribution, think about which type of protection each goal needs. Want to prevent formula edits? Use sheet protection with specific cells locked. Want to prevent people from adding their own tabs or rearranging the structure? Use workbook protection. Want to prevent the file from being opened without authorization? Use file encryption. These three layers serve different purposes and can be used independently or in combination.
If you share a workbook with multiple people, consider using Allow Edit Ranges instead of full sheet protection. This Excel feature lets you define specific ranges that different users can edit, optionally with their own passwords, while the rest of the sheet stays locked. It gives finer control than a single on/off toggle and is especially useful when different team members own different sections of the same sheet.
A common mistake is protecting a workbook to prevent sheet restructuring when the real goal was preventing formula edits. If you only want to stop people from changing formulas, sheet protection is the correct tool. Workbook protection does not restrict cell editing at all — it only controls the structure. Applying the wrong layer of protection leads to frustrated users who can still break formulas but cannot add a tab when they legitimately need one.
After unlocking and restructuring as needed, consider adding drop-down validation to any input fields that feed into calculations. An how to create a drop down list in excel guide covers setting these up efficiently, and combining them with selective cell locking creates a form that's both easy to fill out and hard to break accidentally.