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 from password protection.
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, and the score is recorded in an Excel spreadsheet. There is a section for employee feedback on the Employee Evaluation worksheet.
In the same workbook, the Assessor’s Comments sheet allows the assessor to give feedback. They input the scores and feedback, then send the spreadsheet to the manager for further input.
In the Manager’s Comments sheet, the manager puts comments and sends it back to the assessor, and then finally the assessor sends the spreadsheet to the employee for feedback.
This means there are certain sections that the assessor and manager would not want the employee to edit. We will apply Protect Sheet and Protect Workbook commands in MS Excel for that.
Protect Sheet in MS Excel
- Go to the first worksheet, Employee Evaluation.
- Select cell C17, which is the designated employee feedback cell.
- Right-click on it and choose Format Cells.
- In the Format Cells dialogue box, go to the Protection tab and uncheck the Locked option.
- Press OK.
- 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.
- We are going to uncheck Select locked cells, so employees will only be able to select the unlocked cell C17.
- Check the Select unlocked cells box.
- Set up a password for this worksheet and press OK.
- Retype the password in the Confirm Password dialog box.
- Press OK.
- The cell C17 is locked.
- Follow the similar procedure for cell C7 of the other two worksheets as well.
- Go to the Review tab again and select Unprotect Sheet.
- Enter the password and press OK to unlock it.
Protect Workbook in MS Excel
- Go to the Review tab and choose Protect Workbook from the Protect group.
- Set a password in the Protect Structure and Windows dialog box.
- Click OK.
- Re-enter the password in the Confirm Password dialog box.
- Click OK.
- 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.
- Insert the password in the Unprotect Workbook dialog box.
- Click OK.
- The workbook is not protected anymore, and you can thus insert, rename, move, copy or rename sheets.
Read More: Protect and Share Excel Workbook
Things to Remember
- Though inserting a password is optional, without setting a password, protecting the sheet basically becomes a toggle on/toggle off button and any user can unprotect the sheet anytime.
- It is advisable to always set strong passwords that contain letters, numbers, and special characters for protecting worksheets or workbooks.
Download the Practice Workbook
Related Articles
- How to Protect Excel Workbook from Editing
- How to Mark Workbook as Final in Excel
- [Fixed!] Protect Workbook in Excel Not Working
<< Go Back to Protect Workbook in Excel | Excel Protect | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.
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.