Bottom line: Learn how to create cascading or dependent drop-down lists (also known as cascading validation) in Excel. This technique does NOT require named ranges. If you don't mind using named ranges then there are a few of the page with solutions that will be easier to implement. Skill level: Intermediate Functions used: OFFSET, MATCH, COUNTA, COUNTIF, INDIRECT Drop-Down (Data Validation) Lists Before 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 and also. What is a Dependent Drop-down List?

Excel VLOOKUP with Drop Down - The VLOOKUP function in Excel can become interactive. A table array and returns a value in the same row from another column (to the right) in the table array. DOWNLOAD EXCEL WORKBOOK. Create a Data Entry Form in Excel Connect Slicers to Multiple Excel Pivot Tables.

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?” You can see an example of this in the screencast below. When I select “Coffee” from the validation list in cell B4, the list in cell E4 displays different types of Coffee.

To view the screencast animation in your browser. When I select “Wine” from the list in cell B4, the list in cell E4 displays different types of Wine.

These are called dependent or cascading drop-down lists because the 2nd list depends on the choice made in the first list. This technique requires the use of the OFFSET function to create the dependencies, and it’s a great function to learn. Step-by-Step Guide to Dependent Drop-down Lists The rest of this article will explain how to create these dependent lists in your own workbook. You can also download the example file to follow along, or just copy the sheets into your workbook. (36.6 KB) (37.6 KB) (37.3 KB) Step 1: Prepare the Source Tables Our first step is to create the source tables that we will use for the contents of the drop-down lists. In the image above, the ‘Lists’ sheet contains the lists for each drop-down.

The list in column B contains the Category items for the parent list. The parent list is the list where we will make the first choice. We then use the list in columns D:F on the ‘Lists’ sheet to populate the child list with items. The child list is the second list of choices that will be dependent on the selection in the parent list. The first column in this table (column D) contains the Categories. These are the exact same names that are in column B. The second column in the table (column E) contains the different types for each category.

This is where the relationship between the Category and Type are created. It’s also important to note that the child list needs to be sorted for this technique to work. In the image below you can see that I sorted column D so that all the Category items are grouped together in the list.

How to Create the Lists These lists originated from a much larger data table that contains transactional sales data. To create the lists I needed to extract all the unique values for each list.

Fortunately, Excel has some great built-in tools that allow you to remove the duplicates. One way is to use the Remove Duplicates tool that is located on the Data tab in the ribbon. You could also create a pivot table to quickly list the unique values in the Rows area of the pivot. Then copy/paste the results to the ‘Lists’ sheet that contains the source tables. I won’t go into details on these techniques, but they will make it very fast to create and update you dependent lists. If your lists change frequently with new items, then I would recommend the pivot table method because you can simply refresh the pivot table to generate your new list. Step 2: Create the Parent Drop-down List Ok, now that we have the source data lists setup we can create the drop-down lists.

It displays the name of each font next to its rank. Font viewer for microsoft edge. It displays an example of text with each font.

We will do this with in-cell data validation lists. Dependent drop-down lists are not a built-in feature of Excel. Therefore, we need to get creative with some functions and formulas to create the dynamic dependencies between the lists. With cell B6 selected on the ‘Dropdowns’ sheet, click the Data Validation button on the Data tab of the ribbon. The Data Validation window will appear.

• First, choose “List” in the Allow drop-down list. • Then enter the OFFSET formula in the Source box (see explanation below). We could put the following reference in as the Source: =Lists!B2:B4 However, we want the source list to be dynamic. Dynamic means that the list will automatically expand as we add more items to it. We don’t want to have to change the source reference every time we add items to the list.

The OFFSET function helps create this dynamic range reference, and returns a reference to a range of cells based on the coordinates you specify. It has the following arguments.