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.
Written by Allen Wyatt (last updated August 2, 2021)
This tip applies to Excel 2007 and 2010
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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
It is not unusual to use Excel to gather the answers to users' questions. If you want your users to answer your questions ...Discover More
When using data validation, you may want to reference a list of validation criteria contained on a different worksheet. ...Discover More
Want to control what users put into a cell? It's easy to do using a feature called data validation, as described in this tip.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.