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)

img2

We need to follow these steps:

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

img4

img5

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

img6

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

img7

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

img8

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

img9

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

img10

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

img11

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

img12

The following snapshot shows everything fine.

img13

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.

img14

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

img15

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



Example:


Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>