Highlight Protected Cells in an Excel Worksheet

Here is a quick way to see which cells are protected in an Excel worksheet. This solution uses conditional formatting to do the highlighting. It uses the CELL function to get the protected property of the cell.

For this example, I have created a sample spreadsheet. The cells containing the “Please modify” text are the ones I unchecked the “protect” checkbox in the Format Cells dialog.

Highlight the range you would like to format. Go into conditional formatting, and select “New Rule”.

1. Select “Use a formula to determine which cells to format”

2. Enter the following formula: =CELL("protect",INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE))

3. Click the “Format” button and choose how you would like to format protected cells. I chose a red fill and a bold font for my example.

Once applied, you can quickly see which cells are protected.

Breakdown of the formula

=CELL(“protect”,) --> Determines if the cell is protected or not.

= INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE) –> Specifies the range of the current row and column.