Hello Excellers and welcome back to another #FormulaFriday #Excel tip in my 2019 series. Today we are highlighting cells on an Excel worksheet that are locked. If you have created a worksheet a long time ago and can’t remember which cells are locked, or if you inherit a worksheet and need to do the same, then this tip is very useful. Let’s lock UnitCost cells first.
Follow the steps below to lock your cells.
- Select the full worksheet by Ctrl +A+A.
- Home Tab | Cells Group | Format | Format Cells | Protection.
- Deselect or remove the tick from the locked option.
- Select the cells you want to lock on your Excel worksheet.
- Repeat the process above by selecting the tick option.
- Review Tab | Protect Group | Protect Sheet
With locked cells set, we now can identify them using conditional formatting and using the CELL function. The CELL function returns information about a cell. This is exactly what we want.
The Syntax Of The CELL Function.
The CELL function syntax has the following arguments:
Info_type This is a required argument. A text value that specifies what type of cell information you want to return.
The following list shows the possible values of the Info_type argument and the corresponding results.
Reference This is an optional argument relating to the cell that you want information about. If omitted, the information specified in the Info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper-left cell of the range.
The Protect Info type is what we need to use. Excel returns the value 0 when the cell is not locked; otherwise, it returns 1 if the cell is locked.
Setting The Conditional Formatting.
- First, select the range to be formatted.
- Home Tab | Conditional Formatting | New Rule | Use A Formula To Determine Which Cells To Format. Now enter the following formula.
- Just select the type of formatting to be applied if the result of the formula is true.
That’s it. Easy as that. All locked are highlighted with the choice of formatting made. What an easy way to highlight locked cells. Do you have an alternative?. Please share in the comments below so other Excellers can learn.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Formula Friday Series Click The Link Below
So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month. Just click on the link and enter your email. No spam. Just Excel tips.
I am delighted to let you know I have teamed up with Excel Rescue. Have you got a problem with Excel?. Get Help with Excel – Fast! Excel Rescue is a done-for-you small tasks service for Microsoft Excel. Why don’t you check out how they can help you. Click on the link below for special offers.