2. Excel How to create a drop-down list ?
This is the validation of data against a list of values. It is suitable for columns that accept a limited number of values.
Example
For example, let's say we have a sheet with a "City" column. We will add a drop-down list to the cells of this column.
The list of cities is preferably saved in another sheet :
Desired outcome
When selecting each cell in the "City" column on Sheet 1, a button appears allowing me to open a drop-down list with the values stored in the "City List" column on Sheet 2. By clicking on an item in the drop-down list, its value is automatically entered in the selected cell.
How to do ?
- Select the "City" column on Sheet 1
- Click on the Data tab of the ribbon
- Click on the Data Validation command. The Data Validation dialog box is open
- Choose "List" from the Allow list. Leave the Ignore blank and In-cell drop down boxes checked
- In the Source field of the dialog box, enter the address of the range A2:A8 on Sheet 2; where we have entered the list of cities.
You can also add an Input Message and an Error Alert.
Input Message
The Input Message tab of the Data Validation dialog box allows you to define a message that appears when the relevant cell is selected:
- Enter a Title
- Enter a Message
Then switch to the Error Alert tab of this dialog box.
Error Alert
The Error Alert tab of the Data Validation dialog box allows you to define a message that appears when a value that does not respect the validation rule is entered in a cell concerned by this validation
- Enter a Title
- Enter a Message
- Then validate by clicking on the OK button.
