How to protect and un-protect the worksheet in Excel?

There are times when we want to protect Excel sheet while sending the information to other department or within the department, as we don’t want anybody to disturb (change, edit, delete) the information present in the sheet. 

​Excel provides the easy solution to lock the entire sheet and preventing the accidental damage or the tempering of the information present in the cells.
​

Protecting the worksheet

There are 2 ways to protect the sheet in Excel 2016, 2013 and 2010, perform the following steps:

First way:
​

• Open the spreadsheet you want to protect > click on the Review tab at the top > Select the Protect Sheet.

The process is shown in the following spreadsheet:

Protecting the worksheet using "Review tab"

Second way:
​

•  Right click on the worksheet tab (Sheet 1) > Protect Sheet

Protecting the sheet with right click on the tab

​When you click the Protect sheet option, a dialog window will open asking for the password to protect the sheet as follows:

Enter password to protect the sheet

• In order to protect the sheet, type the password with alphanumeric characters to make it strong and press OK. The confirm dialog box will pop up prompting you to re-type the password avoiding any misprint. Re-type the password and press OK. Be sure to remember the password and it is better to write it in the notebook as you will be needing it to unprotect the worksheet for editing.
• Sometimes, it is uneasy for the team to remember the password every time and unprotect the sheet for editing when forwarding the sheet information within the department.
  
• If you just want to prevent the accidental modification of the information or the formula and at the same time do not want to memorize and type the password every time to unprotect the sheet then just leave the password blank and Press OK.
  
• If somebody in your department wants to unprotect the sheet, just click on the Unprotect Sheet button on the ribbon or in the Sheet tab’s right click (dialog box will open) and click the Unprotect Sheet from its menu. The procedure is shown below:

Ways of unprotecting the sheet

(*When you create the password for the sheet the Protect sheet button automatically changes to Unprotect sheet button)

Selecting the actions, you want the users to perform in your sheet, in the protect sheet dialog box:

• By default, the two options are already selected in Excel like select locked cells and select unlocked cells
• If you want the users to perform other actions like insert column and rows, delete column and rows, sorting etc then you can select these fields and press OK
• If you don’t check any of the options or the fields, the user will only able to see the sheet
• Click the OK button

Selecting the actions for the users to perform in the sheet

• The confirm dialog box will pop up prompting you to re-type the password so as to avoid any accidental misprint. Retype the password and press OK

When someone tries to modify the sheet, the following message will appear:
​
“The cell or chart you’re trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password.

Password protected sheet

Unprotecting the worksheet in Excel

As you have seen above that it is quite easy and Excel offers two ways to protect the work sheet. It is even easier to unlock as Excel provides multiple ways to do it.

Remember, you will always need a password to Unprotect the sheet.

Way 1:

• Review > Unprotect Sheet

First method of unprotecting the sheet

Way 2:

• Worksheet tab at the bottom (sheet 1) > Unprotect Sheet

Second method of unprotecting the sheet

Way 3:

• Home >  Format > Unprotect Sheet

Third method of unprotecting the sheet

Upon pressing the Unprotect Sheet button using any of the above ways, the new dialog box is opened and you are prompted to enter the password to unprotect the sheet.

​On entering the password, press OK and the sheet is unprotected and you can edit it.

Enter the password to protect the sheet

I have used the way 1 to unprotect the sheet shown in the above screenshot which is as follows:

• Review > Unprotect sheet > Password > OK​

Conclusion

The worksheet protection in Excel is not actually a security feature as it uses a very simple algorithm and anyone having the basic knowledge of VBA can break it easily.

​It is a feature which is used to avoid any accidental loss of information by you or your team members.