Locking Cells in a Worksheet

Locking Cells in a Worksheet

In some situations, you might create a worksheet or worksheets and someone else will enter the data. In these situations, you might want to lock cells that contain formulas and functions so that the person doing the data entry does not accidentally overwrite or delete the worksheet formulas or functions. Locking cells in a worksheet is a two-step process. You must first select and lock the cells. Then, you must turn on protection on the entire worksheet for the "lock" to go into effect.

Follow these steps to lock cells on a worksheet:

1. Select the cells in the worksheet that you want to lock. These are typically the cells that contain formulas or functions.

2. Select Format and then Cells . The Format Cells dialog box appears. Click the Protection tab on the dialog box (see Figure 7.5).

   Figure 7.5. Cells can be locked using the Protection tab of the Format Cells dialog box.

   

3. Be sure the Locked check box is selected on the Protection tab. Then click OK .

4. Now you must protect the entire worksheet to have the lock feature protect the cells that you selected. Select the Tools menu, point at Protections , and then select Protect Sheet . The Protect Sheet dialog box appears (see Figure 7.6).

   Figure 7.6. The worksheet must be protected if you want to lock cells containing formulas or functions.

   

5. Enter a password if you want to require a password for "unprotecting" the worksheet. Then click OK .

The cells that you locked in steps 1, 2, and 3 will no longer accept data entry. Every time someone tries to enter data into one of those cells, Excel displays a message stating that data will not be accepted. The cells are now protected, and you can pass the workbook on to the person who handles the data entry.