Excel Tips – How to create Drop Down list

Haven’t you always wanted to create those lovely drop down list boxes to enter data in lists or forms for uniformity and standardisation? (See Figure)

These are called data validation list boxes. Here’s how create them. In our example, Let us consider that you want the user to enter season in column B from a list of valid seasons. To create the List box for all cells in that column, select the entire column, except cell B1, and on the ribbon click <Data> à <Data Validation>

This will activate the [Data Validation] dialogue box. The [Settings] tab, prompts you for the validation criteria. In the first list box captioned Allow, select the option ‘List’. Now in the third list box captioned Source, type out the valid list separated by commas or refer to the range where valid seasons have been entered.

Note that you can also give a named range as the source of a valid list

In the Input message tab, you may enter a message like {Please Select A season from the List}. This may help the person who enters data as regards what is to be entered in that particular cell. In the Error Alert tab, you may enter a message like {Invalid Season, Only an item from the list is allowed!}. This message is flashed whenever the data entry person enters a season that is not one from the List. If you do not specify the Error Alert Message, Excel displays its standard Error Alert Message. That’s it! Click <OK>, and you are done. Now in every cell of column B, a list box appears as under. You may select the appropriate season, by simply clicking the drop down list box arrow. For Key Board addicts, who find it irritating to reach for the mouse every time the designation is to be entered, just go the cell in column D, and hit the [ALT + Down Arrow Key]