Preventing Unauthorized Changes to a Workbook

Preventing Unauthorized Changes to a Workbook

In many businesses, one person or team is in charge of creating workbooks that many people in the business use. In some cases, the person who created the workbook doesn't want the people who are using the workbook to change it. In other cases, the creator wants users to be able to enter data but does not want users to change formatting or any of the formulas or functions that perform the calculations.

Whatever the case, you can password-protect a worksheet, as explained in the following steps. The steps might seem backward to you at first; you unlock certain cells to permit users to change them before you password-protect the worksheet. However, after you password-protect the worksheet, you cannot unlock cells, so you must mark the cells as unlocked before password-protecting the worksheet. Now that you know that, take the following steps to password-protect a worksheet:

1. Open the worksheet you want to protect.

2. Drag over any cells you want to unlockthat is, cells whose contents you want other users to be able to edit. (By default, all cells in a password-protected worksheet are locked, preventing other users from changing the cells or their contents. You must unlock any cells you want other users to be able to change.)

3. Open the Format menu and choose Cells . The Format Cells dialog box appears.

4. Click the Protection tab. The Protection options appear, as shown in Figure 10.11.

   Figure 10.11. You must unlock any cells that you want to enable other users to change.

   

5. Click Locked to remove the check mark next to it.

6. Click OK .

7. To hide any formulas in the worksheet, select each cell that contains a formula you want to hide. (You can drag over a range of cells or Ctrl+click each cell to select multiple cells.)

8. Open the Format menu and choose Cells . The Format Cells dialog box appears.

9. Click Hidden to place a check mark in its box.

10. Click OK .

11. Open the Tools menu, point to Protection , and choose Protect Sheet . The Protect Sheet dialog box appears, as shown in Figure 10.12.

    Figure 10.12. You can password-protect a worksheet to prevent unauthorized changes.

    

12. If no check mark appears in the box next to Protect Worksheet and Contents of Locked Cells , then click the option to add a check mark.

13. Click in the Password to Unprotect Sheet text box, and type a unique password that's easy for you to remember but difficult for anyone else to guess.

14. Under Allow All Users of This Worksheet To , click each action you want other users of this worksheet to be able to perform. (You can turn off an action; remove the check mark by clicking it again.)

15. Click OK . Excel prompts you to retype the password.

16. Type the password again, exactly as you typed it in step 13, and click OK .

Caution

Write down the password you use, and store it in a secure place. If you lose the password, you will be unable to edit the worksheet yourself.

If you need to, edit the worksheet later, you must unlock it. To unlock a worksheet, open it and then choose Tools , Protection , Unprotect Sheet . Type the required password (the password you assigned to the worksheet), and click OK .

The Absolute Minimum

Many users wrestle with worksheets, never satisfied with the way they print and never realizing that Excel features a host of tools for controlling the printouts. Fortunately, now that you have completed this chapter, those print control tools are right at your fingertips, and you now have complete control over the way Excel lays out your worksheets in print. Specifically, you know how to

Pull up the Print Preview screen, so you can see how Excel will print your worksheets before you actually print them.

Print wide worksheets in landscape mode and/or have Excel shrink them to fit on a fixed number of pages.

Adjust the margins in the Page Setup dialog box and in Print Preview.

Add headers and footers that print at the top and bottom of every page.

Print only a selected portion of a worksheet.

Force Excel to print column headings at the top of each page or row headings along the left side of each page.

Control the order in which Excel prints the pages.

Adjust page breaks to control the way Excel distributes your worksheet data on two or more pages.

Print a worksheet.

Prevent unauthorized changes to your worksheet if you decide to share it with other people.

With the skills you acquired in this chapter, you have complete control over the appearance and structure of your worksheets as they appear in print. You also have complete control over who can make changes to the workbook and the types of changes they can make. At this point, you have mastered the techniques for creating, saving, enhancing, and printing your workbooks and worksheets, and are now prepared to go beyond the basics in Part 4.