How to Lock Cells in Excel

Protecting worksheets is an important element of using them with confidence as it prevents a formula from being erased or overtyped accidentally. Where a worksheet is distributed for general use it is also a way of controlling where data can be entered and edited. Locking specific cells can prevent unauthorised alterations of important values and formulas. 

As all the cells on a worksheet are locked by default, before you protect a worksheet, you must unlock cells and ranges where you want users to be able to enter and change data. The cells you leave locked become protected only after you protect the worksheet. 

Unlock Cells for All Users

1. Select locked cells on the worksheet to be unlocked. 
2. Within the Cells group on the Home tab, click the Format icon. 

3. Click Lock Cell -> The selected cells on the worksheet will now be unlocked. 

Alternatively, you can select cells and unlock/lock them as follows: 

1. Select the cell(s) on the worksheet to be unlocked. 
2. Right-click and choose Format Cells to display the Format Cells dialogue box. In the Format Cells dialogue box, go to the Protection tab and select or unselect the Locked check box to control whether specific cells should be locked or unlocked. 

3. Click OK. 

Note: This has no effect on protection until you go on to apply Worksheet Protection, described in the following section. This allows you to specify which specific cells should remain unlocked when the worksheet is protected. It is important to select only the cells that you want to unlock. 

Apply Worksheet Protection

1. Within the Cells group on the Home tab, click the Format icon. 
2. Click Protect Sheet to start the process of locking cells: 

The Protect Sheet dialogue box will be displayed, allowing you to specify which actions users can perform on the protected sheet. 

3. Tick the features that you want to remain available to users when the worksheet is protected. 
4. Enter a password that must be entered to unprotect the sheet. 
5. Click OK. 

Warning: A password may be entered to prevent unauthorised users from switching the worksheet protection off, but if you forget the password you will not be able to unprotect the sheet. 

Note: If you wish to have certain cells that are unprotected, so that when the worksheet is protected, these cells may still be typed in, you have to unlock those cells first, before protecting the worksheet. This ensures that you can protect cells containing sensitive data while allowing users to edit other parts of the worksheet. You can selectively lock or unlock certain cells while leaving other cells editable, especially those containing data or formulas. 

Other worksheet features and elements for which you can restrict access for all users include hyperlinks, sorting, AutoFiltering, PivotTable reports, graphic objects, and scenarios. These protections apply to all users and the entire worksheet, not to individual users or data ranges. 

You can protect the contents of a chart sheet from changes, and you can protect any graphic objects on the sheet from being changed or deleted. A protected chart sheet continues to be updated whenever the source data for the chart changes. 

Unprotect Your Worksheet

You will need to unprotect your worksheet from time to time to make changes. 

1. Within the Cells group on the Home tab, click the Format icon and Unprotect Sheet:

If prompted, type in the password. 

You can now change any part of the worksheet. 

Interested in mastering more Excel skills? Enquire about our comprehensive Microsoft Excel training course today and become proficient in effectively managing and protecting your data.