To create an Excel drop down list has become extremely easy and no longer requires any programming. You can simply make use of the Data Validation Tool.
If you want to create a simple dropdown so that a user chooses only one of 3 departments e.g. Department A, B or C you can follow these steps:
- Click on the cell or cells you want the drop down to appear in
- Go to the Data Ribbon and click on Data Validation.
- In the ‘Allow’ box, choose the List option
- In the Source, one way of creating the list is just to type it separated by commas e.g. A,B,C
- Click OK
Now when someone clicks on one of these cells a drop down arrow will appear and when clicked will show only the A, B, or C. The user can choose one of them but if they type a D for example, it will not be allowed.
For lists that require more options e.g. a drop down list of employees in the company, you may not want to type it in manually in the Data Validation tool. In this case you can set up the list in a separate sheet and then refer to the list in the source instead if typing them out.
The process is the same except that instead of typing the list you highlight the cells that contain the drop down list.
You can now link formula to these cells confident that users will use the correct spelling of the values.