Create Excel Dropdown List

Create a drop-down list from a range of cells

To make data entry easier, or to limit entries to certain items that you define, you can create a drop-down list of valid entries that is compiled from cells elsewhere on the worksheet. When you create a drop-down list for a cell, it displays an arrow next to that cell. To enter information in that cell, click the arrow, and then click the entry that you want.

1. Open a Workbook Book2.xls;
2. Select a cell to be for dropdown list;
3. Click Data > Validation > Settings > Allow > List;
4. Fill Source*;
5. Click OK;
6. Display dropdown list button.

* Source has 3 places for dropdown list data:
1. Same Sheet Same Workbook:
(Sheet1)To select the data range for dropdown list.
2. Different Sheet Same Workbook:
1). (Sheet1)To select the data range, type name in name box;
2). (Sheet2)To fill source: =name(step1 created);
3. Different Sheet Different Workbook:
1). To create the dropdown list in Book1.xls;
2). To select the data range, type name in name box;
3). To click Insert > Name > Define... in Book2.xls;
4). To fill Names in workbook:;
5). To fill Refers to: as ='path\Book1.xls'!name then click OK;
6). To fill Source: =Names(step4 created);
Note: Book1.xls must be openned for Book2.xls work properly.