The two protection modes in Excel and what they actually do
People mix these up constantly, and it costs them time. Protect Sheet locks the contents of one worksheet. Protect Workbook locks the structure of the entire workbook. That's the basic split, but the details matter more than you'd think. Protect Sheet is your go-to when you're handing a file to someone who needs to enter data but shouldn't touch formulas. You apply it through Review > Protect Sheet and pick exactly what users can still do. Let them select unlocked cells. Let them format cells. Don't let them insert columns. The dialog box lays this out as checkboxes, and Excel will honor every selection you make. Protect Workbook does something completely different. It lives under Review > Protect Workbook and locks the sheet structure. Users can't delete worksheets. They can't rename them. They can't move sheets around. The sheets themselves remain editable unless you also applied Protect Sheet. This is the setting you reach for when someone keeps accidentally deleting tabs or rearranging your layout.
I once spent forty-five minutes tracking down why a collaborator couldn't add a new worksheet. The error message was vague. "That action requires the workbook to be unprotected." Turns out the workbook structure was protected, which blocks adding or deleting sheets entirely. I just unchecked Protect Workbook, they added their sheet, and I re-applied it. That took three clicks total. The lesson: before someone complains they can't do something structural, check both protections first.
How Protect Sheet actually works under the hood
Every cell has an Locked property in its Format Cells dialog. By default, every single cell is marked as locked. That sounds counterintuitive at first. Locked means nothing until you turn on Protect Sheet. Once protection is active, Excel enforces that property strictly. Cells you marked as unlocked are the only ones users can edit. Everything else throws an error. Here's the workflow I actually use. First, I select all the cells people need to edit. I press Ctrl+1 to open Format Cells, go to the Protection tab, and uncheck Locked. Then I go to Review > Protect Sheet, set a password if needed, and leave the default checkboxes alone. Users can select and edit those unlocked cells. They can't touch the rest. Simple. There's a detail most guides skip. When you protect a sheet, the Allow Edit Ranges button becomes available. This lets you define specific ranges with their own passwords. I use this when one person should edit cells A1:D50 and another should edit F1:G100, and neither should touch the other's area. Each range gets its own password. This is powerful, but if you lose those individual passwords, you're stuck. The main Protect Sheet password won't unlock the ranges. There's no recovery path.
Get the Full Details
Another thing nobody mentions. Protect Sheet blocks some operations that feel like they should be independent. For example, filtering is allowed by default, but pivot table refreshes are not. If your sheet has a pivot connected to external data and you protect it, that pivot won't refresh unless you explicitly allow "Edit Objects" or "Edit Scenarios" in the protection dialog. I learned this the hard way when a weekly report stopped updating and I couldn't figure out why for twenty minutes.
How Protect Workbook works and what it doesn't touch
Protect Workbook only affects structure. Sheet contents remain fully editable. Users can type anywhere. They can change formulas. They can delete cell values. The only thing locked is whether sheets exist, their names, and their order. Add a worksheet? Blocked. Delete a worksheet? Blocked. Rename a worksheet? Blocked. Drag a tab to reorder? Blocked. Move or copy a sheet? Blocked. The protection dialog for workbook structure is even simpler than the sheet version. It asks for a password and that's it. There are no granular checkboxes. You either lock the structure or you don't. I wish there were an option to allow renaming while blocking deletion. There isn't. You get the binary choice. Here's a quirk that trips people up. If you have VBA macros in your workbook and you protect the workbook structure, those macros still run fine. Macros aren't affected by Protect Workbook. They are affected by Protect VBA Project, which is a separate setting under Tools > VBAProject Properties. Don't conflate the three. Protect Sheet, Protect Workbook, and Protect VBA Project are independent layers. Apply all three when you want maximum lockdown, or apply none when you want maximum flexibility.
Password scenarios and the reality of Excel protection
Excel passwords for sheet and workbook protection are weak. Not broken in the sense that they don't exist, but trivially reversible with free tools. The hash that protects a standard Excel sheet is a single round of MD4. Anyone with five minutes and a download can recover it. Don't use these passwords for anything sensitive. Use them as speed bumps, not walls. If you need real security, encrypt the entire workbook through File > Info > Encrypt with Password. That uses AES-128 or AES-256 depending on your Excel version. That's actual encryption. Protect Sheet and Protect Workbook are just permission flags. They don't encrypt data. They just tell Excel to block certain actions. The distinction matters when you're deciding how much trust to put in the system. There's also a scenario where protecting both sheet and workbook creates a frustrating loop. Say you protect the workbook structure so no one can delete sheets, then protect each individual sheet so no one can edit formulas. A user tries to insert a column inside a protected sheet and gets blocked. They try to delete the sheet instead and get blocked again. They're stuck. Neither action works. The workaround is to leave at least one escape hatch, usually allowing users to insert rows and columns on protected sheets by checking that box during the Protect Sheet dialog.
Edge case: protecting a sheet with data validation dropdowns
I ran into this recently with a budget tracker. The model had data validation lists in column C for expense categories. After protecting the sheet, users couldn't click the dropdown arrows anymore. The cells were technically unlocked, but the dropdown UI got suppressed by the protection. The fix was counter-intuitive. I had to select the validated cells, uncheck Locked in Format Cells, then go back into Protect Sheet and make sure "Edit Objects" was not interfering. Actually, the real solution was simpler: data validation dropdowns work on unlocked cells only if the sheet isn't protecting form controls. My sheet protection settings had "Edit Objects" unchecked, which somehow blocked the dropdown rendering. Checking that box fixed it immediately. This behavior varies between Excel 365 and Excel 2019, so test your version before rolling this out to a team. Protect Sheet applies per worksheet. Use it to guard formulas while allowing data entry. Granular control over what users can still do. Password is easy to lose or bypass. Blocks certain operations like pivot refreshes and dropdowns depending on your settings. Protect Workbook applies to the file level. Use it to prevent sheet deletion, renaming, and reordering. Binary on/off with no fine tuning. Doesn't touch cell contents at all. Doesn't affect VBA macros. Independent from Protect Sheet.
Apply both when you need it. Apply neither when collaboration requires flexibility. Apply one or the other based on what specific risk you're trying to mitigate. There's no universal best choice. It depends on who's using the file and what they're trying to do.
