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. As for drop-down lists for programmers see the ComboBox in the VBA (macros) tutorial presented on the website www.excel-vba.com.

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 databases.

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

First method: Open a new workbook and select cell C1 in the first worksheet.

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

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

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

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 the user is expected to answer exclusively by "YES" or "NO".

Cells addresses as source for Excel drop down list

In the "Source" text box submit a range of cells where you maintain your list. ex(=$G$1:$G$23). Don't forget the equal sign (=) and the dollar signs($). If you don't enter the dollar signs and you copy your DDList it will offer values in range G1:G23 in the first one then G2:G24 in the cell below and G3:G25 in the next...

Learn more on this topic:

When selecting a type of wood for a floor in a first drop-down list one expects only the colors available for the type of wood selected to show in the second drop-down list not all the colors of all the types of wood. It is called a cascading drop-down list. See the easy way to do it in "excel-tutorial-ddlists.xls" one of the 25 spreadsheets included with the Tutorial.

Build a single report template and with SUMPRODUCT just select a branch, department, a city...among 50 and the report is AUTOMATICALLY refreshed. No more spreadsheets with 600 reports to maintain (12 months and 50 cities). In a dynamic report you can choose the city and you can choose the month and the Balance Sheet is AUTOMATICALLY refreshed. See how it is done in "excel-example-dynamic-report.xls" one of the 25 spreadsheets included with the Tutorial.

Here is an invoice where you select the name of the client and the name of the product. When you select the client his address appears automatically in the other cells when you select the product its number, and unit price appear in the other cells. No more mistakes, no more typos. See how to do this with detailed explanations in "excel-template-invoice.xls" one of the 25 spreadsheets included with the Tutorial.

Using the drop-down lists and the SUMPRODUCT function you can also do some dynamic charting where a single chart shows you alternatively the results for any department that you choose in the DDList. See "excel-example-dynamic-charts.xls" one of the 25 spreadsheets included with the Tutorial.

Excel Drop-Down Lists