Last updated on May 17th, 2018
It is often necessary to protect either the sensitive information in one’s actual worksheet or the workbook structure, from being edited. Excel provides different options for protecting and securing one’s data and we are going to look at the two most commonly used ones, namely protecting one’s worksheet and protecting one’s workbook.
So let’s get started with a simple example to illustrate the difference between the worksheet level protection and workbook level protection.
Table of Contents
- Worksheet Level Protection in Excel
- Workbook Level Protection
- Download Area
- Read More…
- Review Section: Test your Understanding
A hypothetical company has decided to hire consultants, to test the skill levels and competencies of their employees, using assessments.
Read More: Workbook-level Events and Their Uses
Each employee is given seven assessments to complete in a set time, for each assessment and the score is recorded by the assessor on an Excel spreadsheet. There is a section for employee feedback on the spreadsheet and two other spreadsheets, in the same workbook, which allows for the assessor to give feedback, and the manager to make an input. The assessor inputs the score, then gives feedback, then sends the spreadsheet to the manager for input, the manager sends it back to the assessor, and then finally the spreadsheet is sent to the employee for feedback. So needless to say there are certain sections that the assessor and manager would not want the employee to edit.
The source data is shown below.
Worksheet Level Protection in Excel
1) So, on the sheet called EmployeeEvaluation, which is the first sheet in the workbook, we would like the employee only to be able to edit cell B16, which is the designated employee feedback cell. We would like all the other cells not to be edited.
2) The cells are by default in the worksheet all locked, so in order to change, B16’s status specifically, we right-click cell B16 and choose Format Cells.
3) Make sure the Protection Tab is selected and uncheck Locked.
4) Now go to Review>Changes>Protect Sheet.
5) In the Protect Sheet Dialog Box, choose what you want the users of the worksheet to be able to do, in this case, we are going to uncheck Select locked cells, so users (the employee who receives the workbook at the end of the day in our example) will only be able to select the unlocked cell B16 basically.
6) Set a password. You don’t have to set a password, but without setting a password, protecting the sheet then basically becomes a toggle on/toggle off button and any user can just unprotect the sheet by going to Review>Changes>Unprotect sheet. In this case, we’ll set our password as SheetOne, so that no user can unprotect the sheet without this specific password. It is advisable to always set strong passwords when protecting one’s worksheets/workbooks/documents that contain letters, numbers and special characters. However, for the purposes of this example, the password is a simple one.
7) Click Ok.
8) You’ll be asked to re-enter the password as shown below in the Confirm Password Dialog Box.
9) Re-enter the password and click Ok.
10) Now the only cell a user will be able to select is cell B16, and the employee can thus enter their feedback/comments in this cell.
11) Now go to the sheet called AssessorsComments which is the second sheet in the workbook, and go to Review>Changes>Protect Sheet, in this case, deselect the Select locked cells option and set the password as SheetTwo.
12) Click Ok and re-enter the password, when prompted and then click Ok again. On the sheet called AssessorsComments, no cells can be selected at all, and thus no cell contents in the worksheet can be changed.
Read More: Viewing Excel Worksheets in Multiple Ways
13) We now go to the sheet called ManagersComments and go to Review>Changes>Protect Sheet. We also deselect the Select locked cells option and set this password to SheetThree in the Protect Sheet Dialog Box.
14) Click Ok and re-enter the password when prompted and then click Ok again. Now, one will not be able to select any cells in the worksheet.
15) In order to unprotect one of the sheets, one has just protected. In this case, let’s choose AssessorsComments. Go to Review>Changes>Unprotect Sheet.
16) You will be asked for the password, in this case, it was SheetTwo, so enter SheetTwo.
17) Press Ok and the user will now be able to select and edit all cells in this worksheet.
Workbook Level Protection
Protecting a workbook means that users cannot view hidden sheets, add, move, delete, hide or rename worksheets.
Read More: Working with Dialog Boxes in Excel
1) In order to protect the Workbook at hand, go to Review>Changes>Protect Workbook.
2) Set a password, in the Protect Structure and Windows Dialog Box. In this case, enter the password TheWorkBook.
3) Click Ok. Confirm the password in the Confirm Password dialog box and then click Ok.
4) While the workbook is protected, if you right-click one of the sheets, you’ll see that you will no longer be able to insert a new sheet, hide a sheet, rename the sheet, move the sheet or delete the sheet.
5) In order to unprotect the workbook, go to Review>Changes>Protect Workbook.
6) Enter the password needed to unprotect the workbook, in this case, it was TheWorkBook, in the Unprotect Workbook dialog box.
7) Click Ok and the workbook is now unprotected again and you can thus insert, rename, move, copy or rename sheets.
MS Word also provides multiple ways of protecting one’s document and restricting editing in order to prevent changes from being made to one’s document or certain sections of one’s document. Our Source Document is shown below.
Setting Formatting Restrictions – Blocking Theme or Scheme Switching
We have used the Slice theme, in this document, so we would like to prevent other users from changing this theme since this would totally change the look and formatting of our document.
1) In order to do this, we go to Review>Protect>Restrict Editing.
2) The Restrict Editing Panel should appear on the right-hand side of the document as shown below, with three different numbered sections.
3) Under the first numbered section, called Formatting restrictions, check the Limit formatting to a selection of Styles option.
4) Click on Settings……..
5) In the Formatting Restrictions Dialog Box, ensure that Block theme or Scheme Switching is checked.
6) Say yes to the Prompt to remove styles or formatting that is not allowed.
7) Now, if you go to Design>Themes, you’ll see that the Themes Option is grayed out and can no longer be selected or changed.
8) While the Restrict Editing Panel is still open, click on the Yes, Start Enforcing Protection button as shown below.
9) Enter a password, in this case, we set DocumentOne as the password. The same guidelines for creating a strong password should be followed, for Word documents, in terms of the password should contain numbers, letters and punctuation marks, however, for the purposes of this example we have kept the password simple.
10) Click Ok, and then close the Restrict Editing Panel and now the user will not be able to change the theme, without having the password.
11) In order to enable users to change the theme and stop protection, we need to go to Restrict Editing again, we can either access it the same way we did before or by going to File>Info>Protect Document. Click on the drop-down arrow and select Restrict Editing.
12) The Restrict Editing Panel is then shown on the right-hand side and gives the information that the document is currently protected from editing. Firstly, click the button to Stop Protection.
13) Enter the password for the document set originally, which was DocumentOne and click Ok.
14) One can now reset the Formatting restrictions in the Restrict Editing Panel, in order to allow users to change the theme. The way one does this is in the Formatting restrictions section, clicking on Settings ………..
15) Uncheck the Block Theme or Scheme Switching.
16) Click Ok. Lastly to complete the process totally, uncheck the Limit formatting to a selection of styles, in order to not be limited to certain styles either and then close the Restrict Editing Panel.
17) Now the theme can be changed again, and the formatting restrictions have been totally removed.
Setting Formatting Restrictions – Only Allowing Certain Parts of the Document to be changed/edited
We would now like to ensure that only certain sections of the Word document can be edited. In this case, we’d ideally like our employee, to be able to edit the section for their input, about how they found the assessments and nothing else. In other words, we don’t want the employee to be able to adjust the scores they obtained.
1) In order to achieve this, we go to Review>Protect>Restrict Editing.
2) In the Editing Restrictions section, which is numbered 2. We check the Allow only this type of editing in the document: option and make sure that No changes (Read Only) is selected from the drop-down list.
3) Now we will select the part of the document that we want to allow changes/edits to be made as shown below.
4) Under Exceptions, check Everyone in order to allow anybody who opens this document to edit this particular segment. We could also add users specifically, by clicking on More users……..and then add this employee specifically, but in this case, for demonstration purposes, we will just add Everyone as an exception.
5) Click on the Yes, Start Enforcing Protection button.
6) Set the password to SettingTwo and Click Ok.
7) The Restrict Editing Panel now shows the regions which are allowed to be edited.
8) The user(s) depending on a number of users, will now only be able to edit that segment accordingly and the Restrict Editing Panel can be closed.
9) In order to remove the restrictions set and allow anybody to edit any segment or section of the document, go to Review>Protect>Restrict Editing. Click on the button to stop protection. Enter the password which in this case was SettingTwo and click Ok. Uncheck Exceptions and Allow Only this type of editing in the document: option and then close the Restrict Editing Panel. The whole document can now be freely edited.
Setting Formatting Restrictions – Protecting the Document and making the whole document Read Only.
We now want to make the whole document ready only.
1) In order to do this go to File>Info>Protect Document. Click on the drop-down arrow and choose Restrict Editing.
2) This time in the Restrict Editing Panel, in section 2 Editing restrictions, select the Allow only this type of editing in the document: option and select the No changes (Read Only). Make sure Exceptions are not checked.
3) Now click on the Yes, Start Enforcing Protection button. Enter a password, in this case, it is WholeDocument and then click Ok
And there you have it the whole document is now protected and cannot be edited unless the user enters the specified password.
Download the files that we have used to create this article.
Excel provides ways for data to be protected in worksheets, as well as the overall structure of the workbook. Word also has options for protecting data and preventing changes from being made to whole documents or just certain sections. These options allow for control over sensitive data or information.
Please feel free to comment and tell us if you often protect your worksheets/workbooks or documents.
Review Section: Test your Understanding
1. In a sample workbook, allow users to only be able to select unlocked cells when protecting your worksheet.
2. In a sample workbook, protect your worksheet structure using a password.
3. In a sample document, prevent users from changing the theme.