Protect Worksheet. Hide Formulas . Lock Cells. Allow Editing of Specific Excel Cell Ranges.
Protecting formulas worksheets in Excel is sometimes needed to protect your formulas, allow editing of specific cells only and hide the formulas in a worksheet so that a user doesn’t change them unknowingly. This helps the user in avoiding errors and since the formulas are supposed to work on the entered data in a specific way, the task of data entry becomes even easier. Of course, you may wish to hide the formulas because you don’t wish people to copy your hard work. The training video below describes in detail how to hide the formulas, allow editing of specific cells in the worksheet and providing protection to other areas of the worksheet where the user cannot enter data. In case the user tries to enter data in the protected areas, a message is displayed indicating that the worksheet is protected and you need to unprotect the sheet before you can enter data in that area. For that the user must have a password!
Implementing the protection of formulas and worksheets:
- We want the user only to be able to edit data in the columns A and B
- Select the cells C2:C6 under the header perks
- Click on Format in the menu bar
- Select cells…
- A new window called Format Cells opens up
- With the Protection tab selected click on the ‘Hidden’ option
- Click OK
- Next we click on Tools in the menu bar, move to protection in the drop-down menu and then select Allow Users to Edit Ranges…
- A new window called Allow Users to Edit Ranges pops up
- We click on the New… button on the top right
- A new window called New Range opens up •We click on the ‘collapse’ button next to the text below ‘Refers to cells:’ and then select cells A2:B6 in our worksheet and finally expand the ‘New Range’ window and our range A2:B6 is automatically entered. These cells can be edited by users without a password
- Next we click on the Permissions… button
- In the new window called ‘Permissions for Range1’ we click on the Add… button
- In the window ‘Select Users or Groups’ that opens we click inside the text box below ‘Enter the object names to select’ and we enter ‘Users’.
- Click on OK
- In the Permissions for Range1 window we can now view that Users has been entered under ‘Group or user names:’ and under ‘Permissions for Users’ the item ‘Allow’ next to ‘Edit range without a password’ has been checked
- We click on ‘Apply’ and then OK
- We are brought to a window ‘Modify range’
- We click OK
- We come back to window Allow Users to Edit Ranges
- We’ll click on Protect Sheet..
- In the Protect Sheet window we’ll enter our password under ‘Password to unprotect sheet:’. The checkbox next to ‘Protect worksheet and contents of locked cells’ is checked
- Click on OK
- In the ‘Confirm Password window that pops up we’ll re-enter our password under ‘Reenter password to proceed.’
- Click on OK
- If you click on cell C2 you’ll notice that the formula is hidden
- You can’t edit the cells in column C
- If you try to do so you get a warning message: The cell or chart you are trying to change is protected and therefore read only. To modify a protected cell or chart, first remove protection ………. for a password.
- In column D also you can’t edit any data
- Cells in column B are editable You have now effectively allowed editing of cells A2:B6, hidden the formulas in columns C and D and protected the worksheet with a password.
You can close this worksheet and reopen it. Now you cannot edit data even in column E. To do so you need a password to unprotect the worksheet. Now when you click on the Tools tab in the menu bar, move to Protection and click on Unprotect sheet,,. then in the ‘Unprotect Sheet’ window you’ll need the password. Only if you have the password you can make changes in cells that are locked.