Protecting Workbooks and Worksheets

Excel 2007 gives you the ability to share your workbooks over the Web, over a corporate intranet, or by copying files for other users to take on business trips. An important part of sharing files, however, is ensuring that only those users you want to have access to the files can open or modify them. For example, Consolidated Messenger might have a series of computers available in a processing center so supervisors can look up package volumes and handling efficiency information. Although those computers are vital tools for managing the business process, it doesn't help the company to have unauthorized personnel, even those with good intentions, accessing critical workbooks.

You can limit access to your workbooks or elements within workbooks by setting passwords. Setting a password for an Excel 2007 workbook means that any users who want to access the protected workbook must enter the workbook's password in a dialog box that appears when they try to open the file. If users don't know the password, they cannot open the workbook.

To set a password for a workbook, open the workbook to be protected, click the Microsoft Office Button, and then click Save As. The Save As dialog box appears, with the name of the open workbook in the File name box. At the lower-left corner of the dialog box, click the Tools button and then click General Options to open the Save Options dialog box. In the Save Options dialog box, you can require users to enter one password to open the workbook and another to modify it. After you click OK, a Confirm Password dialog box appears so that you can verify the passwords required to access and modify the workbook. After you have confirmed the passwords, click Save in the Save As dialog box to finish adding password protection to the workbook. To later remove the passwords from a workbook, repeat these steps, but delete the passwords from the Save Options dialog box and save the file.

Tip

The best passwords are random strings of characters, but random characters are hard to remember. One good method of creating hard-to-guess passwords is to combine elements of two words with a number in between. For example, you might have a password wbk15pro, which could be read as "workbook, Chapter 15, protection." In any event, avoid dictionary words in English or any other language, as they can be found easily by password-guessing programs available on the Internet.

If you want to allow anyone to open a workbook but want to prevent unauthorized users from editing a worksheet, you can protect a worksheet by displaying that worksheet, clicking the Review tab, and then, in the Changes group, clicking Protect Sheet to open the Protect Sheet dialog box.

In the Protect Sheet dialog box, you select the Protect Worksheet And Contents Of Locked Cells check box to protect the sheet. You can also set a password that a user must type in before protection can be turned off again and choose which elements of the worksheet a user can change while protection is turned on. To enable a user to change a worksheet element without entering the password, select the check box next to that element's name.

The check box at the top of the worksheet mentions locked cells. A locked cell is a cell that can't be changed when worksheet protection is turned on. You can lock or unlock a cell by right-clicking the cell and choosing Format Cells from the shortcut menu that appears. In the Format Cells dialog box, you click the Protection tab to display the Protection tab page and select the Locked check box.

When worksheet protection is turned on, selecting the Locked check box prevents unauthorized users from changing the contents or formatting of the locked cell, whereas selecting the Hidden check box hides the formulas in the cell. You might want to hide the formula in a cell if you draw sensitive data, such as customer contact information, from another workbook and don't want casual users to see the name of the workbook in a formula.

Finally, you can password-protect a cell range. For example, you might want to let users enter values in most worksheet cells but also want to protect the cells with formulas that perform calculations based on those values. To password-protect a range of cells, select the cells to protect, click the Review tab, and then, in the Changes group, click Allow Users to Edit Ranges. The Allow Users to Edit Ranges dialog box appears.

To create a protected range, click the New button to display the New Range dialog box. Type a name for the range in the Title box and then type a password in the Range password box. When you click OK, Excel 2007 asks you to confirm the password; after you do, click OK in the Confirm Password dialog box and again in the Allow Users to Edit Ranges dialog box to protect the range. Now, whenever users try to edit a cell in the protected range, they are prompted for a password.

Tip

Remember that a range of cells can mean just one cell!

In this exercise, you password-protect a workbook, a worksheet, and a range of cells and then hide the formula in a cell.

USE the SecureInfo workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\Sharing folder.

OPEN the SecureInfo workbook.

1.	Click the Microsoft Office Button and then click Save As. The Save As dialog box appears.
2.	Click the Tools button and then click General Options. The General Options dialog box appears.
3.	Type work15pro in the Password to open box.
4.	Type pro15work in the Password to modify box.
5.	Click OK. The Confirm Password dialog box appears.
6.	In the Reenter password to proceed box, type work15pro.
7.	Click OK. The Confirm Password dialog box changes to ask you to reenter the password to modify the workbook.
8.	In the Reenter password to modify box, type pro15work.
9.	Click OK. The Confirm Password dialog box disappears.
10.	Click Save. Excel 2007 saves the workbook.
11.	If necessary, click the Performance sheet tab. The Performance worksheet appears.
12.	Right-click cell B8 and then click Format Cells. The Format Cells dialog box appears.
13.	Click the Protection tab. The Protection tab appears.
14.	Select the Hidden check box and then click OK. Excel 2007 formats cell B8 so that it won't display its formula after you protect the worksheet.
15.	On the Review tab, in the Changes group, click Protect Sheet. The Protect Sheet dialog box appears.
16.	In the Password to unprotect sheet box, type prot300pswd.
17.	Clear the Select locked cells and Select unlocked cells check boxes; then click OK. The Confirm Password dialog box appears.
18.	In the Reenter password to proceed box, type prot300pswd and then click OK. The Confirm Password dialog box disappears.
19.	Click the Weights sheet tab. The Weights worksheet appears.
20.	Select the cell range B2:C7.
21.	On the Review tab, in the Changes group, click Allow Users to Edit Ranges. The Allow Users to Edit Ranges dialog box appears.
22.	Click New. The New Range dialog box appears, with the range B2:C7 called out in the Refers to cells box.
23.	In the Title box, type AllWeights.
24.	In the Range password box, type work15pro and then click OK.
25.	In the Confirm Password dialog box, reenter the password work15pro. The range appears in the Allow Users to Edit Ranges box.
26.	Click Protect Sheet. The Protect Sheet dialog box appears.
27.	In the Password to unprotect sheet box, type work15pro and then click OK. The Protect Sheet dialog box disappears.
28.	In the Confirm Password dialog box, reenter the password work15pro.
29.	Click OK.
30.	On the Quick Access Toolbar, click the Save button to save your changes.

CLOSE the SecureInfo workbook.