How To Use Conditional Formatting To Verify Selections Of A Drop Down List In Excel

In this tutorial we’ll see how we can use Conditional Formatting, using the ISBLANK, COUNTIF, and INDIRECT functions, to verify the accuracy of selections from a Data Validation drop down list in Excel.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have a small chart that has two columns: Continent and Country. I’ve created Data Validation drop down lists so I can choose one of three continents in column A and one of 49 countries in column B:

6816-1

Notice, if I choose a country that is within the selected continent, it formats as green, but if incorrect, it formats as yellow.

I’ve created 5 named ranges – one for each continent, one to list all the countries, and one to list the three continents:

6816-2

Here are the lists I’ve used to create those named ranges:

6816-3

In Conditional Formatting, I’ve used three different formulas – all used to format $B$2:$B$4:

=ISBLANK(B2)
=COUNTIF(INDIRECT(A2),B2)=1
=COUNTIF(INDIRECT(A2),B2)<>1

The first is formatted as normal
The second is formatted with a green fill
The third is formatted with a yellow fill

Here is the setup in Conditional Formatting for these three formulas:

6816-4

The key is the use of COUNTIF and INDIRECT in the formulas. Here is how it works:

Since I’ve named the range of each list of countries within a continent, the choice of a continent in column A is actually the name of that range. (Notice that I had to use the underscore instead of a space in the continents’ names – you can’t use spaces when creating named cells or ranges). The INDIRECT function “Returns the reference specified by a text string”. So, “INDIRECT(A2)” takes the text string, which is the name of the continent selected in column A and converts it into the range that is the named range that uses that continents name. That becomes the range for the COUNTIF function. So, if the count of that country in that range is 1, then the second Conditional Formatting rule is true, and it will fill it green. If the count is 0, or <>1, then it will fill it yellow since it does not appear on the list.

Watch the video if you need a more step-by-step explanation of the process.

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , ,

3 Responses to How To Use Conditional Formatting To Verify Selections Of A Drop Down List In Excel

  1. Zak July 15, 2016 at 5:19 am #

    Hi Michael,

    Can you tell me how i would go about only showing the countries in that continent in the second drop down list.
    saving the mistake of selecting the wrong one.

    Kind Regards,

    Zak

  2. nail grooming products September 23, 2016 at 3:14 am #

    There is visibly a bundle to know about this. I think you
    made certain good points in features also.

Leave a Reply