How do I create Excel drop down list values?

The ability to create Excel drop down lists has been around for a long time. It is a very useful feature since it enables a user to click a drop down button on a cell providing them with a list to select from when entering data.

One of the main benefits of using a drop down list is to provide a user with an easier way of inputting data with a great degree of accuracy thus improving your data integrity.

Another benefit is that searches and filters are found to be more reliable since spelling mistakes in drop down data lists can be eliminated.

To create a new list drop down you either need to create the list from scratch or use an existing list. It is always considered best practice to place the list into a separate sheet and then protect and hide the sheet to prevent someone from deleting the list.

Before applying the suggested security and protection procedure, first select the list and give it a name by clicking into the Name Box area, type in a name (spaces not allowed) and press enter. Another good practice is to sort the list in ascending order which makes it easier to find things when presented with the drop down list later on.

The next step is to choose all the cells where you want the drop down list to appear.

The drop down list is created by using the Data Validation tool found on the Data ribbon in versions 2007 and 2010 (for earlier versions use the Data menu). After clicking the Data Validation button you will be presented with a dialogue box with three tabs. The first tab is Settings and here is where you will select 'List' from the Allow: drop down list.

Now go to the Source field and press = immediately followed by the name you have given to your list. If you don't have a separate list stored on a sheet, you can create a list on the fly by entering items into the Source field separating each one with a comma. There is no comma after the last item. You do not need to use the other tabs unless you want to.

Click OK to close the dialogue box.

If you now click on one of the cells which you selected to have the drop down list, you are presented with a small drop down button on the right side of the cell. Clicking this button will present you with a list of items that you can now choose from. Once you click an item, it will be entered into the cell.

Related forum posts:

Applying a drop down option

Please could i be reminded how to add a drop down option to a particular column in a already excisting spread sheet
Read forum post

macros

I would like to produce a cell with a drop down arrow and the options within that cell be.... low, medium and high, but cant seem to do it. I was wondering if someone could help me please? Thanks
Read forum post