Free Microsoft Excel Tutorial - Cascading drop down lists

Microsoft Office Training > Excel Training > Free Computer Training > Cascading drop down lists using Data Validation

Please feel free to use this content on your web site, but please include the links at the end

Cascading drop down lists using Data Validation

Creating Cascading Drop Down Lists in Excel

Using Data Validation in Excel it is possible to create drop down lists in your worksheet.  This tutorial will explain how to create two drop down lists, the second of which will show values based on the value chosen in the first.

1. Start by creating a new workbook with at least two worksheets.  In the second worksheet create the following lists starting in A1

2. Name each list as follows, Location, London, Birmingham, Bristol.  The names you use have to be the same as the values in the location field. Do not include the column headers in the named ranges.

3. In the first worksheet, type the following, starting in A1

4. Now click into A2 and then click Data | Validation (Office 2003) or
Data Ribbon | Data Validation | Data Validation (Office 2007/10)

5. In the Data Validation box, select List from the Allow list.

6. Click into the Source box, press F3 on your keyboard and select Location from the Paste box. Click on OK in the Paste box and then OK in the Data Validation box.

7.  Select B2 and then click Data | Validation (Office 2003) or
Data Ribbon | Data Validation | Data Validation (Office 2007/10)

8. In the Data Validation box, select List from the Allow list.

9. Click into the Source box, and type =INDIRECT(A2)
The INDIRECT function returns the range reference represented by a text string, so in other words the location drop down list in A2 is being used as a list of range names.

10. Click on OK in the Data Validation box.

Your drop down list should now work properly.

NOTE: If your first drop down list contains values that contain spaces you will need to replace spaces with underscores to create valid references.  Use the substitute function if this is the case
 =INDIRECT(SUBSTITUTE(A2," ","_"))

Microsoft Office Training with Blue Pecan Computer Training. Tailored Microsoft Excel Training. Microsoft Excel Training Surrey