Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Creating Dependent Drop-Lists.
by Allen Wyatt
(last updated November 29, 2017)
Carol asked if there is a way in Excel to create drop-down lists so that the second drop-down list is dependent on the selection made in the first drop-down list.
There are actually a number of different ways you can accomplish this task, ranging from simple formulas to complex macros. The method you choose depends, most directly, on the type of drop-down lists you want to create. There are actually three types of drop-down lists you can create in Excel:
Rather than discuss how to create dependent drop-lists based on each of these types of drop-down lists, I'll choose to examine the simplest method, which will suffice for most people. If you use the INDIRECT function along with data validation lists, it is quite easy to get the result you want:
Figure 1. The Data Validation dialog box.
That's it. Now people can only select from your major list if they are using one of the cells specified in step 6, and from the appropriate dependent lists if they choose one of the cells in step 12.
There are lots of different variations of this approach (using data validation). You can find more information on some of these approaches by visiting these Web pages:
http://www.ozgrid.com/download/ (download the MatchingLists.zip file) http://www.contextures.com/xlDataVal02.html
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10545) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Creating Dependent Drop-Lists.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Want to have a small help screen pop up when a user selects a particular cell? This can be done by using data validation, ...Discover More
Want to limit what a person can enter into a particular cell? You can use Excel's data validation feature to help enforce ...Discover More
When using data validation, you might want to have Excel display a message when someone starts to enter information into ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.