In the image below you can see that I sorted column D so that all the Category items are grouped together in the list. It’s also important to note that the child list needs to be sorted for this technique to work. This is where the relationship between the Category and Type are created. The second column in the table (column E) contains the different types for each category. These are the exact same names that are in column B. The first column in this table (column D) contains the Categories. The child list is the second list of choices that will be dependent on the selection in the parent list. We then use the list in columns D:F on the ‘Lists’ sheet to populate the child list with items. The parent list is the list where we will make the first choice. The list in column B contains the Category items for the parent list. In the image above, the ‘Lists’ sheet contains the lists for each drop-down. Our first step is to create the source tables that we will use for the contents of the drop-down lists.
You can also download the example file to follow along, or just copy the sheets into your workbook.ĭependent Dropdown Lists - 3 Lists.xlsx (37.6 KB)ĭependent Dropdown Lists Multiple Dependents.xlsx (37.3 KB) Step 1: Prepare the Source Tables
The rest of this article will explain how to create these dependent lists in your own workbook. Step-by-Step Guide to Dependent Drop-down Lists This technique requires the use of the OFFSET function to create the dependencies, and it’s a great function to learn. These are called dependent or cascading drop-down lists because the 2nd list depends on the choice made in the first list. When I select “Wine” from the list in cell B4, the list in cell E4 displays different types of Wine. When I select “Coffee” from the validation list in cell B4, the list in cell E4 displays different types of Coffee.
You can see an example of this in the screencast below. Leah asked the following question, “How do I create a drop-down list where the list of choices changes when I select an item in another list?” If you don't mind using named ranges then there are a few links at the bottom of the page with solutions that will be easier to implement.įunctions used: OFFSET, MATCH, COUNTA, COUNTIF, INDIRECTīefore we dive into dependent lists, if you are unfamiliar with drop-down lists in general (also known as data-validation lists), I strongly suggest you check out my tutorial for creating drop-down lists and also this post for making drop-down lists dynamic. This technique does NOT require named ranges. This technique does NOT require named ranges.īottom line: Learn how to create cascading or dependent drop-down lists (also known as cascading validation) in Excel. Learn how to create dependent drop-down lists (also known as cascading validation) in Excel.