Protecting Excel Worksheets (from yourself and others)

If you’ve ever worked for hours on a workbook one day, then destroyed it with a few poorly planned changes the next, you’ll appreciate these tips on how to protect your work with cell locking, worksheet and workbook protection.

Cell Locking with Worksheet Protection – If I told you that ever cell in a worksheet was already locked, you’d probably question my grasp on reality. But, indeed, they are. Click the cornerstone of the worksheet to select all cells. On the Home tab, in the Cells group, click the Format drop-down button. You’ll notice that there is an orange box around the Lock Cell selection. What this means is that the locks on the selected cells are ready to be activated. On that same drop-down menu, if you choose Protect Sheet and click OK, the locks are now active and you will not be able to type in any locked cell. Now that you know how it works, click Unprotect Sheet. It’s on the same menu. Turn off the lock attribute on any cells you want to be able to type in, then select Protect Sheet. Now, you won’t be able to delete those technically elegant, 9 ½ foot IF statements!

Workbook Protection – If you’ve hidden worksheets or your formulas depend upon them being in a particular order, you can also turn on Workbook protection. On the Review tab, in the Changes group, click the Protect Workbook button. You’ll notice the Structure checkbox is already checked. Leave this default and click OK. Now any hidden worksheets will remain hidden, and the structure and order of the worksheets cannot be altered.