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.

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 ribbon options: [Home] Cells | Format | Format Cells | 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: [Home] Cells | Format | 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] Data tools | Data Validation | Data Validation|. Change the 'Allow:' drop down box to 'Custom' and then type ="" in the formula box or 'Text Length' = 0. 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 2010 the password can be set using [File] Info | Protect Workbook |.

Passwords are case sensitive and you have got a big problem if you forget your secret word - your work may be irretrievable. The default settings for passwords in Excel 2003 do not offer a high level of security but it is possible to select optional settings and change the encryption key strength. Excel 2010 has a better default level of (128-bit) encryption but this is still only effective when you use a mix of upper and lowercase characters and numbers. Changing the encryption type requires users to edit the registry settings.

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file. It has been rewritten for Excel 2010.