Here’s How to Check the Lock Status of Excel Cells

Since Excel allows you to lock only specific cells in Worksheet, you can still work in the rest of the area. But, how do you know which cell is locked within such thousands of grids?

Well, you can simply double-click on the cell to find out. When you attempt to edit a locked cell, you’ll get an error stating, “The cell or chart you’re trying to change is on a protected sheet.” 

But, personally, I wouldn’t suggest you check like this as it isn’t much of a help in massive sheets. 

Rather, we have the best way to see the lock status of a cell which is by using the Find and Replace and Conditional Formatting. You could also highlight the locked cells to avoid making changes. 

Using Find and Replace

Excel’s Find and Replace tool is the quickest way to locate any values within the sheet based on criteria. So, let us use the Find command to pinpoint all locked cells of your spreadsheet at once. You can also select those locked cells afterward. 

1. On your sheet, enter Ctrl + F for the Find and Replace Menu.
2. On Find What, click on Format.
   
3. Now, in Find Format, go to the Protection Tab. Check the box for Locked and hit OK. 
   
4. Click Find All. You’ll see lists of Locked Cells. Press Ctrl + A if you want to select those cells.
   

Use Conditional Formatting

While you can easily check the lock status of an Excel cell with the Find and Replace feature, you might lose track of that cell with just a selection.

So, instead, use Conditional Formatting to verify and highlight the locked cells with different colors. Highlighting the cells will help you separate them as a locked cell until you finish the task.

Here, we will use the IF and CELL functions nested together to test whether the cell is locked or not. Keep in mind that the formula will highlight cells only when the condition is TRUE.

1. Enter Ctrl + A to select all on your Sheet.
2. From the Home Tab, click on Conditional Formatting. Then, choose New Rule.
   
3. Go to Use a formula to determine which cells to format. Type =IF(CELL("protect", A1)=1, TRUE, FALSE) in the cell.
   
4. Click Format.
   
5. Go to the Fill Tab and click on Colour to highlight. Hit OK.
   
6. Again, click OK.
   
7. You’ll see that all the locked cells are highlighted with Yellow color.