Have you ever wished you could prevent changes to all or part of a spreadsheet? Whether the change you want to prevent are your own or others’, accidental or intententional, Excel’s worksheet protection features have you covered.

If you want to protect your whole worksheet, it is very simple. Just click on the Review tab in the ribbon, then click Protect Sheet. If you are turning on protection for security reasons, make sure you set a password.  This will stop others from turning off protection and making whatever changes they want. If you are only using protection to prevent accidental changes, skip the password.

Protection automatically prevents users from changing or deleting cell contents, but the Protect Sheet window that pops up lets you control other things to like inserting lines or changing formatting.  You can browse through the list of options and set them according to your needs.

When you have them set the way you want, click OK and your worksheet is protected.

What if your worksheet is a form or template and you want to protect some cells, but leave others open for entering data?  In this case, simply right click on the cell (or highlighted group of cells) that you want to allow people to edit and choose Format Cells.  Go to the Protection tab and uncheck the box by Locked.  Now when you turn on protection you will still have full access to the unlocked cells.

One final note, this feature is worksheet protection, not workbook protection.  If there are multiple worksheets/tabs in your workbook, and you want all of them protected, you will need to repeat these steps on each tab. There is a Protect Workbook button but, despite what it sounds like, it doesn’t prevent changes to cells on every worksheet. Instead Protect Workbook locks the workbook structure, preventing users from adding, moving, deleting, hiding, and renaming the worksheets (tabs) in this workbook.



This is an update of a tip originally posted Oct 27, 2010.