Dropdown lists

In Excel, dropdown lists are used to control user input into a cell or to get a single input value from the user to be used elsewhere in the Workbook.

A list shows a single column of data.

Simple

There is a List option in Data Validation that can be used to create a dropdown list in a cell.

The steps in Excel 2010 onwards are:

• Create a single column list of values
• On the Data tab choose Data Validation
• The Data validation form will be displayed
• Set the Allow dropdown to List
• Set the Source range to the first column of the list
• Click on OK (User messages can be added if required)

When the cell is copied, the Data Validation dropdown is copied as well.
To see that there is a dropdown in the cell, the user needs to click on the cell.

This approach is the best choice if the dropdown is going to be used multiple times to control data input.

The weakness of this option is that if the list has more items added, the data validation source needs to be updated to reference the larger range.

With a return value

If the List option in Data Validation is combined with a XLOOKUP formula you are able to convert the selected value into another value.

The steps in Excel 2010 onwards are the same as the Simple example above.

In the list of values add another column with the values that you want to return based on the user selection.

Then in a cell enter a formula like this

	=XLOOKUP(A5,$D$6:$D$10,$E$6:$E$10,0)

which will return the matching value from the second column of your list.

Dynamic with a return value

This solution requires Excel 365.

If the source data is put into a table, it can be referenced using a function that can return an array. In this example the VSTACK function will be used.
When the table expands, the dropdown options will automatically expand as well.
The return values are added into the table as a column.

The steps are:

• Create your table of list and matching values.
• Near to the table, enter a VSTACK formula referencing the column in the table that you want to use for the dropdown list.

	=VSTACK(tblDropdown_data3[Company List])

• On the Data tab choose Data Validation
• The Data validation form will be displayed
• Set the Allow dropdown to List
• Set the Source range to the first cell of the VSTACK formula before it spills and add a hash symbol to the end.

	=$D$6#

• Click on OK (User messages can be added if required)

In a cell enter an XLOOKUP formula to return the matching value from the table.

=XLOOKUP(A5,tblDropdown_data3[Company List],tblDropdown_value[Value],"MISSING")

With a dependant list

A dependant data validation list can be added where the items available in the second list are dependant on the value selected in the first list.

Put the source data is put into a table, add a second column that holds the dependant data. The data is going to referenced via four functions, UNIQUE, TOROW,FILTER and XLOOKUP that return arrays.

This solution is not dynamic.

The steps are:

• Create your table of list and dependant list values.
• Next to the table, enter a UNIQUE function referencing the column in the table that you want to use in the first list.

	=UNIQUE(tblDropdown_data4[Company List])

• Next to the column add a formula to filter and transpose the second list using TOROW and FILTER.

	=TOROW(FILTER(tblDropdown_data4[Department],tblDropdown_data4[Company List]=D6))

• Copy the formula down alongside the first list items.

• Select the cell where the first list is going to be used.
• On the Data tab choose Data Validation
• The Data validation form will be displayed
• Set the Allow dropdown to List
• Set the Source range to the first cell of the UNIQUE formula before it spills and add a hash symbol to the end.

	=$D$6#

• Click on OK (User messages can be added if required).
• Select the cell where the second list is going to be used.
• On the Data tab choose Data Validation
• The Data validation form will be displayed
• Set the Allow dropdown to List
• Set the Source range to an XLOOKUP formula.

	=XLOOKUP(A5,$D$6:$D$10,$E$6:$H$10)

• Click on OK (User messages can be added if required)

There is a weakness with this solution. If a user makes selections but then changes the selected item from the first list, the dependant list item is not cleared. This can result in invalid combinations. A way to mitigate this, is to use conditional formatting to highlight invalid combinations.

Form control dropdown

Form controls can be placed on a worksheet. They can be linked to a range and return the position number of the selected value to a specific cell.

The steps in Excel 2010 onwards are:

• Create your list of data in a worksheet
• Click on the Developer tab and dropdown on the Insert option
• In the Form section choose Combo box or List box
• Use the mouse to draw the box on the worksheet
• Right click on the box and select Format control
• The Format control form will be displayed
• Click on the Control tab
• Set the Input range to your list of data
• Set the Cell link range to the cell where you want the number of the selected item to appear
• Click on OK

This control can be attached to a cell or be free floating.
It is best used if the dropdown value is only going to be selected once on a Worksheet.