Since last time I’ve become kind of Excel guru ), I want to start publishing some of my tips discovered.
In this post I will explain how to quickly identify all Locked and Unlocked cells on the sheet using Office 2007. This is very useful for developing complex sheets.
Here is real life sheet scenario, Excel form filled by staff to gather some information. Everything is standardized and locked to maintain data integrity.
While developing such a form it is very easy to make a mistake and protect the wrong cell and get complains from users.
I found the way to check all cells for Locked flag using “Conditional formatting”.
Here is the recipe
1. First select whole sheet by clicking on left upper corner
2. On the Home ribbon click “Conditional Formatting” button and click “New Rule”
3. In the window appeared choose “Use a formula to determine which cells to format”
Paste this formula:
=CELL("protect", INDIRECT(ADDRESS(ROW(),COLUMN())))=1
Choose format for to identify locked cells, I chose red background.
Voila! Below is same form with protected cells highlighted with red background. It very easy to check now.
Afterwards this rule should be removed before going into production.
excellent trick. shame excel does not have a button that shows them like this. makes you wonder what they do all day in redmond.
Thanks – this is really helpful. It also works in Excel 2003 but you must choose “Formula Is” from the dropdown, then paste the formula given above into the option box to the right.
Awesome – Great work!!