Protect worksheet is a feature in excel when we do not want any other user to make changes in our worksheet, it is available in the review tab of excel, it has various features where we can allow users to perform some tasks but not make changes such as they can select cells to use auto filter but cannot make any changes to the structure, also it is recommended to protect a worksheet with a password.
Excel Protect Sheet (Table of Contents)
- What is a Protect Sheet?
- How to Create a Protect Sheet in Excel?
- How to Protect Cells in an Excel Worksheet?
- How to Hide the Formula Associated with a Cell?
- How to Lock a Cell in Excel Worksheet?
What is a Protect Sheet in Excel?
An excel worksheet that is protected using a password and/or has the cells in the worksheet locked for preventing any changes in the worksheet, known as a Protect Sheet in Excel.
The Purpose of a Protecting excel sheet with a password
To prevent the unknown users from accidentally or purposely changing, editing, moving, or deleting data in a worksheet, you can lock the cells in the Excel worksheet and then protect excel sheet with a password.
#1 – How to Create a Protect Sheet in Excel?
- Open the worksheet you wish to protect then, Right-click on the worksheet or Go to Review -> Protect Sheet. The option lies in the ‘Changes’ group then, click on ‘Protect Sheet’ from the list of options displayed.
- It will prompt you to enter a password
- Enter the password of your choice.
- The section beneath displays a list of options that you can allow the users of the worksheet to perform. Every action has a checkbox. Check those actions you wish to allow the users of the worksheet to perform.
- By default, if no action is checked, the users will only be able to VIEW the file and not perform any updates. Click on OK.
- Re-enter the password as prompted in the second screen then, Click on OK.
NOTE: The password of the excel sheet is case-sensitive and non-recoverable meaning if forgotten, it cannot be reset. So, it is advisable to have the password set to the text that you can easily remember or keep it safe.
- Once protected, a worksheet can be unprotected by selecting ‘Unprotect Sheet’ option from the Format menu in Excel.
- Once clicked on ‘Unprotect Sheet’, a new window, as shown appears prompting for the password. The same password used while protecting the sheet should be used to unprotect the sheet. Clicking on OK would unprotect the sheet in Excel.
#2 – How to Protect Cells in an Excel Worksheet?
To protect cells in excel, follow the steps given below:
- Right, click on the excel cell you wish to protect then, Select ‘Format Cells’ from the menu displayed.
- Go to the tab named ‘Protection’.
- Check ‘Locked’ if you wish to lock the cell in excel. This will prevent the cell from any editing and the content can only be viewed. Check ‘Hidden’ if you wish to hide the cell. This will hide the cell and so the content.
#3 – How to Hide the Formula Associated with a Cell?
- As shown below, cell F2 has a formula associated with it. D2+E2 = F2.
- Below shows that the excel cell is protected as Locked and Hidden as both the options are checked.
- As a result, the formula is hidden / not visible in the formula bar as shown below.
- Upon unprotecting the sheet, the formula also starts appearing in the formula bar, as shown below.
#4 – How to Lock a Cell in Excel Worksheet?
- As shown below, cell F3 is Locked using the Protection preventing any changes and making it view-only.
- If one tries to edit the cell, an alert is thrown as shown. To make the cell editable, the sheet needs to be unprotected using the password used while protecting the sheet.
Pros of Protecting Excel Sheet with A Password
- Protected excel sheet with a password is used to keep the sensitive information secured from unwanted changes done by unauthorized entities.
- Excel Worksheet Cell Actions are access controlled. Meaning, they can be configured so for to be available for some users and not to the others.
Cons of Protecting Excel Sheet with A Password
- If you protect an excel sheet with a password and if it is forgotten, it is non-recoverable. Meaning, there is no automated or manual way of resetting or recovering the old password. This can cause data loss.
Things to Remember About a Protect Sheet in Excel
- The password of the Protect Sheet is case-sensitive.
- The password of the Protect Sheet in Excel is non-recoverable.
- If no actions are checked in the Protect Sheet dialog window, the default accessibility is View. It means the others will only be able to view the protected worksheet and will not be able to add new data or make any changes to it the cells in the worksheet.
- Protecting the sheet is mandatory if one wished to protect the cells as Locked or Hidden.
- If the sheet is unprotected in Excel, all the formatting/locking associated with the cells would be overridden/gone.
- Locking a cell in excel prevents it from making any changes.
- Hiding a cell, hides the formula associated with it, making it invisible in the formula bar.
This has been a guide to Protect Sheet in Excel. Here we discuss how to Protect Sheet in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –