Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Creating Dependent Drop-Lists.
by Allen Wyatt
(last updated November 3, 2020)
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 11.
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 (2972) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Creating Dependent Drop-Lists.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
You've turned on Highlight Changes, but how do you know what has been changed? This tip explains how Excel displays those ...Discover More
List boxes can be a great tool for getting input from users of your worksheets. This tip describes what list boxes are ...Discover More
Check boxes, just like those used in Windows dialog boxes, can be a great addition to a worksheet. Here's how to add them ...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 menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.