Excel Security: Worksheet vs. Workbook Level Protection

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.

Introduction

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.

Employee Evaluation Example Data

Employee Evaluation Example Data 2

Employee Evaluation Example Data 3

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.

Worksheet Level Protection Image 1

3) Make sure the Protection Tab is selected and uncheck Locked.

Format Cells Dialog, Protection Tab

4) Now go to Review>Changes>Protect Sheet.

Review Tab in Excel, Protect Sheet Command

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.

Protect Sheet Dialog Box

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.

Excel Protect Sheet Dialog, Select unlocked cells option

7) Click Ok.

8) You’ll be asked to re-enter the password as shown below in the Confirm Password Dialog Box.

Confirm Password dialog

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.

Worksheet Level Protection Image 2

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.

Protect Sheet Dialog Box, Select Unlocked Cells

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.

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.

Protect Sheet Dialog Box, Select Unlocked Cells

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.

Excel Review Tab, Unprotect Sheet Command

16) You will be asked for the password, in this case, it was SheetTwo, so enter SheetTwo.

Unprotect Sheet Dialog Box, Password to open the sheet

17) Press Ok and the user will now be able to select and edit all cells in this worksheet.

Employee Evaluation Data, Unprotect Sheet Dialog Box

Excel Review Tab, Employee Evaluation Data

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.

Excel Review Tab, Protect Workbook Command

2) Set a password, in the Protect Structure and Windows Dialog Box. In this case, enter the password TheWorkBook.

Excel Review Tab, Protect Workbook, Protect Structure and Windows Dialog Box

3) Click Ok. Confirm the password in the Confirm Password dialog box and then click Ok.

Confirm Password Dialog Box

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.

Employee Evaluation Sample Data

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.

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.

Workbook level protection in Excel


Crossover Tips


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.

Employee Evaluation Data in MS Word

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.

Read More: Creating a pivot table in a worksheet connecting to another worksheet in the same workbook

1) In order to do this, we go to Review>Protect>Restrict Editing.

Protect, Restrict Editing Command

2) The Restrict Editing Panel should appear on the right-hand side of the document as shown below, with three different numbered sections.

Restrict Editing in MS Word

3) Under the first numbered section, called Formatting restrictions, check the Limit formatting to a selection of Styles option.

Restrict Editing Dialog Box

4) Click on Settings……..

5) In the Formatting Restrictions Dialog Box, ensure that Block theme or Scheme Switching is checked.

Formatting Restrictions dialog box in MS word

6) Say yes to the Prompt to remove styles or formatting that is not allowed.

Microsoft Word general dialog box

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.

MS Word Design Tab

8) While the Restrict Editing Panel is still open, click on the Yes, Start Enforcing Protection button as shown below.

Restrict Editing Dialog Box MS Word

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.

Start Enforcing Protection Dialog Box

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.

Employee Evaluation Data in MS Word

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.

Restrict Editing in Info Panel of MS Word

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.

Restrict Editing in MS Word

13) Enter the password for the document set originally, which was DocumentOne and click Ok.

Unprotect Document dialog box in MS Word

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 ………..

Restrict Editing in Word

15) Uncheck the Block Theme or Scheme Switching.

Formatting Restrictions in MS Word

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.

Restrict Editing in MS Word

3) Now we will select the part of the document that we want to allow changes/edits to be made as shown below.

Employee Evaluation Data in MS Word

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.

Restrict Editing in MS Word

5) Click on the Yes, Start Enforcing Protection button.

Restrict Editing in MS Word

6) Set the password to SettingTwo and Click Ok.

Start Enforcing Protection

7) The Restrict Editing Panel now shows the regions which are allowed to be edited.

Restrict Editing in MS Word8) 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.

Protect Document in Info Panel in MS Word

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.

Restrict Editing in MS Word

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 Area

Download the files that we have used to create this article.

Word-Crossover

WorksheetVersusWorkbookLevelofProtection

Conclusion

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.

Useful Links

Protecting a Worksheet

Protecting a Workbook

Allow Changes to Certain Parts of a Protected Document

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS) and biofuels. She enjoys showcasing the functionality of Excel in various disciplines.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.

You may also like...

2 Responses

  1. agnius.bartninkas@outlook.com' Agnius says:

    I was expecting an actual comparison and pros and cons of these types of protection. This kind of protection can be easily breached by VBA and thus is in not way safe. However, that is something one has to live with when facing a situation where user input is needed.

    A better way to save Excel files from unwanted editing is by encrypting it so that a person who does not know the password for the workbook cannot even open it, or can only open it as a read-only file. This kind of protection cannot be broken by VBA or any other means. It can be done by accessing the General Options in the Save As dialog window.

    • Taryn N says:

      Hi Agnius
      Thank you for your comment. Point taken. However, these are topics tested in many Excel exams and there is often confusion since workbook level is actually protecting the worksheet structure, so it was in that context that the tutorial was made and made in order to address those issues.

      This topic (worksheet level and workbook level protection) is covered in the detailed syllabus for the Microsoft Excel Expert Exam for 2013 that is exams (77-427 and 77-428), in the official book released by Microsoft Press – MOS 2013 Study Guide Microsoft Excel Expert by Mark Dodge.

      So it is relevant to go over, for people who are studying for the MOS Excel Expert Exam and any other Excel exam. Also while I agree with you that encryption and VBA also play a role in more advanced level protection (I will do another tutorial on the more advanced level options :-)) .

      My personal opinion is sometimes depending on one’s organization, one’s needs may not be that complex so its worthwhile knowing what Excel has available in terms of simpler options. It is one layer of protection and something is better than nothing at all, in my opinion. A workbook that has sensitive information that has no protection at all, is less secure than a workbook that has worksheet level and workbook level protection. Also from the psychological perspective there is now some form of inhibiting barrier, if one uses worksheet level or workbook level standard protection options.

      However, you have opened up a very interesting debate with your points – is something in terms of protecting one’s data at a simpler level really better than nothing at all or should one address this issue from the VBA and encryption level only.

      I will in the next tutorial, address some of the points you’ve raised and give you credit for the interesting questions/points you’ve posed in the debate section of the next tutorial on Encryption and VBA protection.

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.