Section 22.3. Locked and Hidden Cells | Excel 2007[c] The Missing Manual

22.3. Locked and Hidden Cells

Excel's data validation tools help make sure funky data doesn't end up in your worksheets. But they don't protect your worksheets against things like accidentally deleted formulas, mistakenly scrambled formatting, and "unintentionally" modified Maximum Deficit Spending values. To defend against these dangers, you need to use Excel's worksheet protection features.

To understand how worksheet protection works, you need to know that each cell can have one of two special settings:

Locked . When a cell is locked, you can't edit it.
Hidden . When a cell is hidden, its contents don't appear in the formula bar. The cell still appears in the worksheet, but if the cell uses a formula, you can't see the formula.

You can use these settings individually or together. When a cell is both locked and hidden, you can't edit it, or view it in the formula bar. On the other hand, if a cell is hidden but not locked, people can edit the cell but they can never tell whether the cell uses a formula, because Excel keeps that information secret.

The most important thing you need to understand about locked and hidden cells is that these settings come into effect only when the worksheet is protected . If the worksheet isn't protected (and every worksheet begins its life without protection), Excel doesn't use these settings at all. In other words, there are two steps to building a bulletproof worksheet. First, you start by specifying which cells are locked and hidden, and then you finish up by protecting the sheet so your settings take effect.

Interestingly, every cell in your worksheet starts off in an unhidden and locked state. If you switch on worksheet protection without making any changes, your whole worksheet becomes read-only. Excel uses this approach for a reason. Typically, you'll use worksheet protection to make sure the person using your workbook can edit only a few select cells. It's much easier to designate the few cells that are editable than it is to try to select every single cell that needs to be locked. (Remember, every Excel worksheet boasts millions of cells, most of which are empty.)

Note: Once you've protected a worksheet, you can't change the protection settings of any cells. Of course, you can unprotect a worksheet, as explained below, and then change the cell's protection settings.

22.3.1. Protecting a Worksheet

Here are the steps you need to follow to protect your worksheet:

First, unlock all the cells into which you want people to type information .

You can perform this procedure one cell at a time, or you can select an entire range of cells. Once you've made your selection, right-click it, and then choose Format Cells. The Format Cells dialog box appears.
Click the Protection tab. Then, turn off the Locked checkbox, and then click OK .

Next , you need to hide formulas that you don't want the workbook user to see.

Tip: You can lock or unlock cells without going to the Format Cells dialog box. Just select the cells, and then choose Home Cells Format Lock.

Select the cell or cells with the formulas you want to hide, right-click the selection, and then choose Format Cells again. This time, click the Protection tab, turn on the Hidden checkbox, and then click OK .

If you want, you can change both the Hidden and Locked settings for a cell or group of cells at the same time. Once you've finished unlocking and hiding to your heart's content, it's time to protect the sheet.
Select Review Changes Protect Sheet. In the Protect Sheet dialog box that appears, turn on the checkbox labeled "Protect worksheet and contents of locked cells .

In addition to protecting the contents of unlocked cells, this dialog box lets you toggle on or off a list of Excel actions that you want to allow users of your worksheet to perform, as described in the next step.
From the "Allow all users of this worksheet to" list, turn on the things that you want users of your worksheet to be able to do .

Excel's standard approach is to restrict everything except for cell selection (the first two options). Here's a setting-by-setting breakdown of your choices:
- Select locked cells . Turn off this checkbox if you want to prevent people from moving to locked cells.
  
  Note: Keep in mind that if you can't select a locked cell, there's also no way to copy and paste the information in the cell to another worksheet or program. If you want the people using your workbook to be able to do this, you should keep the "Select locked cells" setting switched on.
