Detect and view locked and unlocked cells in Excel
While creating complex Excel based data input forms many cells needs to be protected from user input, like input labels, table titles, etc... When giving form to production I must be sure that all cells are properly locked. But, how to be sure if this having 1000+ cells ?
I discovered simple and visual technique to quickly detect and show locked and unlocked cells using Excel 2003 or 2007/2010.
Here is real life sheet scenario, 3 pages big Excel form used to capture data input and gather information. Everything must be standardized and locked to maintain data integrity, since form is used in automatic data extraction.
While developing such a form it is very easy to make a mistake and protect the wrong cell or leave cell unprotected. Usually this leads to complains from users or data in wrong cells.
I found the way to check all cells for Locked flag using "Conditional formatting".
Recipe for excel 2007 how to view locked and unlocked cells visually
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
Depedning on your windows regional settings, you may require to change "," to ";" in this formula.
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.
After all cells have been fixed, easy to remove this rule using same method.
If you put "=0" at the end of the formula it will show not locked cells, which is very useful to highlight "entry" fields for users, a common and recommended thing to do when protecting sheets.
Another method for detecting locked and unlocked cells using Find command
You can use the Find for that.
Go to menu Edit->Find, open the Options, open the Format selection and on the protection tab mark the option locked. With that, you can click on Find All and it will list all the locked cells on the worksheet.
The cells are locked by default, so the list may be really long.
English 
Русский Categories
- book
- cloud
- delphi
- dev
- excel
- food
- foto
- fun
- I|T
- linux
- misc
- mobile
- php
- portfolio
- software
- todo
- travel
- tricks
- Uncategorized
- web
- Работа
Recent Posts
- Discover PHP bottlenecks with Xdebug or why my site is so slow
- Reading directory list – new PHP5 way
- Tinydeal – blacklisted, do not buy here
- Dynamicly load Google Maps v3
- How to connect to rare databases in PHP (informix, oracle, db2)
- PHP, HTML, CSS, JavaScript editor (IDE) – Codelobster PHP Edition
- NoSQL Parody :)
- Ubuntu 12.04 64bit – Office 2010 with Wine [SOLVED]
- Making any form AJAX – The easy way
- Settings link in WordPress plugin
Archives
- May 2013
- April 2013
- March 2013
- February 2013
- January 2013
- December 2012
- November 2012
- October 2012
- September 2012
- July 2012
- June 2012
- May 2012
- April 2012
- March 2012
- February 2012
- January 2012
- December 2011
- November 2011
- October 2011
- September 2011
- August 2011
- June 2011
- May 2011
- April 2011
- March 2011
- February 2011
- January 2011
- December 2010
- September 2010
- August 2010
- July 2010
- June 2010
- April 2010
- March 2010
- February 2010
- January 2010
- December 2009
- November 2009
- October 2009
June 11th, 2010 - 17:53
excellent trick. shame excel does not have a button that shows them like this. makes you wonder what they do all day in redmond.
July 6th, 2010 - 10:43
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.
September 3rd, 2010 - 01:29
Awesome – Great work!!
November 3rd, 2010 - 06:54
awesome
December 3rd, 2010 - 12:06
Excellent Stuff !!!!
June 6th, 2011 - 14:00
what a boon! Thank you so much; extremely helpful
August 10th, 2011 - 05:03
This also works with the formula =CELL(“Protect”, A1)=1 (much easier to type!). You can choose the whole sheet or just a block of cells. Must ‘Unprotect Sheet’ for the formatting to work.
September 20th, 2011 - 00:36
This is sooooo helpful!!! THank you!!
December 8th, 2011 - 21:56
thanks for that! very helpful!
December 15th, 2011 - 21:15
Great tip! Huge time saver!
December 21st, 2011 - 19:00
Excellent!! saved me lots of trouble, here is the Excel Spanish translation of the formula, just in case someone else need it.
=CELDA(“proteger”; INDIRECTO(DIRECCION(FILA();COLUMNA())))=1
February 7th, 2012 - 08:53
you’re a legend
March 10th, 2012 - 09:09
Great work… Thanks for the trick
March 22nd, 2012 - 20:42
If you equal 0 (=0 at the end) instead of 1, it’ll find the opposite; unlocked cells. This is very useful to highlight “entry” fields for users, a common and recommended thing to do when protecting sheets.
April 10th, 2012 - 03:15
Awesome! Thanks
April 25th, 2012 - 11:05
I keep MS Excel 2003 just for the its toolbar showing elegantly which cells are locked and which are not. When I get frustrated over the missing functionality in newer versions, I just go back to my old version. That is handy when working with large and complex spreadsheet. I do however appreciate this “workaround”.
April 25th, 2012 - 18:36
Thank you so much; I have a complex spreadsheet with multiple users and despite repeated eductaion, people are still cut/paste into the sheet, bringing with their data the formats (including locked cells). this impacts other users as they can no longer sort/copy the columns and data they need.
this will “highlight” their entry error (and hopefully shame them into doing it correctly!)…
May 4th, 2012 - 14:04
Worked perfectly! Just what I needed!
June 18th, 2012 - 17:57
Our accounting software allows running reports to an 2007 Excel file.
When I do this the Windows Unhide, Ruler, Message Bar, Synch Scroll and several others are blocked. They prevented these features. I want to run macros but cannot, even after openning personal xlsb. They blocked this.
I’ve saved book1.xls as a Trans1.xlsm that didn’t work
I’ve openned Trans1 copied the cells containing data only, pasted this in a new Excel file as Paste Values. That didn’t work either.
How can I get windows Unhide back?
July 22nd, 2012 - 15:14
Great formula, thanks!!!
One little tweek though: I kept getting errors when I pasted your formula and had to change the commas to semicolums. So if other people are also getting errors when trying this formula out, try to paste this one:
=CELL(“protect”; INDIRECT(ADDRESS(ROW();COLUMN())))=1
July 23rd, 2012 - 11:03
Thanks for a comment.
Actually it depends on your windows regional settings. I added a note!
July 30th, 2012 - 22:51
Excellent trick, thanks a million
August 2nd, 2012 - 11:21
excellent – thank you
September 4th, 2012 - 16:25
Just what I needed. Thank you so much.
September 10th, 2012 - 14:09
Thanks – saved me ages!
September 21st, 2012 - 19:37
That is excellent and works perfectly. Thank you for taking the time to share this, it is much appreciated.
September 25th, 2012 - 18:18
This truly is great. I am planning to add this to one of my addins so I will have it available all the time. Of course though, the macro will have to check if the sheet is already protected and prompt to unprotect it first.
November 30th, 2012 - 12:00
Thank you! Thank you! Thank you!
January 19th, 2013 - 18:39
Brilliant !!. One question: Why is a space needed before the INDIRECT function? (without this space it just doesn’t work)
February 7th, 2013 - 01:39
Thank you! This really helped me!
February 27th, 2013 - 00:34
You saved me hours of work. Thanks.
February 27th, 2013 - 14:01
Great trick! Thank you very much…. You saved me lots of hours!
March 28th, 2013 - 06:14
Just what I was looking for – thank you!
April 4th, 2013 - 01:44
Thank you, we needed it.
April 28th, 2013 - 08:26
Way cool! Some very valid points! I appreciate you penning this post plus the rest of the site is really good.