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 to know the difference between protect sheet and protect workbook in MS excel.
Download Practice Workbook
Get the sample file and try it yourself.
What Are Protect Sheet and Protect Workbook in Excel?
In Microsoft Excel, the feature that prevents other users from accidentally or deliberately editing, moving, or deleting data in a worksheet is named Protect Sheet. You can lock the cells on your Excel worksheet with password protection.
On the other hand, the Protect Workbook feature isolates other users from viewing hidden worksheets and editing them in the entire workbook. It also benefits with password protection.
Read More: [Fixed!] Protect Workbook in Excel Not Working (with Quick Solution)
Difference Between Protect Sheet and Protect Workbook in MS Excel
For example, a company has decided to hire consultants, to test the skill levels and competencies of its employees, using assessments.
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 EmloyeeEvaluation worksheet.
In the same workbook, the AssessorsCommments sheet allows for the assessor to give feedback. The assessor inputs the scores and feedback. Then sends the spreadsheet to the manager for input.
Lastly in the ManagersComments sheet, the manager puts input and sends it back to the assessor, and then finally the assessor sends the spreadsheet 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. This is where we will work to apply Protect Sheet and Protect Workbook commands in MS Excel.
Protect Sheet in MS Excel
First, we will learn about protecting individual worksheets in excel. Let’s follow the steps below.
- First, go to the first worksheet, EmployeeEvaluation.
- Here, select cell C17 as we want the employee only to be able to edit this cell, which is the designated employee feedback cell.
- Next, right-click on it and choose Format Cells.
- In the Format Cells dialogue box, go to the Protection Tab and uncheck the Locked option.
- Afterward, press OK.
- Now, go to the Review tab and select Protect Sheet under the Protect group.
- 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 employees will only be able to select the unlocked cell C17.
- Along with it, mark check the Select unlocked cells box to operate the free cells.
- Then, set up a password for this worksheet and press OK.
- Following, again type the password in the Confirm Password dialogue box.
- Lastly, press OK.
- That’s it, you will see that cell C17 is locked now.
- Follow the similar procedure for cell C7 of the other two worksheets as well.
- Simply go to the Review tab again and select Unprotect Sheet.
- Then, enter the password and press OK to unlock it.
Read More: How to Protect Excel Workbook from Editing (5 Effective Methods)
Protect Workbook in MS Excel
Protecting a workbook means that users cannot view hidden sheets, add, move, delete, hide or rename worksheets. Let’s see how it works.
- In order to protect the total workbook at hand, go to the Review tab and choose Protect Workbook from the Protect group.
- Then, set a password in the Protect Structure and Windows dialog box.
- Following, click OK.
- Now, confirm the password in the Confirm Password dialog box.
- Further, click OK.
- In this condition, if you right-click one of the sheets, you’ll see that you will no longer be able to insert, hide, rename, move or delete a sheet.
- Go to the Review tab again and click on Protect Workbook.
- Following, insert the password in the Unprotect Workbook dialogue box.
- Click OK.
- Finally, you will notice that the workbook is not protected anymore and you can thus insert, rename, move, copy or rename sheets.
Read More: Protect and Share Excel Workbook (2 Quick Methods)
Things to Remember
- Though inserting a password is optional, without setting a password, protecting the sheet then basically becomes a toggle on/toggle off button and any user can just unprotect the sheet anytime.
- It is advisable to always set strong passwords that contain letters, numbers, and special characters for protecting one’s worksheets or workbooks.
Henceforth, we learned the difference between protect sheet and protect workbook in MS Excel. Please feel free to comment and tell us your feedback on this article. Keep an eye on ExcelDemy for more excel blogs.
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.
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.