- Select unlocked cells . Turn off this checkbox if you want to prevent people from moving to unlocked cells. You won't use this setting very often, but you might use it in conjunction with the "Select locked cells" setting to lock someone out of the worksheet entirely.
- Format cells, Format columns , and Format rows . Turn on these checkboxes if people need to be able to format individual cells or entire columns and rows. If you allow row and column formatting, Excel also permits people to hide rows and columns. However, Excel never lets anyone change the locked and hidden settings of a cell while it's protected.
- Insert columns and Insert rows . Turn on these checkboxes if you want to let people insert new rows or columns.
- Insert hyperlinks . Turn on this checkbox if you want to let people insert hyperlinks in unlocked cells. This setting can be dangerous because a hyperlink can point to anything from another worksheet to a malicious Web page. See Chapter 26 for more information about hyperlinks.
- Delete columns and Delete rows . Turn on these checkboxes to bestow the ability to remove columns or rows. Use this setting at your peril because it lets people decimate your worksheetfor example, removing entire ranges of data even if they contain locked cells.
- Sort . Turn on this checkbox to let people sort unlocked cells, while keeping locked cells impervious to sorting.
- Use AutoFilter . Turn on this checkbox to let people use filtering on any tables in the worksheet. See Chapter 14 for more information about tables.
- Use PivotTable reports . Turn on this checkbox to let people manipulate any pivot tables in the worksheet. See Chapter 21 for more information about pivot tables.
- Edit objects . Turn on this checkbox to let people edit or delete embedded objects in the worksheet. These objects can include data from other programs or, more commonly, pictures (Chapter 19) or charts (Chapter 17).
- Edit scenarios . Turn on this checkbox to let people edit or delete what-if scenarios. See Chapter 20 for more information about scenarios.
If you want to stop other users from unprotecting the worksheet, specify a password in the "Password to unprotect sheet" text box (see Figure 22-12) .

Once you protect a worksheet, anyone can unprotect it. All a person needs to do is select Review Changes Unprotect Sheet. This behavior makes sense if youre just using protection to prevent people from making casual mistakes. However, if you're worried about deliberate tampering, or if you want to create a truly invulnerable worksheet, it's a good idea to set a password. If you do, no one can unprotect the sheet without supplying the password.

Note: Security experts point out that Excel's password features aren't good enough to keep out experienced computer hackers. However, they are sufficient to stop ordinary people from changing something they shouldn't.

Figure 22-12. The Protect Sheet dialog box lets you set a password that prevents anyone else from tampering with your worksheet.
Click OK .

The protected worksheet doesn't look any different, but if you try to edit a locked cell, you get an error message explaining that the cell's locked (but explaining that you can unlock the worksheet by selecting Review Changes Unprotect Sheet).

Tip: Protected worksheets have a nice feature: You can quickly find all the unlocked cells. Just click the Tab key to move from one unlocked cell to the next. When you reach the last unlocked cell, Excel automatically jumps back to the first unlocked cell. If you want to make it even easier to navigate a dense worksheet, consider disabling the "Select locked cells" setting in the Protect Sheet dialog box.

POWER USERS' CLINIC
Exerting Even More Control with IRM

Worksheet protection isn't the only way you can lock down a spreadsheet. Microsoft Office also has another technology for controlling what people can do. It's called Information Rights Management (IRM).

IRM is a powerful feature that can exert Big Brother-like control over your spreadsheets. For one thing, it can prevent people from copying data to the clipboard or printing a workbook. It can also automatically "expire" your workbook, so that it can't be accessed after a date you choose. Most importantly, IRM is user-specific , which means you can give different permissions to different people.

