BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
If you've ever been annoyed by somebody else changing parts of your Excel workbooks it's a good idea to learn about protection to prevent them from doing it again!
Posted by Andrew Gould on 28 February 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Protecting Worksheets and Workbooks in Microsoft Excel
Whenever you give an Excel workbook to somebody else to use you run the risk of them changing things that you didn't intend for them to change. Protecting worksheets and workbooks helps you to control what other people can do to your precious workbooks!
You can watch a video version of this tutorial if you prefer, and you can download the file used to demonstrate the techniques if you'd like to follow along - or have a look at our Excel training courses.
The Example We'll Use
To demonstrate protection of an Excel workbook we'll use a simple business model for a chocolate shop. There are three main things that we want to do to this file:
- Protect the sheet containing the calculations so that none of the cells can be changed.
- Protect the sheet containing the inputs so that only the input cells can be changed.
- Protect the structure of the workbook so that worksheets can't be deleted, inserted or renamed.
Protecting an Entire Worksheet
If you want to prevent changes to any of the contents of a particular worksheet you can simply protect the entire sheet. To do this:
- Select the worksheet you want to protect.
We don't want anything on the Calculations sheet to be changed.
- From the ribbon select: Review -> Protect Sheet.
Use this dialog box to specify what people can do to the worksheet once it has been protected.
- Make sure that the checkbox is checked.
- You can enter an optional password here. If you add a password you will have to enter the same password in order to unprotect the sheet later.
- Use these checkboxes to control what people can do to the worksheet after it is protected.
- Click OK to protect the sheet.
- If you have entered a password you will have to confirm it and click OK to finally protect the sheet.
Type in your password again and click OK.
Worksheet passwords in Excel use a technique called hashing. The method used in Excel isn't a particularly robust form of security, so beware of relying on worksheet protection to protect sensitive data.
Testing if a Worksheet is Protected
It's very easy to test if you've protected a worksheet: simply try typing into one of the cells.
The message tells you that you can't modify this sheet until it has been unprotected.
Unprotect a Worksheet
You can unprotect a worksheet using almost the same method that you used to protect it.
- Select the worksheet that you want to unprotect.
- From the ribbon choose: Review -> Unprotect Sheet.
- If you have entered a password you will be prompted for it at this stage.
Enter the password and click OK to unprotect the sheet.
Protecting an entire worksheet is easy enough, but what if you want people to be able to change some of the cells in a sheet? The answer lies in the next part of this series!