How to Highlight Locked or Unlocked Cells in Excel?

Excel's cell locking feature is essential for protecting sensitive data while allowing edits to other cells. However, identifying which cells are locked or unlocked can be challenging, especially in complex spreadsheets. This guide will walk you through several optimized methods to highlight locked and unlocked cells, improving your spreadsheet management efficiency.

Understanding Cell Locking in Excel

Highlight locked cells with Conditional Formatting

Highlight unlocked cells with Kutools for Excel

Understanding Cell Locking in Excel

Before highlighting cells, it's important to understand Excel's locking mechanism:

• By default, all cells are locked (though this has no effect until sheet protection is applied)
• Locking only takes effect after protecting the worksheet (Review tab > Protect Sheet)
• Locked cells prevent editing when the sheet is protected
• Unlocked cells remain editable even when the sheet is protected

Highlight locked cells with Conditional Formatting

You can use conditional formatting with a special formula to highlight locked cells, making it easier to visually distinguish protected areas in your worksheet before applying sheet protection.

1. Select a range you want to highlight the locked cells, then click Home > Conditional Formatting > New Rule. See screenshot:
   
2. In the New Formatting Rule dialog, configure the following settings:
   • Select Use a formula to determine which cells to format in the Select a Rule Type list box;
   • Then type this formula =CELL("protect",A1) into Format values where this formula is true text box;(💡 Note: A1 is the the top-left cell of your selected range.)
   • And then, click Format button.
     
3. In the Format Cells dialog, click Fill tab, and select a color to highlight the locked cells. See screenshot:
   
4. Click OK > OK. Then you can see the locked cells are highlighted by the color you defined. See screenshot:
   

Highlight unlocked cells with Kutools for Excel

Tired of manually checking which cells are editable in your worksheet? Let Kutools for Excel do the work for you! With just one click, you can instantly highlight all unlocked (editable) cells—no complex formulas, no coding, no guesswork.

After installing Kutools for Excel, please do as this:

1. Go to the Kutools Plus tab, and click Design View.
   
2. Then, a Kutools Design tab will displayed, click Highlight Unlocked in the ribbon. Kutools will immediately highlight all unlocked cells in the worksheet with a predefined color.
   

🔚 Conclusion

Highlighting locked or unlocked cells helps you better manage input fields, protect formulas, and streamline worksheet security. Whether you're preparing a protected form or auditing your worksheet structure, these methods can save time and reduce errors. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.

Related Articles:

• Highlight odd or even numbers in Excel
• Highlight whole numbers in Excel
• Highlight all error cells in Excel
• Highlight all named ranges in Excel