To run IRM on your own, you need to have some seriously high-end software, including a company server with Windows Server 2003, Active Directory, and a database product like SQL Server. (You can find the full requirements in an article at http://www.microsoft.com/technet/technetmag/issues/2005/01/OfficeSpace.) Unless you're at the helm of a fairly large company, you probably don't have this software on hand (or you don't want the monumental task of setting it up to use IRM). As an alternative, Microsoft lets people sign up for its free IRM service, which works over the Internet. Although this service has existed for several years , it's still described as a "free trial," which means Microsoft could conceivably charge for it in the future.

Microsoft's IRM service uses Passport authentication , which is the technology that underlies their popular Hotmail service for online email. If you have a Hotmail account, you can use your email and password to log on to Microsoft's IRM service. If you don't, you need to create a Hotmail account or get a passport for an email address you already have. Both tasks require that you go through a free Web sign-up process, and pick a password.

Overall, IRM makes most sense as a specialized tool for big business. But if you want to experiment with it, start by choosing Review Changes Protect Workbook Restricted Access. Follow the prompts to install a small Office update that adds the IRM features, and sign up for Microsofts free IRM service. You can then control who can use your workbook (by specifying their email address) and what they're allowed to do.

When you open an IRM-protected workbook, you need to supply your email address and password. Excel then communicates with the IRM service to find out what you're allowed to do. If you aren't in the list of authorized people, you're out of luck. And if you try to open an IRM-protected workbook on a computer that doesn't have an add-in, Excel prompts you to download it from the Web.

22.3.2. Protecting the Entire Workbook

You can use one more level of protection: Excel gives you the power to protect an entire workbook. When a workbook's protected, Excel prevents people from inserting, moving, or removing worksheets (tasks that were covered in Chapter 4).

Workbook protection works hand-in-hand with worksheet protection. If you use workbook protection but not worksheet protection, people can still edit all the cells in your worksheets. However, they can't delete the worksheets or add new ones. On the other hand, if you use workbook protection and worksheet protection, people can't tamper with your data or the structure of your workbook.

To enable workbook protection, select Review Changes Protect Workbook Protect Structure and Windows. The awkwardly named "Protect Structure and Windows dialog box appears, as shown in Figure 22-13.

Figure 22-13. The "Protect Structure and Windows" dialog box looks a little like the Protect Worksheet dialog box, but it provides fewer options. You still have the same chance to lock out miscreants by using passwords, however.

The "Protect Structure and Windows" dialog box provides two checkboxes:

Structure . When you turn this option on, the people using your workbook can't insert or delete worksheets. They also can't rename an existing worksheet, hide it, or move it from one place to another.
Windows . When you turn this option on, Excel doesn't let anyone change the size or position of your workbook window inside the main Excel window. (Usually, this setting has no effect because the workbook window's maximized , which means it's given the largest size that fits in the Excel main window.) In addition, you can't use or change other fancy viewing settings, like frozen columns and split windows (both of which are described in Chapter 7).

Note: In rare cases, you might use the Windows protection setting to make sure that your workbook viewers don't change specialized view settings you've created. However, most of the time it's not worth the trouble, because different people using different computers (with different monitors , different display resolutions , and different eyeglass prescriptions) may have good reasons to want to adjust these settings.

You can also supply a password to prevent people from unprotecting your workbook. You can use the same password that you used to protect a worksheet, or you can choose a new password.

Once you're finished, click OK to apply your protection settings. You can remove workbook protection by once again choosing Review Changes Protect Workbook Protect Structure and Windows, although if you used a password, youll need to have it handy.

22.3.3. Protecting Cell Ranges (with More Passwords)

As you've already seen, you can use passwords to lock up individual worksheets or the entire workbook. For most Excel users, this is as far as they want to go. But if you're hungering for more protection settings, you do have another option. You can lock up individual ranges . The ranges you protect can be anything from a single cell to a larger group that encompasses entire rows and columns.

Note: The ability to protect individual cell ranges sounds great. However, it's often more trouble than it's worth. After all, do you want to manage a workbook with dozens of different password-protected areas, each with different passwords? If you can, save yourself some aggravation by separating the data you need to protect and placing it in a dedicated worksheet.

Here's how to add protection to a specific range of cells:

Select the cells you want to protect .
Choose Review Changes Allow Users to Edit Ranges .

The "Allow Users to Edit Ranges" dialog box appears.
Click New to create your first range .

The New Range dialog box appears (Figure 22-14).

Figure 22-14. The New Range dialog box lets you supply three key pieces of information for your rangethe title, cell references, and password.
Fill in a title and password for your range .

You don't need to explicitly set the "Refers to cells" box because Excel automatically fills it in with the cells you selected in step 1. You just have to supply a descriptive title (like "Budget Numbers") and a matching password that unlocks the range for editing.
Click OK .

Excel adds your range to the list in the "Allow Users to Edit Ranges" dialog box (Figure 22-15).

You can click New again to create more protected ranges. Or, you can click OK to close the "Allow Users to Edit Ranges" dialog box.
Choose Review Changes Protect Sheet, enter a master password (which only you know), and then click OK .

Remember, your ranges don't become password-protected until you turn on protection for the worksheet.

Now, if people need to edit certain sections of your worksheet, you can give them the appropriate range password. However, you won't share the password that turns off worksheet protection with anyone. That's for your use only.

Figure 22-15. The workbook associated with this dialog box has a single protected range. Using this dialog box, you can modify a range (click Modify), or remove it (click Delete). You can also add Windows permission settings (discussed in the next section).

Figure 22-16 shows what happens when you start typing in a protected range.

Figure 22-16. A nice feature about protected cell ranges is that the person using the workbook doesn't need to explicitly turn off the protection. Instead, when someone starts typing in one of the cells in the protected range, Excel pops up a dialog box asking for the password, as shown here. Fill it in, and you're ready to go.

22.3.4. Allowing Specific Windows Users to Edit a Range

When protecting cell ranges, there's one more wrinkle. As with worksheet and workbook protection, you can restrict access using a password, which the person using your workbook must supply before changing one of the protected cells. Alternatively, you can allow people based on their Windows user account . (User accounts are the login system Windows uses to let multiple people share a single computer.)

This option's a bit trickierin order for it to work right, the person who uses your workbook needs to log on to the same computer you use, or the same network server. In other words, this technique can work for a small team of people working on a company network, but it's no good for more independent Excel fans.

Keep one other point in mind. The user account permissions work in addition to the cell range password. In other words, you follow the same process described in the previous section to lock up a range of cells using a specific password. Then, you give some special people the ability to edit the cell range straight awayno password required.

Tip: If you never intend to actually use the cell range password, you can keep it a secret. However, the password's a good fallback if people need to edit your workbook when they aren't connected to the company network (for example, if someone takes it home on a laptop). At that point, Excel can't recognize the user, so the person who's using the spreadsheet needs to use the cell range password to unlock the cells.

Here's how to apply user account permissions:

Choose Data Data Tools Allow Users to Edit Ranges .

The "Allow Users to Edit Ranges" dialog box appears.
Select the range you want to use .

If you haven't created the range yet, you can click the New button to add it now. Follow the steps in the previous section to create the range.
Click Permissions .

A Windows permission dialog box appears. You may have seen a window like this before if you've ever modified the permissions on your files. The permission dialog box lists all the people who are allowed to access the range, and can bypass the password. At first, this list is empty.

Click Add to enter your first user .

The "Select Users or Groups" dialog box appears (Figure 22-17).

Figure 22-17. You can safely ignore all the buttons in this dialog box. All you need to do to add a user is enter the user name in the "Enter the object names to select" box. In this example, the user billjones (who logs into the domain Sales) is being added.

In the big text box, type in the user name .

Excel needs to know two things: where the user logs in (on the local computer, via a network server, or Windows domain), and who the user is (the user name). To fill in a user name correctly, you need to supply both pieces of information, separated by a backslash (\). For example, if billjones logs into the Sales domain, the full user name is Sales\billjones .

Tip: Forgot your user name? One easy way to get your full user name is to pay attention when you log onto your computer. You'll find all the details in the Windows login box. Or, if in doubt, talk to your friendly neighborhood network administrator.

Instead of using a user name, you can use a group name to save time and avoid headaches . The box in Section 22.3.4 explains how this works.

TIMESAVING TIP
Put Your Users in Groups

It's easy to get in over your head when giving permissions to specific users. Before you know it, a workbook that you initially planned to share with one person is being passed around an entire company, and you're spending every lunch hour feverishly editing the list of allowed users.

Skilled network administrators use groups to simplify this kind of task. For example, billjones and everyone else on his team might be assigned to the same SalesEmployees group. That way, you can configure your Excel workbook so it allows the entire SalesEmployees group, rather than each person. This approach is quicker to set up and easier to maintain. Best of all, when new hires are placed in the SalesEmployees group, they automatically get access to your workbook. And if Bill goes on a bender and the company gets rid of him, the network administrator can pull him out of the SalesEmployees group. He won't have access to your workbook any longer, and you don't need to waste a second changing your protection settings.

Click OK to add the user .

If you've made a mistakefor example, typed a user that doesn't seem to exist or isn't defined on the computer or domain you pickedyou'll wind up at the Name Not Found dialog box, where you can try and correct the user name.

If you've entered a valid user range, Excel adds it to the list of allowed users, as shown in Figure 22-18.

Figure 22-18. When you add a new user to the list, that user automatically gets a checkmark in the Allow column, which indicates that he's allowed to edit the range without a password. You can also use the Deny column to explicitly lock out specific users, but that approach rarely makes sense because it introduces too many potential security holes.

You can now return to step 4 to add somebody else. When you're finished, click OK .

You're returned to the "Allow Users to Edit Ranges" dialog box.
Click OK to get back to your worksheet .

Remember, you need to choose Review Changes Protect Sheet to protect the worksheet in order for your settings to take effect.

Tip: If you want to get a full breakdown that lists all your protected ranges, their passwords, and the people who have access to them, choose the "Paste permissions information into a new workbook" setting before you click OK. Excel creates a new workbook in a separate window, and copies this information into that workbook.

Once the worksheet is protected, it behaves a little differently. Now, the allowed users don't have any idea that they're typing in a restricted region. For example, if billjones starts editing one of the protected cells, Excel quietly checks the user account, sees that it matches your list, and lets him type away without any disruption. On the other hand, if someone else tries to make a change to one of the restricted cells, Excel pops up a dialog box asking for the password, just as it did before (as shown in Figure 22-16).