Written by Allen Wyatt (last updated November 3, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003
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.
=INDIRECT(A3)
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
Excel can talk to you, reading back whatever you enter into a cell. If you want to turn this capability off, you'll want ...
Discover MoreYou've turned on Highlight Changes, but how do you know what has been changed? This tip explains how Excel displays those ...
Discover MoreAutoFill can be a real timesaver if you often work with set lists of data. You can define your own custom lists and then ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments