Excel provides an easy way to select a value from a list of values. I often use this feature in dynamic Excel reports to select dates, regions, products, and other settings.
Here, 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 call the list MyList. To assign the label in cell A2 as the name of the list below it, select the range A2:A7. Then…
- In New Excel, choose Formulas, Defined Names, Create from Selection.
- In Classic Excel, choose Insert, Name, Create.
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 New Excel, choose Data, Data Tools, Data Validation, Data Validation.
- In Classic Excel, choose 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…
…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.
There are few 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.
My next post, How to Select Dates and Regions in a Dynamic Excel Report Linked to an Excel Database, uses data validation to control a dynamic Excel report.