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...

There's thunder and lightning in the cloud these days. And the rumbling is all about security. Could peer-to-peer file transfers be the answer? As the old accounting adage goes, there's more than one way to skin a...
Saving Excel spreadsheets as PDF files has gotten incrementally easier over the years, but can still require more effort than necessary. When saving a document as a PDF, many users go through several mouse clicks on the File...
Imagine being able to file a business expense, such as a cab ride or a meal with a new client, in real time from an app on your smartphone. Here’s the best part: Imagine being reimbursed for that expense in 24 to 48...

Already a member? log in here.

Upcoming CPE Webinars

Nov 5Join CPA thought leader and peer reviewer Rob Cameron and learn ways to improve the outcome of your peer reviews while maximizing the value of your engagement workflow.
Nov 18In this session Excel expert David Ringstrom, CPA tackles what to do when bad things happen to good spreadsheets.
Nov 19How do you minimize redundant work and unnecessary steps to maximize the amount of work moving through your firm?
Nov 20Kristen Rampe will share how to uncover new opportunities with your clients by asking powerful questions.