Lesson#122: Cell formatting in a protected sheet

Excel doesn’t accept to format cells which are protected, if they aren’t allowed for users. To format unprotected or protected cells users must allow before protecting a sheet.

When you are protecting a sheet just see these options.

The third option is Format Cells and when you select this you will be able to format both protected and unprotected cells.

There is other ways to format locked or unlocked cells.

You can put a formula in Conditional Formatting before you protect a sheet. You have to select whole sheet or particular cell or range of cells.

Go to Conditional Formatting>New Rule>Use a formula to determine which cells to format.

Select a format there and paste the formula for formatting locked cells =CELL(“protect”,A1)=1

Here the A1 will be changed to most upper left cell among the selected cells to format.

We can use the formula 
=CELL(“protect”,A1)=0 in the same way to format unlocked cells.

Puspendu is the founder author of Excelabcd. He is a creative person, blogger and Excel-maniac guy.

Tagged with:

Leave a Reply

Your email address will not be published. Required fields are marked *