How to Protect Worksheets in Excel

The best use of the Excel protection system is to prevent a data entry person from accidentally damaging formulas or formatting in your spreadsheet file. Passwords aren't needed for this purpose.

Worksheet protection

There are two different menu locations that together control worksheet protection:

* Format, Cells, Protection, with a check box labeled Locked, to choose which cells the user will be able to modify when the sheet is protected
* Tools, Protection, Protect Sheet to toggle sheet protection on and off In a new blank worksheet all cells are formatted Locked, but worksheet protection is off (Tools menu) so the locked cell formats have no effect. This is the part that confuses some people; it seems to work backwards to expectations, but the way it works actually makes it less work to use, once you get used to it.

Here's what you do: select all the cells you want the user to be able to edit, either one range at a time or all together using the Control key, and go to Format Cells Protection and uncheck Locked. Then you can go to Tools Protection and turn on sheet protection. The user will only be able to type in the cells you formatted unlocked; if they try to change a locked cell, deliberately or by accident, they'll get an error dialog.


Workbook protection

To prevent the user from deleting or inserting worksheet tabs, go to Tools, Protection, Protect Workbook. In both this dialog and the Protect Sheet dialog, most of the time you won't need to change any check box options.

When either sheet or workbook protection is on, the submenu item at Tools Protection (pictured above) will say "Unprotect Sheet" or "Unprotect Workbook" instead of "Protect."