A drop down list in Excel presents a number of options for the user to select from. The advantages of eliciting a response from the user in this way are that:
- the entries in the list give the user a clue about what values are allowed or expected
- it’s easier for the user to select an option than to type one into a cell
- it reduces typos that the user makes
- you can make the list dynamic by populating it with entries from different cells elsewhere in the workbook
To indicate that a drop down list is present, Excel displays a down arrow to the right of the cell.
Create A Drop Down List From A Range Of Cells
To create a drop down list from the contents of a range of cells, first of all type in the values you want to appear in the list in a single row or single column in your workbook. Make sure that there are no blank entries in the list.
Be aware that the order that you type in the data is the order that the values will appear in the drop down list so perform any sorting you need before you go any further.
Select the cell where you want the list to go and then click the Data tab and go to the Data Tools group. Click the Data Validation button and in the Data Validation window ensure that you’re working on the Settings tab.
In the Allow box select List and then enter the range of cells in the Source box. To get the range into the box, you can also click on the cell selector button and then drag over the cell range you need. Make sure that the range of cells is preceded by an equals sign like this: =$F$8:$F$13
Make sure that the In-cell drop down box is checked and if you want the user to be able to make a blank selection (i.e. leave it blank) then check the Ignore blank box.
If you want to display an informational message for the user when they make the drop down list cell active, go to the Input Message tab. Check the Show input message when cell is selected box. Then enter values for the title an input message itself.
If you want to display an error message after invalid data has been entered, click on the Error Alert tab. Ensure that the Show error alert after invalid data is entered box is checked and select the most appropriate Style:
- To display an information message that does not prevent entry of invalid data, select Information.
- To display an information message that does not prevent entry of invalid data, select Warning.
- To prevent the entry of invalid data, select Stop.
Type in a title for the error message and also some descriptive text.
To remove a drop down list from a cell, make the cell active and then click to the Data tab. Go to the Data group and then click the Data Validation button. On the Settings tab, click the Clear All button at the bottom.
Create A Drop Down List Using Static Values
If you want a drop down list of static values that don’t change, and aren’t dependent on the contents of other cells, you can type in the values directly. Select the cell where you want the list to go and then click the Data tab and go to the Data Tools group. Click the Data Validation button and in the Data Validation window ensure that you’re working on the Settings tab. In the Allow box select List and then type in the value you want to display in the list into the Source box. Separate the values with a comma. Review the steps above if you need to add any informational or error messages. Click OK when finished.
If you typed in an input message, it will be displayed when the cell becomes active, like this:
The user will need to click on the down arrow to display the drop down list, and then they can click on the entry they want to select it.
