Show Protected Cells

This tutorial will explain how to show protected cells in Excel using conditional formatting, Microsoft Inquiry, or Search and Replace.

How to show protected cells in Excel using conditional formatting?

Steps to highlight protected cells in Excel:

  • Select the entire Workbook
  • Create a new conditional formatting rule
  • Define a formula: =CELL(“protect”, A1)=0

Explanation: It’s easy to identify protected cells using a new conditional formatting rule. First, select the entire Worksheet by clicking the green triangle in the upper-left corner.

Enter a new conditional formatting rule. Select the Home Tab, and under the Styles Group, choose ‘Conditional formatting’ and apply ‘New Rule’.

show protected cells using conditional formatting

In the dialog box, choose the “Use a formula to determine which cells to format” option. Enter the following formula:

=CELL("protect", A1)=0

Click the Format button to select the cell style, choose your preferred color, and press OK. Excel will show all protected cells!

How to identify and show all protected cells using Find Function

If you are unfamiliar with conditional formatting and the CELL function, here is a workaround with the Find Function in Excel.

#1: Open the Find and Replace dialog box

Select the range you want to check locked cells. Next, locate the Home tab on the ribbon. Then, under the Editing Group, use the “Find & Select” drop-down list and choose “Find”. If you prefer keyboard shortcuts, use the Ctrl+F command. The “Find and Replace” dialogue box will appear.

Open the Find and Replace dialog box

#2: Choose Options

Click on the “Options” button under the “Find what” section.

Click on the Options button

#3: Click on the Format button

After clicking the Options button, click Format.

Click on the format button

#4: Activate the Protection Tab

After clicking the Format button, the “Find Format” dialog will appear. Activate the Protection Tab.

show locked cells protection tab

#5: Check Locked cells

Make sure that the Locked box is checked. Note: Locking cells have no effect until you protect the Worksheet (Review Tab, Protect Group, Protect Sheet button)

locked cells

Click OK to close the Find Format dialog box.

#6: Use ‘Find All’ to show locked cells

Now the “Find and Replace” dialog box appears, and you should have to see the following text: “Preview*”.

use find all to show protected cells

Finally, click “Find All” to show locked cells.

show protected cells result

Note: We don’t recommend you select the entire Worksheet in this case. Showing locked cells using conditional formatting is a much faster way.

Istvan Vozar

Istvan is the co-founder of Visual Analytics Ltd. He writes blog posts and helps people to reach the top in Excel.