Excel Lesson 9 of 30: Excel Drop-Down Lists and Data Validation

Creating a drop-down list in one or many cells of an Excel worksheet is very easy and very useful. With drop-down lists there is no need to enter manually the same values time and again and you are sure that the spelling is right.

Note: The Excel drop-down lists presented below can be created by anybody on regular Excel worksheets. If you are looking for drop-down lists for programmers see the VBA (macros) section

I use Excel drop-down lists a lot when I need people to enter data in an Excel database.

I also use Excel drop-down lists when I create questionnaires that users complete. By using drop-down lists I am sure that they supply me with valid answers because the drop-down list limits them to a choice of pre-set answers.

Finally I use them in dynamic reports (automated using the SUMPRODUCT function) where users can select a month, a store, a branch in the header and the report then shows the proper numbers based on the choice of the user.

First method:

Open a new workbook and select cell C1 in the first worksheet. Go to the menu bar and select "Data/Validation" the following window appears:

In the "Allow" text box select "List" and the window changes to this:

In the "Source:" text box write YES,NO separated by a comma and click on "OK". You now have a drop-down list from which you can select either "YES" or "NO. This is very useful when you create a questionnaire in Excel and you want to user to answer exclusively by "YES" or "NO".

Second method:

In the "Source" text box you can also submit a range of cells where you maintain your list. ex(=$A$1:$A$23). DO NOT FORGET the $ because when ypu copy/paste this cell into others it will look at another list.

Leave the first cell of this range that you are submitting empty specially if you have more than 8 values. The good reason for doing so is that when you try to use your drop-down list the selection made by Excel is the first empty cell in the range where you maintain your list. By leaving the first cell empty it becomes the selected value and all the other values follow below.

Leave cell A1 empty and in cells A2 to A6 enter five names (Peter, John, Mary, Luke, Ann). Select cell B1 go to the menu bar "Data/Validation". In the text box "Allow:" select "List". In the "Source:" box write =$A$1:$A$65 then click on "OK". Try your drop-down list. Select cell B2 and do the process again but this time in the "Source" box write =$A$1:$A$11. Try your drop-down list. In cell A7 enter Magdalene. Go back to cell B2 and look at the choice that you have in the drop-down list. Notice that "Magdalene" is there. When I create drop-down lists I always include a few empty cells in the range that I use as reference to add new values.

Third method:

Select cell B3 and do the process again (Data/Validation). To submit the values that will be used in your list click on the small red arrow ("Source:" box) then click on cell A1 hold and select all the way to A11. Click again on the small red arrow, click "OK" and there you are.

For the second and third methods if you don't want the list that feeds you drop-down to show just hide the column where they are.

Fourth method:

When you use the third method if you try to go to another sheet after clicking on the small arrow Excel doesn't allow you to do so. As I like to maintain all my lists on a single sheet, I found a way around this (see below).

Cascading Drop-Down Lists

When you select a country in the first drop-down list you want only the cities from this country to show in the second drop-down list not all the cities of all the countries. We call it cascading drop-down lists (see below).

Extending drop-down list

You can create drop-down lists in a single cell or in many cells at the same time. If you need to extend the range showing the drop-down list you can do it two ways.

1- Set the value of one cell that has the drop-down list to nothing and copy/paste it.
2- Select one cell that has the drop-down list and any number of other cells. Go to "Data/Validation" and Excel will tell you that the selection contains cells without the data validation setting. Click on the "Yes" button.

Deleting the drop-down lists

If you want to clear the data validation setting in some cells select them go to "Data/Validation" and click on "Clear All". You can also select many cells that contains the setting or not and go to "Data/Validation". You will see the dialog box asking you if you want to extend the settings. Click "Yes" and then click on "Clear all".