This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Protecting Worksheets and Workbooks in Excel Part one of a three-part series of blogs
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! Protecting Worksheets and Workbooks in Microsoft Excel (this blog) Protecting Part of a Worksheet Protecting the Structure of a Workbook

Protecting Worksheets and Workbooks in Excel

Part one of a three-part series of blogs

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!

Protecting Worksheets and Workbooks in Microsoft Excel (this blog)
Protecting Part of a Worksheet
Protecting the Structure of a Workbook

Posted by Andrew Gould on 28 February 2012

In this blog

The Example We'll Use
Protecting an Entire Worksheet
Testing if a Worksheet is Protected
Unprotect a Worksheet
What's Next?

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.

What's Next?

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!