Cascading Drop Down List Using Conditional Formatting in Microsoft Excel

In this article, we will learn about how to create cascading drop down list using conditional formatting. To create the cascading drop down list we will use Countif and Indirect functions.

Let us understand with an example:

We have a list of Fruits & Vegetables.

Now, we want to create cascading drop down list which will highlight the incorrect selection (highlight the value if it does not belong to Fruits or Vegetables)

We need to follow these steps:

We will use Define Name to create named ranges for Vegetables & Fruits

The next step is to create data validation list for Fruits & Vegetables say in range A13:A18. Refer below shown screenshot

In range B13:B18, create data validation list with formula referring to cell A13 using Indirect function.

Copy the cell B13 & use Paste Special & select Validation in range B14:B18

This will copy the validation in the selected cells.

After the data validation step is over, now we can use the formula that will highlight the wrong selection.

• Select the range B13:B18
• Click on Home tab & select Conditional Formatting
• Click on New Rule

• In the New Formatting Rule dialogue box, select “Use a formula to determine which cells to format”

• Enter the formula =COUNTIF(INDIRECT(A13),B13)=0

• Click on the Format & set the formatting, then click OK twice.

The following snapshot shows everything fine.

But as soon as we change the Fruits to Vegetables in cell A13, you will find the conditional formatting takes over & highlight the incorrect selection.

Similarly, if we change value in any drop down in below range say cell A18 then it will be highlighted.

In this way, we can track the wrong selection made in drop down list & change it to correct value.