MEND MY LADDER
  • Home
  • Blog
  • About
  • Privacy
  • Disclosure

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

21/3/2019

1 Comment

 
Lock and unlock 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:
Protect sheet through review tab
Protecting the worksheet using "Review tab"
Second way:
​
  •  Right click on the worksheet tab (Sheet 1) > Protect Sheet
Protecting sheet with right click
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
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
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
Allowed actions in Excel
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
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
Unprotecting the sheet
First method of unprotecting the sheet
Way 2:
  • Worksheet tab at the bottom (sheet 1) > Unprotect Sheet
Unprotecting the sheet way 2
Second method of unprotecting the sheet
Way 3:
  • Home >  Format > Unprotect Sheet
Unprotecting the sheet way 3
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.
Protect the sheet
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.

1 Comment
Things to do link
13/9/2022 05:24:40 pm

This helps me a lot! Thank you so much!

Reply



Leave a Reply.


    Author

    Hi! I write posts on growing your business


    Categories

    All
    Grow Your Business


    Archives

    August 2021
    July 2021
    April 2021
    March 2021
    February 2021
    May 2019
    April 2019
    March 2019
    February 2019


    Categories

    All
    Grow Your Business


    RSS Feed

Copyright Mend My Ladder @ 2021
Proudly Powered by Weebly
  • Home
  • Blog
  • About
  • Privacy
  • Disclosure