Most Excel users probably believe that a VBA macro is required in order to display a drop-down list in a cell. But it’s not. You can easily display a drop-down list in a cell — no macros required.
Below Figure shows an example. Cell B2, when selected, displays a down arrow. Click the arrow, and you get a list of items (in this case, month names). Click an item, and it appears in the cell. The drop-down list can contain text, numeric values, or dates. Your formulas, of course, can refer to cells that contain a drop-down list. The formulas always use the value that’s currently displayed.
The trick to setting up a drop-down list is to use the data validation feature. The following steps describe how to create a drop-down list of items in a cell:
1. Enter the list of items in a range. In this example, the month names are in the range E1:E12.
2. Select the cell that will contain the drop-down list (cell C2, in this example).
3. Choose Data➜Data Tools➜Data Validation.
4. In the Data Validation dialog box, click the Settings tab.
5. In the Allow drop-down list, select List.
6. In the Source box, specify the range that contains the items. In this example, the range is E1:E12.
7. Make sure that the In-Cell Dropdown option is checked and click OK. If your list is short, you can avoid Step 1. Rather, just type your list items (separated by commas) in the Source box in the Data Validation dialog box.
In previous versions, the list of items were required to be on the same worksheet as the cell that contains the drop-down list. With Excel 2010, that’s no longer the case.
If you plan to share your workbook with others who use an older version of Excel, make sure that the list is on the same sheet as the drop-down list. Alternatively, you can put the list on any sheet, as long as it’s a named range. For example, you can choose Formulas➜Defined Names➜ Define Name to define the name MonthNames for E1:E12. Then, in the Data Validation dialog box, enter =MonthNames in the Source box.