Excel Tip: Determining Worksheet Cell Protection at a Glance

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.
Read more articles by David Ringstrom.
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 also presents monthly Excel webcasts for AccountingWEB partner CPE Link.


You may like these other stories...

How are you planning? What tools do you use (or fail to use) for forecasting? PlanGuru is a business budgeting, forecasting, and performance review software company based in White Plains, N.Y. AccountingWEB recently spoke...
Event Date: October 30, 2014, 2 pm ETMany Excel users have a love-hate relationship with workbook links. For the uninitiated, workbook links allow you to connect one Microsoft Excel spreadsheet to other spreadsheets, Word...
Event Date: September 9, 2014, 2:00 pm ETIn this session we'll discuss the types of technologies and their uses in a small accounting firm office. Included will be:The networked office: connecting everything together for...

Already a member? log in here.

Upcoming CPE Webinars

Aug 21
Meet budgets and client expectations using project management skills geared toward the unique challenges faced by CPAs. Kristen Rampe will share how knowing the keys to structuring and executing a successful project can make the difference between success and repeated failures.
Aug 26
This webcast will include discussions of recently issued, commonly-applicable Accounting Standards Updates for non-public, non-governmental entities.
Aug 28
Excel spreadsheets are often akin to the American Wild West, where users can input anything they want into any worksheet cell. Excel's Data Validation feature allows you to restrict user inputs to selected choices, but there are many nuances to the feature that often trip users up.
Sep 9
In this session we'll discuss the types of technologies and their uses in a small accounting firm office.