Use Excel Formulas to Create a Variable Drop-Down List

The following example shows you how to use Excel formulas to create a variable drop-down list.

Suppose you have a list of members of staff, split into teams. In the following example a drop-down list is created, which contains all the staff members for a selected team. This list varies depending on which team the user is interested in.

We begin with a list of members of each team, as shown below:

Data for Variable Drop Down Menu Example

Next, we create a range of cells that show the team members for any selected team. The team name is typed into cell G2, and a formula in cells E2-E6 returns the members of the selected team.

The formula to show the team members combines the Hlookup and the Row functions. As shown in the spreadsheet below, this formula, (entered into each of the cells in the range E2-E6), is:

=HLOOKUP($G$2, $A$1:$C$6, ROW(), 0)
Formula for Variable Drop Down Menu Example

Also, in the example spreadsheet, the formula "=$G$2" is used to reflect the selected team name in cell E1.

We can now create a drop down list, that takes its valid values from cells E2-E6.

To create a drop-down list in cell G4, from the values in cell E2-E6:

  1. Select cell G4
  2. In Excel 2007 and later versions of Excel:   Select the Data Tab from the top of your Excel spreadsheet and from within this menu, select the option Data Validation→Data Validation...
    In Excel 2003:   Select the Data Menu from the top of your Excel spreadsheet and from within this menu, select the option Validation...
  3. The Validation menu will appear. Within this menu:
    1. In the first box, select the option 'List'. This should cause further options to appear.
    2. Ensure the option 'In-cell dropdown' is ticked
    3. In the 'Source' option box, click on the symbol on the right of the box and use the mouse to select cells E2-E6 or type in "=$E$2:$E$6"
    4. Click OK

You will now have a drop-down menu in cell G4. This is shown below.

Final Result for Variable Drop Down Menu Example

Final Result for Variable Drop-Down List, Depending on Another Drop-Down List

To make your spreadsheet look that bit more professional, it is a good idea to hide columns A-C, which contain the original data, or place this data onto a different worksheet.

As a further finishing touch, you could also make the cell G2 into a drop-down list, from which a team name can be selected, as shown below.

Click here for a further example of creating a drop-down list using excel data validation