Other Links

This is one of the 30 lessons from the
Tutorial on Excel Formulas

The drop-down lists in Excel

Lesson 2-2: Excel Drop-Down Lists and Data Validation

Part 1:

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 lists presented below can be created by anybody on regular Excel worksheets. 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.

Finally DDLists  are used 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 " 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" . Now you can have a DDList  with M,F as  answers to the question "Sex" and  avoid answers like "3 times a week" .

NOTE: When you use addresses in the "Source" box (as in some examples below) and you want to copy/paste your DDlist make sure that you use the dollar signs ($) properly to make the addresses or part of them absolute. See lesson 3-4 on dollar signs that you can add with the F4 key.

Part 2:

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

Your list can be dynamic. For example if you list comprises 5 names you would submit range G1 to G6 and if you want to add a sixth name you will need to go back to your DDList and change the range. To avoid this irritant set the range to G1:G25 even if you have only 5 names. From then on you can add 20 names to your list and not have to change the DDLists.

Another irritant appears when you submit a range with many empty cells. When you click on the arrow of the DDList the first empty cell is selected and all the names might not be visible within the DDList  unless you use the scroll bar . To avoid this problem submit range G1:G25 but start entering names in G2. The problem is solved because when you click on the small arrow cell G1 is selected within the list and all the names are visible (unless you have more than 8 in which case you need to use the scroll bar.

Part 3:

Third method

In the source text box you can enter a the name of a named field (lesson 3-2) preceded by an equal sign. ex(=nfMyList). This approach will be indispensable if you want to set your lists on a sheet other than the sheet where the DDlist is. Indeed if you follow the procedure and try to go to another sheet when it is time to specify the source Excel doesn't allow you to move away from the sheet. So you first name the field on the other sheet and then submit it in the source as shown above. Don't forget the equal sign.

Part 4:

Cascading Drop-Down Lists

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

To achieve such a thing you first submit the list for the first DDlist in its source box. For the second DDList you will submit a formula like the following:
=IF(G4="Maple",A1:A12,B1:B12)
meaning that if the value in cell G4 is "Maple" then the second DDlist should feed on range A1 to A12 else it should feed on B1 to B12.

You can also work with named fields if your lists are maintained on another sheet. The formula will then look like this:
=IF(G4="Maple",nfMaple,nfOther)

This approach works if you have one to eight different lists to chose from because you cannot nest more than 7 IF's. The formula would look like this:
=IF(G4="Maple",nfMaple,IF(G4="Ash",nfAsh,IF(G4="Oak",nfOak,IF(G4="Pine",nfPine,
IF(G4="Spruce",nfSpruce,IF(G4="Cypress",nfCYpress,IF(G4="Cherry",nfCherry,nfOther)))))))

If you have more than 8 secondary lists you need to create a dynamic list on another sheet using INDEX/MATCH formulas. See the easy way to do it in "excel-tutorial-ddlists.xls".

Part 5:

Deleting, modifying and extending drop-down lists in Excel.

To delete a DDList you select the cell or cells, you go to "Data/Validation" on the menu bar and you click on "Clear All" and "OK". If you have selected many cells and some of them do not contain the DDList or  contain a different DDList you will see one of the following dialog window:

Click on OK or "Yes" and go on with "Clear All" and "OK"

To modify a DDList you select the cell or cells, you go to "Data/Validation" on the menu bar and you make the necessary changes in the "Source" box.

To extend a DDList you select one cell containing the DDList you set its value to nothing (Right click and Select "Clear Contents" and then you copy/paste as usual. But remember that when you use addresses in the "Source" box (as in some examples above) and you want to copy/paste your DDlist make sure that you use the dollar signs ($) properly to make the addresses or part of them absolute. See lesson 3-4 on dollar signs that you can add with the F4 key.

Part 6:

Applications developed with drop-down lists

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

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

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"