With Excel data validation, you can create dependent drop down lists to show items based on the value in another cell. For example, select "Canada" in cell B2, and see a drop down list of Canadian cities in cell C2. There are several ways to set up the dependent drop downs, and this page helps you pick the method that's best for your requirements.
This animated screen shot shows how a simple dependent drop down list works. There is a main list (Produce Type) and a dependent drop down (Item)
There are setup instructions and a video on the Create Dependent Drop Down Lists page.
This simple dependent drop down lists method is good to use when:
If your lists don't meet these requirements, this method isn't the best fit. You will spend too much time setting up named ranges, and maintaining them.
If your version of Excel has dynamic arrays, you can use the new Excel functions to create a dependent drop down list. See the video and written steps on the Dependent Drop Down - Dynamic Arrays page
NOTE: Dynamic arrays are available in Microsoft 365 plans.
If your main list has lots of items, or if you want multiple dependent levels, a flexible dependent drop down setup is a better choice. This screen shot shows an example of this technique, with a main drop down (Regions), and 3 levels of dependent drop downs - Country, Area and City.
There are setup instructions and a video on the Dependent Lists Using Tables page
This technique requires only two tables
and 3 named ranges
This flexible dependent drop down technique is good to use if any of these conditions exist:
If your lists meet any of these conditions, this method will be the best fit. You will set up the tables and named ranges once, then edit and add to them when the items change.
After someone selects an item from a dependent drop down, they could go back to the main drop down, and select a different item. That could result in mismatched items in that row.
For example, Fruit could be selected from the main drop down, and then Banana in the dependent drop down. If Fruit is changed to Vegetable later, the Banana selection would be incorrect -- it's not a vegetable.
To prevent invalid selections, you can use a formula or a macro.
Click these links to see more information on dependent drop down lists. Many of the pages have sample files to download, and some have videos that show how to set up the dependent lists.
Last updated: April 16, 2021 2:47 PM