By David Ringstrom, CPA
It can be frustrating when you're working in an unfamiliar worksheet and “the cell or chart you are trying to change is protected” prompt appears on-screen. By default, you can't easily tell at a glance if a cell is locked or not, but in this article, I'll show you how to add a visual aid. As an added bonus, this technique also makes it far easier for you to lock and unlock worksheet cells.
Before we get to the visual aid, let's first take a look at worksheet cell protection, as shown in Figure 1. By default, every cell on a worksheet is set to locked, which means if you then protect the worksheet itself, no cells can be changed. Thus, you must first unlock any cells that you wish for someone to be able to change. To do so:
- Select one or more cells you wish to unprotect.
- Press Ctrl-1 to display the Format Cells dialog box.
- Clear the check mark next to Locked on the Protection tab and then click OK.
Figure 1: The Format Cells dialog box allows you to lock and unlock cells.
Once you've adjusted the protection level for individual worksheet cells, you can now protect the worksheet:
- Excel 2007 and later: On the Review tab, click Protect Sheet, optionally enter a password, and then click OK.
- Excel 2003 and earlier: Choose Tools, Protection, and then Protect Sheet. Optionally enter a password, and then click OK.
As shown in Figure 2, to unprotect a sheet, carry out the same steps as above, but this time the Protect Sheet command will read Unprotect Sheet, and then enter the password if necessary.
Figure 2: Cell protection doesn't take effect until you protect a worksheet.
With that background out of the way, let's get to the trick. In any version of Excel, we can add an icon that will let us determine if a cell is locked and also enable us to toggle the locked setting on or off. In Excel 2007 we'll use the Quick Access Toolbar, which typically resides on the top of your Excel screen:
- As shown in Figure 3, click the arrow at the end of the toolbar, choose More Commands, and then select the Home tab from the drop-down menu.
- Scroll the list until you find Lock Cell, and then double-click on that command to add it to your Quick Access toolbar.
- Click OK to close the Excel Options dialog box.
Figure 3: Excel 2007 and later allows you to add a Lock Cell shortcut to the Quick Access Toolbar.
As shown in Figure 4, if a cell is locked, the Lock Cell icon will have a colored background, while unlocked cells will have a gray background. Every icon on the Quick Access Toolbar is assigned a numeric shortcut, so you can now lock or unlock cells without using your mouse. To do so, select one or more cells, press the Alt key, and then the number that corresponds with the Lock Cells icon.
Figure 4: Locked cells will have a colored background; unlocked cells have no background.
In Excel 2003, choose Tools, Customize, Commands, and then click on the Format category. Scroll down the list on the right until you find Lock Cell, and then drag the command onto a toolbar or into the gray space to the right of the Help command. If a cell is locked, the icon will have a blue square around it or just a gray background if a cell is unlocked. You'll have to use your mouse to toggle the icon on or off, but this does give you an easier way to lock or unlock cells without going through the Format Cells dialog box.
A previous version of this article first appeared on www.accountingweb.com .
About the author:
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
