Lesson#122: Cell formatting in a protected sheet in Excel

Excel doesn’t accept formatting cells that are protected if they aren’t allowed for users. To format unprotected or protected cells users must allow them 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 are 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 a whole sheet or a 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 cells among the selected cells to format.

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