Worksheet protection in excel allows us to lock any sheet in a workbook so that the cells cannot be edited. However, you can allow a few cells to be edited and modified. Worksheet level protection is specially useful in the cases where you want the users to change the values of only few cells.
In this article, we will discuss how to lock and unlock the excel sheet and how to allow few cells to be modified in a locked sheet.
Now, before we start, I would like to remind you about the fact that excel offers the encrypted password security only at workbook level (Securing the excel file with a password to open). All other protection and security is just to avoid other users change the data and structure accidentally.
Protecting the Sheet
When you want to allow the users to view the contents of a excel sheet but restrict them to do operations like formatting, inserting, deleting or making changes; you should go for Excel worksheet protection. If you are using Excel 2007 or above; go to the review tab -> changes group and click on Protect Sheet. In excel 2003 or earlier, go to Tools -> Protection and select Protect Sheet. The ‘Protect Sheet’ dialog box appears where you have the option to select what changes you want to allow and prevent. If you select the checkbox, user is allowed to make that change.
Typically, you will like to allow selection of cells, sorting the data and use of auto-filter. Here you have other options available like inserting rows and columns, formatting cells, deleting cells, inserting hyperlinks, etc.
After you have done with the options, type the password which will be used to unprotect the sheet. Of course, you will be asked to repeat the password for confirmation.
TIP – If you wish, you can protect the sheet without a password also. Just leave the password field blank while protecting the worksheet.
If your excel sheet is protected, you can unprotect it if you know the password. The process is similar to protecting the excel sheet. In Excel 2007 or above; go to Review tab -> Changes group and click on Unprotect Sheet. For excel 2003 or earlier; go to Tools -> Protection and select Unprotect Sheet.
Locking selected Cells in an Excel Sheet
Imagine a scenario where you want to collect the information from a group of people. Here, you don’t want users to change the entire sheet but just a few cells. In that case, before protecting the sheet you may like to unlock the desired cells first.
Let’s understand it with an example. Suppose you have created a small application to calculate the total marks and percentage of marks obtained like this.
Now, you don’t want the user to change any cells except B2 to B5 which will be used for data input. Here is how to do this:
- Make sure your excel sheet is not protected. If it is, unprotect the sheet as per the method described above.
- Select the cells you want to allow changes. In our case it is B2 to B5.
- Go to Home tab -> Cells group -> Format -> Protection and clicked on locked Cell to clear the cell locking. By default, when you protect the sheet, all cells are locked, so you need to clear the cell locking for these cells.
- Once you are done, protect the sheet. Just make sure you have allowed cells selection by checking ‘Select locked cells’ and ‘Select unlocked cells’ check boxes.
- You know that to protect and unprotect the sheet, you can also go to Home tab -> Cells group ->Format and click on Protect Sheet.
- You may rather like to do the reverse. That is you want to restrict only the few cells and allow users to make changes to others. In that case, you should first unlock the entire sheet by selecting all cells (Shortcut – Ctrl + A, or clicking on select all button). Now lock only those cells which you want to restrict users from making changes and then protect the worksheet.
Hiding the Formula in a Cell
Sometimes, you may not want the formula used in any cell to be visible. This can simply be done similar to locking the cell.
- Select the cells with the formula which you don’t want to make visible.
- Go to Home tab -> Cells group ->Format and click on Format Cells.
- In protection tab, select the hidden checkbox and click OK.
- Now protect the worksheet. Please note that you can hide the formula only in a protected worksheet.
Microsoft Excel gives you the complete flexibility on how you want to protect your sheet. Only thing that you should keep in mind is that the worksheet protection with a password is not encrypted. It is only meant to prevent users from making unintentional changes. So, it can be unprotected with a little effort (I don’t want to discuss here the methods, but you can search and find out yourself). If your data is sensitive, rather use a strong password for file opening (as discussed in the previous article).