I often work in Microsoft Excel and it includes many different functions and usability features that makes the things more easy and increase productivity. Yesterday, I discovered that Excel lets you lock individual cells without locking the complete worksheet. Once you lock/protect individual cells in Excel, they can no longer be modified, but the remaining lines and columns of the worksheet can be. Before finding this feature of Excel, I used to protect the whole worksheet even when there is need to lock only few columns or cells. You also have the option to assign a password to suspend the protection of the cells.
Protect individual cells in Excel without protecting the sheet
The protection of individual cells is somewhat more complex than locking of the entire worksheet. To protect a worksheet, you can select Protect Sheet option by right clicking on the sheet. Alternatively, you can also find the Protect sheet option under the Review tab. Follow the below guide to know how you can lock certain cells without locking the entire worksheet. Currently, I am using the Microsoft Excel 2010 but this guide is also applicable to Microsoft Excel 2007 and Excel 2013.
- First select the entire working area. For this, right click on the small grey square on the top left between the column name and line numbering. The entire working area then turns blueish. Choose the Format cells option from there.
- Navigate to the Protection tab. The Locked option is checked by default. Deactivate it by unchecking it and click OK.
- Now select the cells that you wish to lock. If these cells are not next to each other, click on these cells individually by keeping the Ctrl key pressed.
- After this, open the context menu again with a right click, select Format cells and activate the Locked option in the Protection tab.
- Now activate the usual cell protection option and it will be applicable only to the cells that you have selected above. For this, under the Review tab, select the Protect Sheet option. You can also open this by right clicking on the worksheet and selecting the Protect Sheet.
- You have the option to assign a password to increase the security. If you set a password, the protected cells can only be unlocked using the password. However, without password, anybody can alter cells by removing the protection.
If you have any query related to above tutorial, let us know in the comment section.
How to protect more other cells of a same excel sheet when there are already some protected cells
Selecting all the cells (cells that are already protected and new cells that you want to protect) at a time and performing protection task should do the job.
[…] In this post, We’ll show you how to set these prompts in your Excel sheet. […]
i have been trying for days to do this, have tried 13 other pages
this worked first try
I cannot get this to work. I have created a form and only want some lines to be locked with formulas. Have followed step by step, but then when you use protect it doesn’t allow for some columns that need to be used are unusable. Not sure what I am missing.
Doesn’t work