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. 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
  4. Now on the ‘Home’ ribbon menu click on ‘Conditional Formatting’ and select ‘New Rule’
  5. From the list select ‘Use a formula to determine which cells to format’
  6. Under ‘Format values where this formula is true’ enter the following
  7. =CELL("protect", INDIRECT(ADDRESS(ROW(),COLUMN())))=1
  8. Click on the ‘Format’ button
  9. Click on the ‘Fill’ tab
  10. Select the colour which will display for locked cells – e.g. red
  11. Click ‘OK’ and ‘OK’ again to save the changes
  12. To return the fields to their normal background click on the ‘Conditional Formatting’ button and then select ‘Clear Rules’ -> ‘Clear Rules from Entire Sheet’

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

Related Articles

One comment on “Excel 2010 – how to view all locked cells

  1. Instead of using INDIRECT(ADDRESS(ROW(),COLUMN())) we could give the reference of the 1st cell of the selection (eg A1 if we are selecting the entire sheet)

Leave a Reply

Your email address will not be published.