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.

The following spreadsheet contains a list of members of staff, split into teams. We can use this data to create a drop-down list that contains just the staff members for a selected team, and automatically updates when a different team name is selected.

Data for Variable Drop Down Menu Example

Step 1 - Create a List of Team Members that Update According to the Contents of a Single Cell

We start by creating a range of cells (cells E1-E6 in this example), that display the team members for any team name that is typed into cell G2. The steps to do this are:

  1. Type a team name into cell G2 (e.g. 'Admin' has been entered into the spreadsheet below);
  2. Insert the following formula into cell E1:

  3. Insert the following formula into cell E2:

    =HLOOKUP($G$2, $A$1:$C$6, ROW(), 0)
  4. Copy the formula in cell E2 into cells E3 - E6.

These formulas are displayed in the spreadsheet below:

Formula for Variable Drop Down Menu Example

Note that, in the above example spreadsheet, the formula "=$G$2" in cell E1 reflects team name that is entered in cell G2, and the formulas in cells E2 - E6 use the Hlookup and Row functions to look up the members of this team.

Step 2 - Create a Drop Down List Containing the Team Members for the Selected Team

In Step 1 above, we created a range of cells (cells E2-E6) that vary according to the team name that is typed into cell G2. We will now use the names in cells E2-E6 to create a drop down list that will automatically update every time the value of cell G2 is changed. We will put this drop-down list into cell G4 of the spreadsheet.

To do this:

  1. Select cell G4
  2. Select the Data Validation option. This is found:

    • Data Validation Option in Excel
      In current versions of Excel (2007 and later):   The Data Validation option is found in the 'Data Tools' grouping on the Data Tab of the Excel ribbon. From this menu, select the option Data Validation→Data Validation... (see right).
    • In Excel 2003:   The Data Validation option is in the Data Menu at the top of your Excel spreadsheet. Select the option Validation...
  3. Data Validation Dialog Box for Variable Drop Down List
    When you select the Data Validation option, the Data Validation dialog box will appear (see rightabove). Within this dialog box:
    1. In the 'Allow' input box, select the option 'List'. This should cause further options to appear in the dialog box.
    2. Ensure the option 'In-cell dropdown' is checked.
    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, that displays the team members relating to the team name that is currently typed into cell G2 (see below).

Typing a different team name into cell G2 causes the list in cell G4 to automatically update with the new team members.

Final Result for Variable Drop Down Menu Example

Step 3 - Optional Extras

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

To make your spreadsheet look that bit more professional, you might prefer 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 in the above spreadsheet on the right.

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