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’.
Choose the “Use a formula to determine which cells to format” in the dialog box. Enter the following formula:
=CELL("protect", A1)=0Click 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 the 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.
#2: Choose Options
Click on the “Options” button under the “Find what” section.
#3: Click on the Format button
After clicking the Options button, click Format.
#4: Activate the Protection Tab
After clicking the Format button, the “Find Format” dialog will appear. Activate the 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)
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*”.
Finally, click “Find All” to show locked cells.
Note: We don’t recommend you select the entire Worksheet in this case. Showing locked cells using conditional formatting is a much faster way.
