Excel 2010 – how to view all locked cells

If you’ve ever worked on a complicated Excel spreadsheet you’ve probably ended up with protected (locked) cells everywhere and eventually lost track of which are protected and which aren’t.

Here’s a quick was to detect and view the protected cells in your spreadsheet.

Please note: by default all cells in a spreadsheet are locked – if you haven’t unlocked any cells this process will correctly display everything as locked.

  1. Open your spreadsheet.
  2. If the spreadsheet is currently protected you will need to turn this off in the ‘Review’ tab
  3. Excel-DisplayProtected-1
  4. Now you need to select all cells, this can be done by pressing Ctrl and A on your keyboard or using the select all cells icon which is between the A and 1
  5. Excel-DisplayProtected-2
  6. Now on the ‘Home’ ribbon menu click on ‘Conditional Formatting’ and select ‘New Rule’
  7. Excel-DisplayProtected-3
  8. From the list select ‘Use a formula to determine which cells to format’
  9. Under ‘Format values where this formula is true’ enter the following
  10. =CELL("protect", INDIRECT(ADDRESS(ROW(),COLUMN())))=1
  11. Excel-DisplayProtected-4
  12. Click on the ‘Format’ button
  13. Click on the ‘Fill’ tab
  14. Select the colour which will display for locked cells – e.g. red
  15. Excel-DisplayProtected-5
  16. Click ‘OK’ and ‘OK’ again to save the changes
  17. Excel-DisplayProtected-6
  18. To return the fields to their normal background click on the ‘Conditional Formatting’ button and then select ‘Clear Rules’ -> ‘Clear Rules from Entire Sheet’
  19. Excel-DisplayProtected-7

Reference: http://a32.me/2010/04/excel-locked-unlocked-cells/