Excel provides two ways to select a value from a list of values. I often use this feature in interactive Excel reports to select dates, regions, products, and other settings.
Below, for example, the dropdown list box in cell C3 allows users to select any of the five products in the list.
The list is easier to maintain if you name it. Here, I named the list MyList. To assign the label in cell A2 as the name of the list below it, first select the range A2:A7. Then, in the Formulas, Defined Names group, choose Create from Selection.
In the Create Names dialog, make sure that only Top Row is checked, then choose OK.
To set up the list, select the cell where you want the list box to be, and then in the Data, Data Tools group, choose Data Validation, Data Validation.
In the Settings tab of the Data Validation dialog, choose List in the Allow list box. In the Source ref-edit box, enter…
=MyList
…if you want to use a range name. Otherwise, click in the cell and then select the list. Doing so will return the cell reference of the list. Then choose OK.
Initially, the cell in the list box will be unchanged. To change it, click on the arrow to the right of the cell and select a new value, as shown above.
There aren’t many limitations to this method. The list can be in a different worksheet, even in a different workbook. The list can be in a row or a column. And if the list contains more than eight cells, Excel adds a scrollbar to the dropdown list box.
