Quickly find protected cells in Excel

Use conditional formatting to format protected or unprotected cells.

Once you complete a spreadsheet you plan to distribute, you probably protect cells that you don't want altered. Doing so keeps you in control. However, later when you make changes to the sheet, you might have trouble remembering which cells are protected and which aren't. Fortunately, using conditional formatting, you can quickly identify protected (and thereby non-protected) cells.

To identify protected cells in Excel 2003 do the following in an unprotected sheet:

  1. Select the range you want to check—usually that's your entire spreadsheet area. You can select the entire sheet by press [Ctrl]+A or clicking the Sheet Selector (the gray intersecting cell between the column and row headers).
  2. From the Format menu, choose Conditional Formatting.
  3. From the Condition 1 dropdown, choose Formula Is.
  4. Enter the following formula: =CELL("Protect",A1)=1. To format unprotected cells, use the formula =CELL("Protect",A1)=0. (To avoid curly quotes, paste as text only)
  5. Click Format and apply an appropriate format, say, a back color.
  6. Click OK. The following figure shows conditional formatting for displaying an orange back color.

Click Ok. The protected cells—that's almost all of the cells in this sheet—are orange.

In Excel 2007 and 2010, select the entire unprotected sheet and do the following:

  1. Click Home | Conditional Formatting | New Rule.
  2. From the Select A Rule Type list, select Use A Formula To Determine Which Cells To Format.
  3. Repeat steps 4 through 6 from above.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox