Lock Cells In Excel
Excel’s data validation tools help make sure funky data doesn’t end up in your worksheet. 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 lock cells in excel 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 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 you protect the worksheet. If you don’t protect it (and every worksheet begins its life without protection), Excel doesn’t use these settings at all. In other words, you need to take two steps to build a bulletproof worksheet. First, you need to specify which cells you want locked and hidden, and then you finish up by protecting the entire 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.)
Protecting a Worksheet
Here are the steps you need to follow to protect your worksheet:
1. First, unlock all the cells into which you want people to type information. You can do this one cell at a time, or you can select an entire range of cells. Once you make your selection, right-click it, and then choose Format Cells. The Format Cells window appears.
2. 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 person using the workbook to see.
3. 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 finish unlocking and hiding to your heart’s content, it’s time to protect the sheet.
4. Select Review→Changes→Protect Sheet (or just right-click the worksheet tab and choose Protect Sheet).
The Protect Sheet window appears.
5. Make sure the checkbox labeled “Protect worksheet and contents of locked cells” is checked.
In addition to protecting the contents of unlocked cells, the Protect Sheet window lets you toggle on or off a list of Excel actions that you want to let people using your worksheet perform, as described in the next step.
6. From the “Allow all users of this worksheet to” list, turn on the things you want people to be able to do. Excel’s standard approach is to restrict everything except 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.
• 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 check boxes 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 check boxes 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 page 80 for more information about hyperlinks.
• Delete columns and Delete rows. Turn on these check boxes to bestow the ability to remove columns or rows. Use this setting at your peril, because it lets people decimate your worksheet—for 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 Auto Filter. Turn on this checkbox to let people use filtering on any tables in the worksheet. See Chapter 14 for more about tables.
• Use PivotTable reports. Turn on this checkbox to let people manipulate any pivot tables in your worksheet. See Chapter 26 for more on 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, charts, or slicers.
• Edit scenarios. Turn on this checkbox to let people edit or delete what-if scenarios.
7. If you want to stop other people from unprotecting the worksheet, specify a password in the “Password to unprotect sheet” text box.
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 you’re just using protection to prevent people from making casual mistakes. But 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.
8. 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 (and explaining that you can unlock the worksheet by selecting Review→Changes→Unprotect
Protected worksheets have a nice feature: You can quickly find all the unlocked cells. Just press 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 turning off the “Select locked cells” setting in the Protect Sheet window.