Drop-down lists are useful when you want to force the user to enter only specific values into a cell in an Excel sheet. The user is presented with a drop-down list and he has to choose any one value from it. If the user enters a different value, it will be rejected. For example, you may create a drop-down list where users select a day of the week or Yes/No. This little tutorial will show you how to add such a drop-down list to an Excel cell.
1. The first step will be to create the list. Add the list values in cells either vertically in a column or horizontally in a row. For instance, A1, A2, A3 etc or A1, B1, C1 etc.
2. Select the cell where you want to add the list, such as D1, in this example.
3. Click on the Data menu and choose Data Validation.
4. Choose List from the “Allow” option’s drop-down list. Make sure “In-cell dropdown” option is checked.
5. Click the Source control and drag to highlight the column A. Alternately, enter the reference (=$A$1:$A$7 or =$A:$A).
6. Click OK, and you are done.
You must have noticed that we still have the source list (column A) in the sheet and it doesn’t look good. But we can’t delete it because deleting it will remove the values from the drop down list. But we can move the list to another part on the Excel sheet, or to an entirely different sheet in the same workbook. So highlight the column and press Ctrl+X to cut it and then paste it (Ctrl+V) on Sheet 2, in any column – not necessarily column A.
This way you can create as many drop-down list on sheet 1 and place all sources on sheet 2.