How to Lock - Protect Cells in Excel

In Excel you may wish to lock cells so that your users can’t make changes to a specific worksheet. It is mostly done to protect the structure of the workbook or to prevent users from making critical changes to the formulas. In essence you are locking the ability for changes to be made. In this way you can protect the integrity of the workbook by locking Excel cells.

The following video on YouTube outlines the the Lock cells technique. The Excel file used is this video is below:

Protect Cells.xlsx

 
 

Lock All Excel Cells on a Worksheet

To lock all of the cells in Excel, so the cells are protected - on the Home menu choose File.

unlock1.PNG

The following menu will appear.

Unlock2.PNG

Choose Protect Current Sheet.

lock cells in Excel

The above Excel Protect Sheet dialog will pop up. At this point you can choose to put in a password. You can choose what a user is allowed to change from the tick boxes in the list. By default, the first two boxes are ticked (Select locked cells, Select unlocked cells), which allows a user to select locked and unlocked cells. You can choose other options in the list, format rows, allow insert columns etc.

Click OK when you are happy and the workbook will be locked to the changes you chose. If any change is attempted the following will pop up.

Lock Cells

This message is an indicator than the sheet is protected and can not be changed as you have locked all of the cells in the worksheet.

To take the password protection off - follow the same process, put it your password and the cells will be unlocked once more.

Unlock Certain Cells in Excel

To unlock certain cells in a spreadsheet to allow users data entry, it is a two step process. To lock the cells in Excel firstly choose the cells you don’t want locked. Let’s say I want the cells from C11:N15 left unlocked so users can edit them. The other cells on the worksheet we wish to protect. First highlight the cells you want user entry.

Protect or lock cells in Excel

In the above example we only want users to the cells in yellow - these cells are unlocked, everything else will be locked. Highlight C11 to N15.

Press Ctrl + 1 together (Ctrl 1)

Unlock all cells in Excel

Untick the Locked check box, which by default is ticked.

Now follow the steps above - File - Protect Workbook - Protect Current Sheet - choose your settings and click OK.

That is how to protect part of a sheet so users can enter data. The following file is the one used in the YouTube video.

Protect Cells.xlsx