• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Data Validation With Dependent Lists

You are here: Home / Excel Formulas / Excel Data Validation With Dependent Lists
Excel Data Validation With Dependent Lists
February 8, 2012 by Mynda Treacy

Using Data Validation to restrict what gets entered in a cell or range of cells is great for standardising your workbooks.

But what if you want a second data validation list to only show values that are specific to the first list, like the one below?

dependent data validation lists

Well, that’s exactly what Jackie emailed me about the other day, and here's how you do it.

How to set up Dependent Data Validation Lists

First of all enter the data for your lists. These are mine:

dependent drop down lists

Now, give your primary list a named range.

To insert a Named Range:

  1. Highlight the range of cells containing your list, excluding the header.
  2. Up in the name box (the name box is highlighted by the orange box in the image below) type in the name you want to use (with no spaces) and press ENTER. As you can see, mine is called dv_country.

dependent drop down lists

Now give your secondary lists named ranges too.

Here’s the trick: you must use the data from your primary list for your secondary list names.

So, my secondary list for the USA states is called ‘usa’, Australia’s secondary list is called ‘australia’ and the UK list is called ‘uk’ as you can see in the image below.

dependent data validation

Now you’re ready to set up your data validation.

Setup Data Validation

  1. Choose the cells you want validated using your first list. Mine are A4:A6.
  2. On the Data tab of the ribbon > Data Validation > Data Validation
  3. Choose ‘List’ from the ‘Allow’ field
  4. Enter the named range for your primary list in the Source field
  5. Press OK

dependent data validation

Setup Dependent Data Validation

  1. Select the cells you want validated. Mine are B4:B6.
  2. On the Data tab of the ribbon > Data Validation > Data Validation
  3. Choose ‘List’ from the ‘Allow’ field
  4. In the source field enter an INDIRECT formula that references the first cell containing your primary data validation. Mine is A4 therefore my formula is =INDIRECT(A4)
  5. Press OK

dependent data validation

Bob’s your Uncle (as we used to say when I was about 12).

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

More on Named Ranges.

More on the INDIRECT Function.

Excel Data Validation With Dependent Lists

More Data Validation Posts

select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Searchable Drop Down List in Excel

Searchable Drop Down List in Excel

This searchable drop down list in Excel includes an option for "All" and ignores duplicates. No VBA and no formulas. You won’t believe how easy it is.
selecting multiple items in data validation list

Populating Multiple Cells from Single Data Validation (Drop Down) List

Using a little VBA we can use a single data validation list to select multiple items and populate multiple cells
Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List

Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points
Excel Custom Data Validation

Excel Custom Data Validation to Limit Entries

Excel Custom Data Validation enables you to limit the value or number of entries in a range of cells.
Excel Combo Box KO’s Data Validation

Excel Combo Box KO’s Data Validation

Use an Excel Combo Box as an alternative to Data Validation Lists
Excel Tables as Source for Data Validation Lists

Excel Tables as Source for Data Validation Lists

Excel Factor 20 Custom Number Format Disguise

Excel Factor 20 Custom Number Format Disguise

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 19 Dynamic Dependent Data Validation

excel drop down lists

Excel Drop Down Lists

Excel Drop Down Lists or Data Validation Lists as they're officially known, are a great tool speeding up data entry and ensuring data is entered correctly.

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.




Category: Excel FormulasTag: Data Validation
Previous Post:What If Analysis Using Excel ScenariosWhat If Analysis Using Excel Scenarios
Next Post:VLOOKUP Multiple CriteriaVLOOKUP Multiple Criteria

Reader Interactions

