Forum Discussion

Jon Layton's avatar
Jon Layton
Copper Contributor
Sep 20, 2018

View to show which cells are locked/unlocked

Is there a way to easily view which cells are locked / unlocked without having to go into each one and checking its' format to see if the "Locked Cell" box is ticked? This would be really handy if y...
excel
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Sep 20, 2018

Hi Jon,

 

You can set a conditional formatting rule and apply it to all worksheet to visualize whether a cell is locked or unlocked so that the unlocked cell can be highlighted in a specific format.

 

To do that:

  • Select the triangle in the left-hand corner of the worksheet
  • Go to Home >> Styles >> Conditional Formatting >> New Rule
  • In the New Formatting Rule dialog box, select Use a formula to determine which cells to format
  • Insert this formula into the formula box:
=CELL("protect",A1)=0
  • Press the Format button to choose the format
  • Hit OK

 

 

Hope that helps

  • Murad1820's avatar
    Murad1820
    Copper Contributor
    Jul 14, 2020

    It works nicely. Could you please explain how that works?

  • Jon Layton's avatar
    Jon Layton
    Copper Contributor
    Sep 21, 2018

    Haytham,

    Thank you, I had thought of doing it this way and it will work for my application, however I was looking for a view that would not affect my print (although for my spreadsheet I have picked a very light fill colour).

    Grateful for your support.

    Cheers

    • ralphmorgan's avatar
      ralphmorgan
      Copper Contributor
      Jul 15, 2020

      To be able to view the locked cells using this method without affecting the normal formatting, I simply copied the entire sheet to a new tab and did the formatting to highlight the protected cells only in that tab.

    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Sep 25, 2018

      Hi Jon,

       

      This add-in seems to be working in a different way I did not expect!
      It's can only detect the unlocked cells when they contain formulas!
      So if a cell is unlocked and contain a formula, it will be detected by the analysis in addition to all its unlocked precedent cells.

      This is shown in the below screenshot.


    • Haytham Amairah's avatar
      Haytham Amairah
      Silver Contributor
      Sep 21, 2018

      Hi Jon,

       

      You have many options in the Format Cells dialog box.

      For example, you can use the Border or the Font color instead of the Fill color to make the effect on your existing format as less as possible.

       

      Also, you can detect the unlocked cells in your workbook by using the Workbook Analysis which is one of the tools available in the https://support.office.com/en-us/article/Turn-on-the-Inquire-add-in-6BC668E2-F3C6-4729-8CE1-75EA20AA9D90.

       

      Please note that the Inquire Add-in is only available in the Excel 2013 or later (Professional Plus Edition).

       

      But the problem with this add-in is that it does not work correctly as I have some unlocked cells in my worksheet but they are not detected by the analysis as you see in the screenshot below.

       

      I don't know what is the problem!

      However, I sent feedback to Microsoft about this issue.

       

      Regards

Resources