Excel Drop Down List
Excel drop down list also known as drop box or combo box is used to enter data from pre-defined items list into a spreadsheet. It serves the purpose of limiting number of choices accessible to the user enabling faster data input. The excel drop-down list assists in preventing spelling mistakes when making entries in excel.
How to Create Drop-down List
There are four ways for creating drop-down menu.
The first and fastest involves use of comma-separated values. Secondly there is drop-down menu based on named range. Thirdly the drop-down menu based on a table. And finally drop-down menu based on range of cells.
Creating Drop-down Lists with Comma-separated Values
Start by selecting a cell or range of cells where you want drop box to appear. This may include entire column, range of cells or single cell. By selecting a column drop down menu gets created in each cell of that column saving time. By press holding Ctrl key while selecting cell with the mouse, you can select non-contiguous cells.
After selecting cell range, use excel data validation to create a drop down list. This can be done by going to the Data tab followed by Data Tools group and click Data Validation. This is followed by entering list of items and choosing options. Under Data Validation window, on the Setting tab, choose Allow box and select List.
Then in the Source box type items intended for drop-down menu separated by commas with or without spaces. Click Ok once the In-cell drop-down box is checked and after checking Ignore blanks box. This enable users to leave the cell empty. To edit excel drop down list, one must Change every cell which references validation list.
Creating Excel Drop-down List Based on Named Range
This process takes time to create but in the long run it saves time. Start by typing entries for your drop-down list. This can be done by selecting entries required in drop-down menu from existing worksheet or by typing entries in new a sheet.
The entries are done in single column or row without blank cells sorting them in preferred order. Then create a name range to make drop-down list easier. Select every entry to be included in drop-down box, after right-clicking them choose
Define Name tab from menu or click Name Manager on the Formulas tab, or Ctrl+F3. Under Name Manager, click New. Then in Name field, type name of your entries using underscores to separate them followed by Ok.
Drop-down List Based on a Table
A list may be converted to excel table in Insert table then creating a list name.
This is done by entering table name in the Refers to field. Can also be done by opening Name Manager then selecting all cells without column header and then Refers to box will be filled automatically. Then select the location of your list by simply clicking where you want it. Under Data tab navigate to Data Validation.
On the Data Validation, under Settings in Allow box choose List. Type Name of range preceded by =. Check In-cell dropdown box and Ignore Blanks box then Ok.
Drop-down box based on Range of Cells
The excel drop-down list based on range of cell can be done by clicking on the Range Selection tab.
After that select every cell with entries you want in your drop-down list. If they are not the same, go to the other sheet and select a range with your mouse.