How to highlight unprotected cells in Excel

In this article, we will learn How to highlight unprotected cells in Excel.

Scenario:

Excel gives you the feature of protecting certain cells in unprotected sheets. Excel locks those cells and doesn't let any unidentified user edit values. As a user who doesn't have access to protected cells faces problems like using the sheet not knowing which cells are editable. So this article guides you how to protect or unprotect cells. And how to highlight cells which are unprotected. It would be difficult to see various trends just for examining your Excel worksheet. Let's understand below how conditional formatting catches unprotected cells.

How to solve the problem?

For this, we will be using the Conditional Formatting and CELL function in Excel. Cell function in Excel stores all the data of a referenced cell and returns the info_type of the cell. Here type of information we are looking for is "PROTECT". We will be using the generic formula under the conditional formatting option named Use a formula to determine which cells to format. Generic formula and example is explained below. 

Generic formula:

=CELL("PROTECT",cell_ref)=0

CELL function returns 1 if the cell is locked or 0 if unlocked

cell_ref : reference cell to check

=0 : value to check with

 

Example :

All of these might be confusing to understand. Let's understand this formula using it with one example shown below. Here we have some values and we need to first protect some cells and see the difference between protected ones and unprotected ones. 

To protect cells in excel, we select the cells and right click on the any selected cell -> select Format cell as shown below.

A dialog box appears, select Protection and tick the Locked box if you want to lock or untick to unlock as shown below.

As you can see, we know how to protect cells or how to unprotect cells in excel. It's difficult to see various cells having protected and unprotected cells. Now we wanted to highlight cells which are unprotected. 

Select the cell. Go to Home -> Conditional formatting -> New Rule

A dialog box appears in front select Use a formula to determine which cells to format -> input the below formula under the Format values where this formula is True:

Use the formula:

=CELL("PROTECT",B2)=0

Explanation:

  1. CELL function returns the type of cell info you require. Here "PROTECT" is info type.
  2. B2 is cell to check.
  3. The function returns 1 if cell is protected or 0, if not.
  4. =0 matches the return value to return True or False.

As you can see from the above snapshot, we obtained all the values highlighted with green color for the unprotected cells. Now copy the formula format for other values using the shortcut Ctrl + D or dragging down cell from the right bottom corner.

As you can see the formula changes the format of the cells only where the cell is unprotected. Now you can get the whole sheet differentiated on the basis of protected and unprotected cells. Use =1 in-place of =0 if you want to change the format of protected ones. Conditional Formatting provides a way to visualize data and make worksheets easier to understand.

Here are all the observational notes regarding using the formula. 

Notes:

  1. Conditional formatting allows you to apply the formatting basis on the cell values such as colours, icons and data bars.
  2. The formula works for text and numbers both.
  3. Check the formula in a sample cell first. As it's easy to use a formula in a cell rather than hard coding in conditional formatting formula box.
  4. Select the info_type argument from the drop down list, you will see while using the formula.

Hope you understood How to highlight unprotected cells in Excel. Explore more articles on Excel highlighting cells with formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

 

Related Articles:

How to Protect an Excel Sheet : do not let the user make any change to some certain sheet. Protect or lock sheets in Excel under Review section explained here.

Protecting Cells Containing Formulas in Protected Sheet : To protect the formulas from the end-users, Lock the cells using the Format cell option explained here.

Protecting Workbooks with a Digital Signature : Protect your workbook with your digital signature. Here's how.

Protect and Unprotect Excel Workbooks : To protect excel workbooks with a password with a read-only option or to put opening restrictions use the method explained here.

How to Highlight cells that contain specific text in Excel : Highlight cells based on the formula to find the specific text value within the cell in Excel. 

IF function and Conditional formatting in Excel : How to use IF condition in conditional formatting with formula in excel. 

Conditional Formatting using VBA in Microsoft Excel : Highlight cells in the VBA based on the code in Excel.

Popular Articles :

50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube