• 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 Dependent Data Validation

You are here: Home / Excel Formulas / Excel Dependent Data Validation
Excel Dependent Data Validation
August 16, 2016 by Mynda Treacy

I’ve written about how to create dependent data validation lists before here; Excel Data Validation with dependent lists, and here; Dynamic Dependent Data Validation. However the approach I’m going to cover in this tutorial is probably the best I’ve seen, especially if you have a lot of dependencies as it’s easily scalable.

What is Dependent Data Validation

Dependent data validation is where you have a series of data validation lists that display a different list dependent on the selection chosen in the preceding list.

In the image below you can see when the USA is selected in the first data validation list, the state validation list only displays US states. And with CO selected in the State data validation list, the City validation list only displays cities in Colorado.

excel dependent data validation lists example USA

If I select a different country, the state and city lists dynamically update to display related items:

excel dependent data validation lists example Australia

Note: If you choose a different country/state the cells containing the data validation lists (Q7 and S7) don’t re-set, but you could write some VBA code to clear them out if a different country/state was selected.

Download Workbook

This tutorial has quite a few moving parts so it will be easier to follow along if you download the workbook and reference it as you read the steps below.

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.

Note: for the purpose of this tutorial the source data, PivotTables and data validation lists are all on the same sheet so you can see them side by side, but in practice it’s best to keep your workings (source data and PivotTables on separate sheets to your data validation lists).

 

Excel Dependent Data Validation Setup

There are a few steps to set up dependent data validation lists so let’s take a look at the process.

1.      Source Data

We start with a list of our countries, states and cities formatted in an Excel Table (mine is called Locations):

excel dependent data validation lists source data

Of course you might have products, projects, or other hierarchical data.

Count Column

We need to add a column that counts the number of states and apportions 1 across each record so that when you add up the count for a particular state it adds up to 1. What??? I’ll show you what I mean.

In the image below we can see the COUNTIF formula in column D. In column B (State) ACT has two records with each count in column D resulting in 0.50, so the total of ACT’s count equals 1. Likewise, NSW has three records and each count is .333’ so when added up we get 1.

excel countif column

Let’s understand the COUNTIF formula in cell D2:

=1/COUNTIF([States],B2)

In English the COUNTIF part of the formula reads: COUNT the states in the ‘States’ column IF they match the value in B2, which is ACT.

We then divide 1 by the COUNTIFS result to apportion 1 over the rows for that state. We use this column in a PivotTable to find out how many states we have for each country.

2.      PivotTables

Next we create 3 PivotTables; one for the list of countries:

excel pivottable for countries list

One for the list of states and state count we created with the COUNTIF formula:

excel pivottable for state list

And one for the list of cities and city count:

excel pivottable for cities list

We use these PivotTables as the source for our data validation lists. And the nice thing about using PivotTables is if the source data changes we just Refresh All (Data Tab) and everything is updated.

Tip: remove the Grand Totals from the PivotTable as they’ll interfere with the next step.

3.      Dynamic Named Ranges

We use three dynamic named ranges  (country, state and city) to find the relevant section of the PivotTables that we want displayed in each data validation list.

For example, in the image below we can see the Country data validation list source is a (dynamic) named range called ‘country’:

data validation list for country

I’ve used the OFFSET Function to create the dynamic named ranges. I won’t go into great detail on how OFFSET works, you can learn it at the above link, but I’ll do a quick recap.

OFFSET Function

The OFFSET function returns a reference to range of cells (or a single cell), and by nesting other functions inside it we can make the range returned by OFFSET dynamically update. See where I’m going here?

When a selection is made in the first data validation list (Country) the OFFSET formula we use in the dynamic named range for the second data validation list (State) will update based on the country selected, and so on.

The syntax for the OFFSET function is:

OFFSET(reference, rows, cols, [height], [width])

  • Reference is the starting cell
  • Rows is the number of rows to move down/up from the starting cell to find the first cell in our range.
  • Cols is the number of columns to move left/right from the starting cell. We won’t be moving left or right from the starting cell so this argument will be empty.
  • [height] is an optional argument that tells Excel how tall the range is that we want returned
  • [width] is an optional argument that tells Excel how wide the range is that we want returned. We only need a range that’s one column wide so we can omit this argument as the smallest range is always 1 column wide.

The dynamic named range formula for the Country data validation list is fairly straight forward:

=OFFSET(Sheet1!$F$3, , , COUNTA(Sheet1!$F$3:$F$20))

In English it reads:

Start the range in cell F3, , , then count the values in cells F3:F20 (F20 allows for growth) and use the resulting value as the height of the range.

Note: The 2 empty arguments denoted by , , account for the rows and cols arguments we don’t need.

The COUNTA part of the formula returns 3 and so the result returned by OFFSET is a range 3 rows high, which is F3:F5

In the image below we can see the formula in the Name Manager (Formulas tab or F3 to open the Name Manager), and the cells F3:F5 in the worksheet are surrounded in marching ants to show the range returned by the ‘country’ name:

dynamic named range for country

Helper Cells

For the State and City named ranges we’ll use some helper cells to provide OFFSET with the rows and height arguments for the Country and State.

Let’s start with the dynamic named range for ‘state’, which uses this formula:

=OFFSET(Sheet1!$H$3,Sheet1!$O$3,,Sheet1!$O$4)

The reference argument is H3, which is the first row (after the header) of the second PivotTable. The rows and height arguments are referencing helper cells O3 and O4 respectively.

Rows: Cell O3 contains a MATCH formula that locates the row number in the range H3:H40 that contains the country selected in the first data validation list.

Height: Cell O4 contains an INDEX formula that returns the state count for the country selected in the first data validation list.

Below is an image showing the helper cells (O3 and O4) and the formulas contained within:

helper cells for dynamic named ranges

We can see in the image above that the country selected is Australia, which is in the first row of the range H3:H40, so MATCH returns 1.

In cell O4 INDEX references helper cell O3 to return the 1st value in the range I3:I40, which is the 7 in cell I3.

OFFSET uses 1 as the rows argument and 7 as the height argument and the dynamic named range formula evaluates to:

=OFFSET(Sheet1!$H$3,1,,7)

Which evaluates to the range:

=H4:H10

And when this named range (state) is used as the source for the data validation list we get a list of Australian states because ‘Australia’ is selected in the Country data validation list in cell O7:

dynamic named range for state

The dynamic named range formula for ‘city’, which is used as the source for the City data validation list, works similarly:

=OFFSET(Sheet1!$K$3,Sheet1!$Q$3,,Sheet1!$Q$4)

It references helper cells Q3 and Q4 which locate the state in the third PivotTable and number of cities for that state using the same technique as the State named range.

Notes:

  • If you’re confident with OFFSET and dynamic named ranges you can nest the formulas from the helper cells into the dynamic named range formula if you prefer.
  • If you have more dependencies you can replicate the dynamic named ranges accordingly.

4.      Data Validation Lists

Now all that remains is to set up the data validation lists in the cells you want and use the named range as the ‘source’.

On the Data tab > Data Validation. In the ‘Allow’ field choose ‘List’ and in the ‘Data’ field enter your dynamic named range:

set up Excel dependent data validation lists

Limitations

1. If you have States or Cities that are present in multiple countries/states then you have to make them unique. For example, both USA and Australia have a WA state. In the US, WA is for Washington and in Australia, WA is for Western Australia. I’ve entered WA for Australia as W.A. to differentiate it. *Update - see solution to this limitation below

2. This only works for one set of Data Validation Lists. If you want to copy the Data Validation lists to multiple rows then you'll find a similar technique in this Excel workbook.

Duplicate State/City Solutions

Update

A few of our members took on the challenge of solving the duplicate state/city limitations and they agreed to let me share them with you here.

