How to create a drop down list based on another list

What is a dropdown list based on another list?

A dropdown list based on another list is a Data Validation feature in Microsoft Excel that restricts cell entries to predefined values stored in a separate range, named range, or Table. This approach centralizes data management by allowing users to maintain options in one location while applying them across multiple cells.

Dropdown lists based on another list enhance data entry accuracy by limiting choices dynamically. They prevent invalid combinations, such as selecting incompatible options, which is especially useful in structured data workflows like clinical trials or inventory management.

How to create a dropdown list using a direct cell range reference

To create a dropdown list using a direct cell range reference, use the Data Validation dialog box with a source range from your worksheet.

Step 1: Enter your list items in a column or row. For example, type product names in cells A2:A6 on a worksheet named "Lists."

Step 2: Select the cell or range where you want the dropdown to appear.

Step 3: Go to the Data tab on the Ribbon.

Step 4: Click Data Validation in the Data Tools group.

Step 5: In the Data Validation dialog box, select the Settings tab.

Step 6: Under Allow, choose List from the dropdown menu.

Step 7: In the Source field, enter the range reference. Type =Lists!A$2: A$6 or click the range selector icon and highlight your source cells.

Step 8: Click OK to apply the dropdown list.

The dropdown now displays all values from the specified range. Absolute references (using $ symbols) ensure the source range remains fixed when copying the dropdown to other cells.

How to create a dropdown list using a named range

To create a dropdown list using a named range, define a name for your source data and reference it in Data Validation.

Step 1: Select the cells containing your list items. For a department list, select cells containing "IT," "HR," "Finance," and "Operations."

Step 2: Click the Name Box located to the left of the Formula Bar.

Step 3: Type a name for your range (for example, "Departments") and press Enter. Range names cannot contain spaces and must start with a letter or underscore.

Step 4: Select the cell where you want the dropdown list.

Step 5: Navigate to Data > Data Validation.

Step 6: Under the Settings tab, select List from the Allow dropdown.

Step 7: In the Source field, type =Departments (the named range you created).

Step 8: Click OK.

Named ranges simplify formula management and make Data Validation sources easier to identify. You can view and edit named ranges through Formulas > Name Manager.

How to create a dropdown list using a Table reference

To create a dropdown list using a Table reference, convert your source data to an Excel Table for automatic expansion when new items are added.

Step 1: Select your source data, including a header row. For a location list, select cells containing "Country" as the header and country names below.

Step 2: Press Ctrl+T or go to Insert > Table.

Step 3: Confirm the range and check "My table has headers." Click OK.

Step 4: Note the Table name in the Table Design tab (default names are Table1, Table2, etc.). Rename it to something descriptive like "CountryList."

Step 5: Select the destination cell for your dropdown.

Step 6: Open Data > Data Validation.

Step 7: Choose List under the Allow option.

Step 8: In the Source field, enter a structured reference: =INDIRECT("CountryList[Country]") where "CountryList" is your Table name and "Country" is the column header.

Step 9: Click OK.

Excel Tables automatically expand to include new rows, making this method ideal for lists that change frequently.

How to create a dependent dropdown list using the INDIRECT function

To create a dependent dropdown list using the INDIRECT function, combine named ranges with INDIRECT to make dropdown options change based on a previous selection.

Prepare the source data

Step 1: Create your primary list. For a product selection scenario, enter main product categories in column A: "Electronics," "Furniture," "Clothing."

Step 2: Create secondary lists for each primary option. In separate columns, list the corresponding items:

Column B (Electronics): Laptop, Smartphone, Tablet

Column C (Furniture): Desk, Chair, Bookshelf

Column D (Clothing): Shirt, Pants, Jacket

Create named ranges for each list

Step 3: Select your primary list items (A2:A4).

Step 4: Click the Name Box and type "Products" then press Enter.

Step 5: Select the Electronics items (B2:B4). Name this range "Electronics."

Step 6: Select the Furniture items (C2:C4). Name this range "Furniture."

Step 7: Select the Clothing items (D2:D4). Name this range "Clothing."

The named range names must match the primary list values exactly, including spelling and capitalization.

Create the primary dropdown

Step 8: Select the cell for your first dropdown (for example, F2).

Step 9: Go to Data > Data Validation.

Step 10: Select List under Allow and enter =Products in the Source field.

Step 11: Click OK.

Create the dependent dropdown with INDIRECT

Step 12: Select the cell for your dependent dropdown (for example, G2).

Step 13: Open Data > Data Validation.

Step 14: Select List under Allow.

Step 15: In the Source field, enter =INDIRECT(F2) where F2 is the cell containing your first dropdown.

Step 16: Click OK.

The second dropdown now displays options based on the selection in the first dropdown. Selecting "Electronics" in F2 shows Laptop, Smartphone, and Tablet in G2. Selecting "Furniture" shows Desk, Chair, and Bookshelf.

Use cases for dropdown lists based on another list

Product selection

Selecting a product from the first dropdown populates the second with relevant models or variants. Choosing "Laptop" displays "Model_X1," "Model_X2," or "Model_X3." This reduces errors in e-commerce forms or parts catalogs.

Size and add-ons

Picking a "Large" size updates the add-ons dropdown to only compatible items, such as specific fabrics or features for that size. Inventory systems and custom order forms use this configuration with named ranges and the INDIRECT function.

Location hierarchy

Country selection filters states or provinces in the next dropdown, then cities, streamlining address forms in registration systems or surveys. This cascading approach enables precise geolocation data entry across multiple administrative levels.

Clinical data entry

Choosing a "Study Site" dropdown filters available "Treatment Arms" or "Patient Cohorts," ensuring compliance with SDTM/ADaM standards by avoiding mismatched entries during data cleaning. This structure maintains data integrity in trial datasets.

Department tasks

Selecting a "Department" populates task or role options in the next list. IT shows "Server Maintenance," "Network Configuration," and "Software Installation." HR displays "Onboarding," "Benefits Administration," and "Performance Review." Project trackers and HR forms use this method to provide relevant options by team.

How to update a dropdown list after creation

To update a dropdown list after creation, modify the source data and adjust references when necessary.

Updating a direct cell range reference

Step 1: Add or remove items in your source range.

Step 2: Select any cell containing the dropdown.

Step 3: Go to Data > Data Validation.

Step 4: Edit the Source field to include the new range (for example, change =A2:A6 to =A2:A8).

Step 5: Click OK.

Updating a named range

Step 1: Add new items to your source list.

Step 2: Go to Formulas > Name Manager.

Step 3: Select the named range you want to modify.

Step 4: Click Edit.

Step 5: Update the Refers To field with the expanded range.

Step 6: Click OK, then Close.

All dropdowns using that named range update automatically.

Updating a Table reference

Step 1: Click inside your Table.

Step 2: Add new rows by typing in the first empty row below the Table. The Table expands automatically.

Step 3: Dropdown lists referencing the Table column include new items without manual adjustment.

Tables provide the most efficient method for maintaining dropdown lists that require frequent updates.