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.
- 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).
- From the Format menu, choose Conditional Formatting.
- From the Condition 1 dropdown, choose Formula Is.
- To format unprotected cells, use the formula
- Click Format and apply an appropriate format, say, a back color.
- 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:
- Click Home | Conditional Formatting | New Rule.
- From the Select A Rule Type list, select Use A Formula To Determine Which Cells To Format.
- Repeat steps 4 through 6 from above.
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.