Lesson 20: Excel Drop-Down Lists

Creating a drop-down list in one or many cells of an Excel worksheet is simple, very easy and very useful. With drop-down lists there is no need to key in  the same values manually time and time again and the spelling is always right.

Note: The Excel drop-down list presented below can be created by anybody on regular Excel worksheets. The drop-down lists for programmers also called combo boxes can be seen in the Tutorial on Excel Macros presented on the website www.excel-vba.com.

First Method: List if Values

Step 1: Open a new workbook and select cell C1 in the first worksheet.

Step 2:
In Excel 2007 to 2010: Select the "Data" ribbon and choose "Data/Validation" the following window appears:

In Excel prior to 2007: Go to the menu bar and select "Data/Validation" the following window appears:

Step 3: In the "Allow" text box select "List" and then the "Source" text box appears:

Step 4: In the "Source:" text box write YES,NO separated by a comma and click on "OK" . You now have a drop-down list in cell C1. When you select cell C1 a small arrow appears on the right from which you can select either "YES" or "NO.

Second Method: Range of cells as list of values

Step 1: In cells A1 to A3 enter a list of company names. For example: ABC Transport Inc. in A1, Louisville Wood Furnishing in A2 and Kirkland Signature Products in A3. Notice how long it took you to enter these values without typos.

Step 2: Select cell B1

Step 3:
In Excel 2007 to 2010: Select the "Data" ribbon and choose "Data/Validation" the following window appears:

In Excel prior to 2007: Go to the menu bar and select "Data/Validation" the following window appears:

Step 4: In the "Allow" text box select "List" and then the "Source" text box appears:

Step 5: In the "Source" text box submit the range of cells where you maintain your list preceded by an equal sign (=A1:A3) and click "OK". You now have a drop-down list in cell B1 from which you can select one of three company names, you don't have to enter them manually and you avoid typos..

Canceling the Drop-Down List

If you want to cancel the drop-down list that you have created previously in cells C1 and B1 follow the steps below

Step 1:  Select the cell with the drop-down list

Step 2:
In Excel 2007 to 2010: Select the "Data" ribbon and choose "Data/Validation" the following window appears:

In Excel prior to 2007: Go to the menu bar and select "Data/Validation" the following window appears:

Step 3:  Click on "Clear All" and then on "OK"

Excel drop-down lists are used extensively when there is a need for users to enter data in an Excel database. It insures that the values are valid and that the spelling is right. DDLists protect the integrity of the database.

Excel drop-down lists are also used to develop questionnaires that users complete. By using drop-down lists a valid answer is always supplied because the drop-down list limits them to a choice of preset answers.

There are  five sections in this website

Section 1: About Databases: 4 Lessons
In this section we review the basic notions on databases and evaluate Excel as a database. You will then see how Excel can be used downstream from most centralized databases and data management programs. Finally we will discuss the "upgrade" from Excel to Access..
Section 2: Excel and External Data: 5 Lessons
There are many ways to get data into your Excel Workbooks and there are many ways to easily extract data from all the databases in your organization and the Internet. This section is centered on querying data
Section 3: Analyzing Data with Excel: 8 Lessons
Once you have the data in the right formats within your Excel workbook it is time to use the powerful analytical tools of Excel including the database functionalities (sort, filter, subtotals), the integrated calculator, the comparative analysis tools and the mysterious and not so complicated pivot tables. 
Section 4: Reporting with Excel: 7 Lessons
The report is a communication tool and you can not accept to compromise on its presentation. Excel is the best reporting tool allowing you to easily develop popular dashboards and to share these reports with multiple colleagues.
Section 5: Other Functionalities in Excel: 3 Lessons
In this fifth section you will review the useful CTRL and ALT keystrokes, the conditional formatting, the drop-down lists and other interesting functionalities.