Comments

  1. Mahmoud Eliwa

    August 19, 2016 at 10:46 pm

    Thank you sharing this, I have here a question about same trick: what if I want to select multiple selection! any clue?
    Thanks
    Mahmoud

    Reply
    • Catalin Bombea

      August 20, 2016 at 3:17 am

      Hi Mahmoud,
      That is a different problem. By default, it’s impossible to select more than 1 item from a dropdown data validation list. Only with a code this can be done, here is an example for that: selecting-multiple-items-in-data-validation-list
      Cheers,
      Catalin

      Reply
  2. Chandregowda

    September 25, 2013 at 6:04 pm

    Hi Carlo,
    Thanks for explaining, it solves some part of my requirement.
    Is it possible to have only two columns as a data list say “Country” and “States” in a separate worksheet and under those columns we will have country names repeated in first column and states corresponding to that country in other. How can we use the Data Validation in that case?
    For Ex:
    A: Countries | B:States
    ————————
    USA | NY
    USA | CO
    USA | MN
    UK | LN
    UK | ESS
    UK | SOME

    Reply
    • Mynda Treacy

      September 26, 2013 at 2:37 pm

      Hi Chandregowda,

      With your data formatted like that the data validation list will show the countries repeated. The data validation list cannot exclude duplicates. You need to give it a list of unique values first.

      So your format will not work for a data validation list. You need to set it up the way it is explained in the tutorial above.

      Kind regards,

      Mynda.

      Reply
  3. jezryl

    February 15, 2013 at 6:18 am

    Hi Mynda,

    Is it possible to have several dependents?
    I want to use this to specify details.

    Here’s the example below.

    Primary Secondary Next Then
    Country States Village Street

    Thankyou.

    Reply
    • Carlo Estopia

      February 15, 2013 at 11:58 pm

      Hi Jezryl,

      Yes. You can! 🙂

      Just follow the structure in the example file.

      Continuing with our Example, We already have 3 Countries.
      If you want to add Villages, then in column C,
      add a data validation =INDIRECT(B4) if in row 4, B5 if in 5 etc.

      Now, here’s the tedious part. You have to name a range for each state in a country.
      For example, You should create name ranges for each of these
      states below — and in those ranges should consist of villages.

      NY	QLD	London
      CO	NSW	Essex
      MI	VIC	Middlesex
      UT	ACT	Kent
      AL	NT	Devon
      CT	SA	Somerset
      CA	WA	
      DC		
      

      For example, in ‘NY’ Range you’d have Village1, Village2 etc.
      So that when you have NY in B4 and you click the dropdown in
      C4, you’ll reference the ‘NY’ named range via B4 thus you’ll
      get the list of Village1, Village2 etc.

      It’s all about structure.

      Cheers.

      CarloE

      Reply
      • jezryl

        February 22, 2013 at 5:18 am

        What a great tricks. Thanks a lot.

        Reply
        • Mynda Treacy

          February 22, 2013 at 10:13 am

          You’re welcome, Jezryl 🙂

          Reply
  4. Rachael Hanna

    February 8, 2013 at 6:39 am

    Hi Mynda, I successfully used this example of “Excel Data Validation With Dependent Lists” but there were a couple of things I noticed.
    1. In the “Source” box you need to have an “=” Could you change the wording in your example to “=dv_county”.
    2. When I entered the “Indirect” function my version of Excel automatically used the absolute value of the cell I selected :-(..so when I copied the equation down the list it still referenced $A$4 .This is a quirk of Excel …. but thought I’d let you know. I love your site. It helps me a lot with curly excel issues and your explanations are very clear.

    Reply
    • Carlo Estopia

      February 10, 2013 at 10:16 pm

      Hi Rachael,

      I’m really sorry for not getting this immediately. I really thought that you’re just making
      a comment about the blog post but at second glance it seems you were in fact asking a question.

      On number 1, Yes you can change it; provided, you change the named range. You can go to the
      Formulas ribbon, Click Name Manager, Look for dv_country and double click, In the name box
      change it to dv_county; that is, if it’s what you mean by this one.
      Visit: Named Ranges Basics

      On number 2, In all versions, Indirect function is referencing a cell/address indirectly; hence, the name: ‘Indirect’.
      To explain it better, we define by example what is direct referencing. So, for example, if you’re
      in B1 and you want to write a simple formula to get A1’s value you simply write “=A1”.
      So this is direct referencing of a cell.
      A1- Dog
      B1 -Formula: =A1
      Result: Dog

      On the contrary, If we use “=INDIRECT(A1)”, we are not referencing
      A1 but what is the value in A1. For example, If A1 has a text value of “C1”, then what you will get is the
      value –indirectly– in C by referencing A1.
      A1 – C1
      C1 – Dog
      B1 -Formula: =INDIRECT(A1)
      Result: Dog (coming indirectly from C)

      Note: also that the argument of INDIRECT is not a range, but a text string called ref_text. In other words, this is really the essence
      of an indirect; that is, it will not change like the usual direct referencing of ranges. It is not a quirk in other words.

      Please visit these blogs by Mynda about INDIRECT with other functions.

      Cheers.

      CarloE

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.