Excel Tutorials: How to Lock Cells and Protect Worksheet Data in Excel 2007

Protecting data from change in Excel is a two step process.

1. Step one involves locking/unlocking specific cells in your spreadsheet.


2. Step two involves applying the Protect Sheet option. Until step 2 is completed, all data is vulnerable to change.

This article cover step one: How to Lock/Unlock specific cells.

For more Excel tips and tricks go to my static page:  Excel Tutorials and Tricks Series.

Lock/Unlock Cells in Excel

By default, all cells in an Excel spreadsheet are locked. This makes it very easy to protect all data in a single worksheet or in a workbook simply by applying the Protect Sheet or Protect Workbook option.

Unlocking specific cells permits changes to be made to these cells after the protect sheet/workbook option has been applied.

Often certain areas of a worksheet are unlocked so that new data can be added. Cells containing formulas, functions, or other important data are kept locked so that once the protect sheet/workbook option has been applied, these cells cannot be changed.

Example: Unlock Cells in Excel 2007

1. Enter the following data into cells A1 to D1: 123, 135, 147, 130.
   
   
2. Drag select cells A1 and B1 to highlight them.
   
   
3. Click on the Home tab.
   
   
4. Choose the Format option on the ribbon to open the drop down list.
   
   
5. Click on Lock Cell option at the bottom of the list.
   
   
6. The Lock Cell option works like an ON/OFF button. Since all cells are initially locked in the worksheet, clicking on the option has the affect of Unlocking the highlighted cells A1 and B1. 

Protecting data from change in Excel is a two step process.

1. Step one involves locking/unlocking specific cells in your spreadsheet.


2. Step two involves applying the Protect Sheet option. Until step 2 is completed, all data is vulnerable to change.

This article cover step two: Protecting the worksheet.

Protect the Worksheet

By default, all cells in an Excel spreadsheet are locked. This makes it very easy to protect all data in a single worksheet simply by applying the Protect Sheet option.

Unlocking specific cells permits changes to be made to these cells after the protect sheet option has been applied.

Often certain areas of a worksheet are unlocked so that new data can be added. Cells containing formulas, functions, or other important data are kept locked so that once the protect sheet option has been applied, these cells cannot be changed.

Example: Protect a Worksheet in Excel 2007

1. Open an Excel file to protect. If you open a new file all cells in the worksheet will be protected. If you wish, follow the example in step one to unlock certain cells in a worksheet.
   
   
2. Click on the Home tab.
   
   
3. Choose the Format option on the ribbon to open the drop down list.
   
   
4. Click on Protect Sheet option at the bottom of the list to open the Protect Sheet dialog box.
   
   
5. This dialog box contains a number of options when protecting the worksheet.
   
   
6. The first option is to add a password to prevent worksheet protection from being turned off. This password does not stop users from opening the worksheet and viewing the contents.
   
   
7. Next there a number of options that can be turned on or off with check boxes. The first two allow a user to drag select locked and unlocked cells. If these two are turned off, users will not be able to make any changes to a worksheet - even if it contains unlocked cells.
   
   
8. The remaining options allow users to carry out specific tasks on a protected worksheet, such as formatting cells and sorting data.
   
   
9. These options, however, do not all work the same. For instance, if the format cells option is checked off when a sheet is protected, all cells can be formatted. The sort option, on the other hand, works only on those cells that have been unlocked before the sheet was protected.
   
   
10. When you have selected the appropriate options, click OK.
    
    

Turning Off Worksheet Protection

1. Click on the Home tab.
   
   
2. Choose the Format option on the ribbon to open the drop down list.
   
   
3. Click on Unprotect Sheet option at the bottom of the list.