Drop Down Lists in Excel

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

 

Excel Chapter 1B of 24: Excel Drop-Down Lists and Data Validation

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) section.

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:

Excel Data Valition General

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

Excel data validation list

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

Second method:

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.

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

See the easy way to do in "excel-tutorial-ddlists.xls". Learn also how to delete, maintain and extend drop-down lists.

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 products. When you select the client his address appears automatically in the other cells.

See how to do this with detailed explanations in "excel-template-invoice.xls"

 

Next Chapter: CTRL Keys in Excel

Excel Table of Contents
or use the site quick links below

 

Discover Even More in 50 Excel spreadsheets

 

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Drop Down Lists in Excel