After working on a spreadsheet or workbook application, you probably want to ensure that your work stays intact. You know, however, that a user can easily render all your work useless by removing a single formula or changing the value of a constant. You can train users, but sometimes that isn’t enough. If you need a real guarantee, enable sheet protection. Then, your work is safe — maybe a bit too safe. Once you protect a sheet, users can’t do anything but look at it. These five tips will take you beyond simply enabling protection for a sheet.

Note: To enable protection in Excel 2003, choose Protection from the Tools menu, select Protect Sheet, and then enter a password and confirm it. In Excel 2007 and 2010, click the Review tab and click Protect Sheet in the Changes group.

1: Unlock for data entry guidance

Use Excel’s protection feature to guide users through the cells they should modify. Before protecting a sheet, uncheck the locked format that works with protection. Specifically, right-click input cells, choose Format or Format Cells (depending on the version), and then click the Protection tab. Uncheck the Locked option. Once you protect the sheet, Excel automatically selects the first unlocked cell. As users press [Enter] and [Tab], Excel cycles through the unlocked cells.

This behavior is a great way to guide users through the cells they need to update. They can still scroll throughout the sheet. They can also select locked cells. They just can’t change the data in locked cells. Remember that locking and unlocking cells has no effect unless you protect the sheet.

2: Allow special access

A protected sheet is seriously limited. Users can’t update it or format it — they can’t even sort the data. You can unlock cells so users can update and/or enter new data (#1), but users will often need to do more than that. For instance, sorting and filtering data is a common task.

When enabling protection, Excel offers a number of special access options. Figure A shows several of them. (By default, users can select all cells, even locked cells.) Checking an option allows users to perform that particular function even with protection enabled.

Figure A

Allow access to specific tasks when protecting a sheet.

3: Hide formulas

When you hide a cell and then protect the sheet, the hidden cell is still visible, but nothing shows in the Formula bar when you select that cell. Nor can you copy the formula to another cell. You can see the result of the formula or the literal value, but you can’t view it in the Formula bar. This is a good way to hide intellectual and proprietary property in custom workbooks. (Excel doesn’t hide cells by default.)

4: Allow programmatic updates and tasks

If you’re familiar with VBA, you can stretch protection a bit. Simply add the following statement to the workbook’s Open event:

sheetname.Protect UserInterfaceOnly:=True

where sheetname identifies the sheet you want to protect. This particular statement will enable protection for sheetname when you open the workbook but allow changes to locked cells made via VBA procedures. You must execute this statement when opening the workbook.

5: Don’t mistake protection for security

Protecting a sheet isn’t a security feature. Don’t use protection to prevent users from accessing confidential or sensitive data — it simply doesn’t provide that level of security. Protection is a means of protecting the integrity of your design and data. This feature prevents accidents, not willful abuse. For better or worse, protection is easy to crack and will work only with those users content to apply it.