How to Highlight cells in Excel
You can also use the Conditional Formatting trick that will highlight the cell whenever there is a mismatch.
Below are the steps to highlight mismatches in the dependent drop-down lists:
Step 1: Select the cell that has the dependent drop-down lists.
Step 2: Click Home> Select Conditional Formatting> New Rule.
Step 3: In the New Formatting Rule Dialog box, Select ‘use a formula to determine which cells to format.
Step 4: In the Formula Field, Enter the Following formula: =ISERROE(VLOOKUP(E14, INDEX($A$2:$B$6,, MATCH(D3,$E$7:$F$11)),1,0)) and set the Format
Step 5: Click OK.
The above Formula uses the VLOOKUP Function to check whether the item in the dependent drop-down list is one from the main category or not. If it isn’t from the category the formula returns the Error. This is used by the ISERROR Function to return TRUE which tells conditional Formatting to highlight the cell.
How to Create a Dependent Drop Down List in Excel
Dependent Down Lists allow you to validate the data and make the data entry and data filtering very smooth and time-saving. Dependent Down Lists increase the chances of fewer errors in one’s worksheet. The prerequisites for learning Dependent Drop Down List are Create from Selection and Drop Down List.
Sometimes you may want to use more than one drop-down list in Excel, Such that the items available in the second Drop-down list are dependent on the selection made in the first drop-down list. These are called Dependent drop-down lists.
Contact Us