Home	Excel	Access
| Feedback | Links | Spreadsheet Testing |

Protecting Important Data

You may choose to protect the contents of a cell in order to prevent accidental deletion or to stop someone else from wrecking the integrity of your calculations. (see spreadsheet testing). If you have a complex formula which has been properly tested it may be a good idea to block further changes by someone who does not understand its purpose. Alternatively, if you have a data input area, you may wish to surround it with protected cells in order to stop people from inputting too many values.

There are two ways of protecting cells, both of which will prevent anyone from changing a formula or value.

Would you like to learn more?

Excel 2003 Formulas by John Walkenbach (with CD)

Method One - Worksheet Protection

Excel has a protection tool for this purpose. All cells by default are locked (to prevent amendment) but this security feature has no effect until it is switched on for each worksheet. By selecting the menu options: Tools | Protection | Protect sheet you can activate it. A password is only necessary if you really don't trust your colleagues!

It is possible that you only want to protect a limited range of important cells on a sheet and the remainder can be freely edited. Before activating the protection, highlight the ranges to be left unprotected, then choose: Format | Cells | Protection and remove the tick from the Locked Cells option.

Method Two - Cell Validation

There is a simpler method for adding protection to individual cells (or ranges). Select a cell and then choose: Data | Validation | Settings. Change the 'Allow:' drop down box to 'Custom' and then type ="" in the formula box. If you want to customise the warning dialog which will appear each time anyone attempts to modify the cell, use the Error Alert tab. Be warned however that although this method stops someone from entering a value in a cell, it does not prevent them from deleting a cell's content.

Protecting a whole spreadsheet

A whole workbook can be password protected with two different options.

1. It requires a password to open it and be able to view the file contents.
2. The workbook can be opened by anyone but cannot be modified and saved unless you enter a password. The user is  able to modify the workbook and Save As a different filename.

The method of setting workbook passwords has changed slightly in different versions of Excel. In old versions of Excel, protection was accessed by selecting 'Save Options' within the SaveAs dialog box. In Excel 2000 the SaveAs dialog box has its own 'Tools' button that enables you to set the security using 'General Options ...'

Passwords are case sensitive and you have got a big problem if you forget your secret word - your work may be irretrievable. These passwords do not offer a high level of security and there are websites offering solutions to crack the passwords and open protected workbooks.

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file