1. Andrew Evans used a simple COUNTIFS to ensure the country and state matched in step 1. He also had to make some other changes and you can see them all in his file here.

2. Jim Benton avoided step 1 altogether by shifting the count outside of the PivotTable. You can see his file here.

3. Leonid Koyfman used an array formula and the seldom used N function to perform a logical test inside MATCH. He also added some Conditional Formatting to highlight when the State or City lists didn't match a selection higher up. You can see Leonid's file here.

4. Henk Huiting has a slightly different approach which also allows for use on multiple rows and returns an error if you try to choose a different item before clearing entries downstream. You can see it here.

Thanks

I love to see this amazing Excel community working together to find solutions that we can all learn from. Thanks to Andrew, Jim, Leonid and Henk for taking the time to share your ideas.

A big thank you to PaulaS and UtterAccess for this awesome technique. PaulaS shared a link on our Excel Forum to this technique on the UtterAccess.com wiki.

Excel Dependent Data Validation

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 Formulas
Previous Post:Slicer Controlled Interactive Excel ChartsSlicer Controlled Interactive Excel Charts
Next Post:Clear Downstream Dependent Data Validation ListsClearing Downstream Dependent Data Validation Lists

Reader Interactions

Comments

  1. Hardik Shah

    November 29, 2016 at 3:01 pm

    Hello,
    The above solution holds good for result in one row. What if I have multiple rows & generate the result of Country, State & City details in multiple cells.

    Reply
    • Mynda Treacy

      November 29, 2016 at 8:26 pm

      Hi Hardik,

      In the post above under the heading “Duplicate State/City Solutions”, point 4 contains a solution to using the data validation list on multiple rows. I presume that’s what you meant, but if not, please let me know.

      Mynda

      Reply
  2. Shawn

    September 20, 2016 at 9:32 am

    Using a volatile function like OFFSET isn’t something that scales well, so I rewrote some of the names using INDEX instead. Here’s an example:

    Country
    =Sheet1!$F$3:INDEX(Sheet1!$F$3:$F$20,COUNTA(Sheet1!$F$3:$F$20))

    State
    =INDEX(Sheet1!$H$3:$H$100,MATCH(Sheet1!$O7,Sheet1!$H$3:$H$40,0)+1):INDEX(Sheet1!$H$3:$H$100,MATCH(Sheet1!$O7,Sheet1!$H$3:$H$40,0)+INDEX(Sheet1!$I$3:$I$40,MATCH(Sheet1!$O7,Sheet1!$H$3:$H$40,0)))

    You could define some of the regions or subparts of that last one with names as well to make it more readable/adjustable

    Hope it helps.

    Reply
  3. Jack

    August 20, 2016 at 3:40 am

    Very nice for those applications where it fits. I modified it a little to make me happier by using Countifs in Column D to avoid having issues with duplications in the States column ( =1/COUNTIFS([Country],A2,[States],B2) ). The Countifs formula essentially filter by Country and State. That still left an issue with the Pivot Table in columns K – L which was resolved with 13 lines of VBA code that Filters the Pivot Table by Country and includes the resets whenever the value in O7 changes. I included the code below if anyone would like to try it and please note that it is a WorkSheet_Change code that must be inserted onto the Code Page of the same name as the Worksheet (i.e. in this case it would be Sheet1) that the Data Validation is on.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False

    If Intersect(Target, Range(“O7”)) Is Nothing Then Exit Sub
    On Error Resume Next
    Application.EnableEvents = False
    Sheets(“Sheet1”).PivotTables(“Sheet1”).PivotCache.Refresh
    With Me.PivotTables(“PivotTable3”)
    .PivotCache.Refresh
    .PivotFields(“Country”).CurrentPage = Target.Value
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End Sub

    It works very well for me so I hope this helps some others.

    Reply
  4. Henk Huiting

    August 20, 2016 at 12:06 am

    Hello Mynda,

    Thanks for this good blog!
    I like the way the pivottable is used for making items unique. But i didn’t like the help-cells in O3:O4 and Q3:Q4. Especially when you want to use this data-validation on more rows. So with your blog and an other one, i found it can be done without help-cells and even with a warning that you can’t change the country if the state-cell is filled.
    If your interested in this file, let me know because i can’t send it with this reply.
    Regards, Henk.

    Reply
    • Mynda Treacy

      August 20, 2016 at 2:43 pm

      Thanks, Henk.

      Yes, as I said in the comments in step 3; you can nest the helper cells in the named range formula if you’re confident to do so. I left them out to make it easier to follow the steps.

      I’d be happy to share your file with the others that also solve the duplicates issue under the heading “Duplicate State/City Solutions” above.

      Cheers,

      Mynda

      Reply
  5. Michael Fate

    August 19, 2016 at 3:28 am

    Great post Mynda. This will help me quite a bit to arrange Coach’s and Coachee’s in our organizations coaching program.

    You mentioned a bit of VBA code could be written to reset the dependent fields. Can you provide any help or direction in how this could be accomplished?

    Many Thanks,

    Reply
    • Mynda Treacy

      August 19, 2016 at 9:14 am

      Hi Michael,

      Glad you’ll find this useful.

      Phil’s blog post next week will provide some VBA code to reset the dependent fields when there is a mismatch.

      Mynda

      Reply
    • jim

      August 19, 2016 at 6:39 pm

      Instead of using that, I’d use conditional formatting to highlight mismatches or make them appear blank
      In the Multi DV version you could make the subsequent dropdowns have “Select valid state/city for previous entry” (or something more succinct) as the only option available

      Reply
  6. Steve Farrell

    August 19, 2016 at 1:47 am

    I may be oversimplifying but is using slicers not going to give the same result with less work?

    Reply
    • Mynda Treacy

      August 19, 2016 at 9:03 am

      Hi Steve,

      Thanks for your comment. Good question, but Data Validation allows you to choose an item from a list and insert it in a cell. Slicers can’t do this.

      Mynda

      Reply
      • SunnyKow

        August 19, 2016 at 11:40 am

        I use both Slicer and Data Validation, depending on the needs.
        You can still “enter” data into a cell using Slicer.
        Just use a formula to refer to the filtered Pivot Tables.
        It is limited to certain cells but so far it is good enough for me.

        Reply
        • Mynda Treacy

          August 19, 2016 at 11:46 am

          I suppose I think of data validation being used for data entry, where you want to restrict a user to entering data you provide. Using a Slicer for this wouldn’t work so well, but for one off filtering, which is what Slicers do best, is practical.

          Reply
  7. Bob Hutchins

    August 18, 2016 at 11:39 pm

    Wow! Really good stuff! I have tried to figure out ways to this very thing without much success, so you have planted a seed for me.

    I think I grasp the concept, but maybe I don’t. The MATCH() functions in O3 and Q3, they are hard-coded to row 7 ($O$7 & $Q$7, respectively) so it seems that the data validation may only work for the three columns in the example, but only in Row 7. I inserted the data validation criteria in the cells immediately below the example cells (Row 8), and the drop down lists are incorrect. For example, if I choose “US” as the country (again, in Row 8), nothing changes in O3 (because nothing changed in O7) so I get a list of Australian states. The drop down list for cities (in Row 8) is the list of cities for the state listed in Q7 (the cell directly above).

    This is why I say I maybe (probably) don’t grasp the concept.

    Please straighten me out!

    Reply
    • Mynda Treacy

      August 19, 2016 at 3:56 pm

      Hi Bob,

      The technique described above doesn’t work for mutliple rows of the Data Validation Lists. I will add that as a limitation.

      In this Excel file is a modified version which uses the INDIRECT function instead of dynamic named ranges.

      Hope that helps.

      Mynda

      Reply
      • Bob Hutchins

        August 19, 2016 at 8:46 pm

        Mynda,

        Thanks for the help – this solution is perfect for me!

        Bob

        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.