Our Expert Consultants use dropdown lists to simplify data entry within Microsoft Excel. They can also be used to validate your data. However, it's not immediately obvious how to create these lists, especially if you wish to select from a long list of data.
The first step is to give your data set a single name. We can then refer to this in the creation of a dropdown. You can do this by selecting the items and entering a name in the address bar (1). You have given the list a range name, albeit one that is defined by the cells selected. That means any further items listed would not get added to the range and subsequent dropdown. A more sophisticated solution would be to use a dynamic range but that is not covered here.
The next step is to select the cell, or cells, to which you wish to add the dropdown list (2). You will also need to select the Data ribbon and click Data Validation->Data Validation
This brings up the box displayed in (3). By default, cells can accept any value. As this is the Data Validation option, you can force users to enter e.g. positive numbers from the Allow dropdown. To restrict entry to the values in your list, you should select to Allow a List, and enter the range name with an "=" sign as in the image. If you do not use an "=" sign, it is assumed you are listing the allowable values in a comma separated list. If you click OK, the cell will now contain a dropdown list of four animals. Without the equals sign, the dropdown list would contain a single option, "Animals".
One issue with Data Validation is that it will prevent you entering other data into your cells or cells. If you want a dropdown list without restrictions, you can click the Error Alerts tab from the Data Validation menu and uncheck the box that states "Display Error Alert after Invalid Data is Entered".
You can use data validation and dropdown lists on earlier versions of Excel. You can find the option on the Data menu under Validation. In all other ways, the procedure is identical.
