Easy Steps Excel Dependent Drop Down List Data Validation

What Is a Dependent Drop Down List? In an Excel dependent drop down list, the list of items changes, depending on the value in another cell. Togglenavigation Home Tips Files Products Newsletter Blog CreateDependentDropDownLists EasystepsforExceldependentdropdownlist.Conditionaldatavalidationbasedonothercell,likeRegionandCitylists.Videos,writtensteps,getfreeExcelfile. WhatIsaDependentDropDownList? Video:DependentDropDownLists GetStarted:DependentDropDownLists Setup2sheetsintheworkbook Createlistsforthedropdowns Setupthemaindropdownlist Addthedependentdropdownlists Testthedropdowns AdvancedDependentDropDowns UseTwo-WordItems UseIllegalCharacters Adda3rdDependentDropDown UseDynamicSourceLists DependentDropDownIFFormula ShowLongorShortList PreventInvalidSelections DownloadtheSampleFiles WhatIsaDependentDropDownList? InanExceldependentdropdownlist,thelistofitemschanges,dependingonthevalue inanothercell. Forexample: FruitwasselectedasProduceTypeincellB3,andthedropdownshowedtheFruitlistincellD3 Next,incellB4,VegetablewasselectedasProduceType.IncellC4,thedependentdropdownlistchangedautomatically,andnowitshowstheVegetablelist Therearesetupinstructionsandavideointhesectionsbelow. Video:DependentDropDownLists Inthisshortvideo,you'llseehowtosetupamaindropdownlist,withadependentdropdownlistinthenextcolumn.Thewritteninstructionsarebelow thevideo,andthefulltranscriptisontheDependentDropDownListsVideopage. GetStarted:DependentDropDownLists Herearethestep-by-stepinstructionsformakingdependentdropdownlistsinExcel.ThisexampleusesFruitandVegetablelists. Inthescreenshotbelow,youcanseethecompleteddataentrysheet. Selectaproducetype(fruitorvegetable)incolumnB. Then,incolumnC,adependentdropdownlistshowitemsfortheselectedproducttypeonly. Thereareafewstepsforsettingupthedropdownlists,andthedetailsareinthesectionsbelow. Setup2sheetsintheworkbook Createlistsforthedropdowns Setupthemaindropdownlist Addthedependentdropdownlists Testthedropdowns SetUptheWorksheets Thisexamplewillhaveaworkbookwithtwosheets--adataentrysheet,andasheetwithlists. Createanewworkbook ChangethenameforSheet1to"DataEntry" Insertanewsheetintheworkbook,andnamethatsheet"Lists" CreateLists Next,you'llcreatelistswithitemsforthedropdownlists. Inthisexample,you'llsetup3lists.Themainlisthasproducetypes,andtheothertwolistshaveitemsforthedependentdropdowns. Tocreateeachlist,you'lldothreethings: TypetheitemsontheListssheet FormatthelistsasanExceltable Namethelists. 1.TypetheLists Totypethelists,gototheListssheet.Startwiththemainlist-Producetypes. IncellB2,typethemainlistheading,ProduceList IncellsB3andB4,typeFruitandVegetable. NOTE:TheProducelisthasone-worditemsinit--FruitandVegetable.Thisisimportant,becausethosewordswillbeusedasExcelnames,andtwo-wordnamesarenotallowedasnames.Ifyouneedto usemultiple-worditemsinthemainlist,see:UsingTwoWordItems AddtheDependentLists Next,typethedependentlists,withtheheadingsFruitList(incellD2)andVegetableList(incellF2).Theselistscancontainone-worditems(apple),ormultiple-worditems(greenbeans). 2.FormatListsasTables Next,followthesesteps,toformateachlistasanExcelTable.Thismakesyourlistdynamic--thelistsizewilladjustautomaticallyifyouaddorremoveitems,soyourdropdownwillshowtheentirelist. Followthesestepsforeachofthe3lists: Selectacellinthelist,andontheHometab,clickFormatasTable ClickononeoftheTableStyles AddacheckmarkforMytablehasheaders ClickOK HereistheListssheet,withall3listsformattedasExcelTables CreateaNamedRange Next,followthesestepstocreateanamedrangeforeachformattedExcelTable..Later,youwillusethisname,whenmakingthedropdownlistsontheDataEntrysheet. NametheProduceList SelecttheitemsintheProducetable(cellsB3:B4),butnotthetableheading. ClickintheNamebox,totheleftoftheformulabar Typeaone-wordnameforthelist--Produce PresstheEnterkey,tocompletethename. NametheFruitandVegetableLists Next,followthesestepstonamethefruitandvegetablelists. SelectthefruitnamesincellsD3:D6 ClickintheNamebox,totheleftoftheformulabar Typeaone-wordnameforthelist--Fruit. PresstheEnterkey,tocompletethename. SelectthevegetablenamesincellsF3:F6. ClickintheNamebox,totheleftoftheformulabar Typeaone-wordnameforthelist--Vegetable. PresstheEnterkey,tocompletethename. AddtheMainDropDown Next,you'lladdthemaindropdown(ProduceType)ontheDataEntrysheet.Thisisjustanormaldatavalidationdropdownlist. StarttheDataEntrySheet First,you'llenterheadingsonthedataentrysheet,andsetupanamedtable. OntheDataEntrysheet,typetheheadingsinB2:C2--ProductTypeandItem SelectcellB2,andandontheHometab,clickFormatasTable ClickononeoftheTableStyles AddacheckmarkforMytablehasheaders ClickOK AddtheMainDropDown Next,you'llthemaindropdownlist,intheProduceTypecolumn OntheDataEntrysheet,selectcellB3 OntheRibbon,clicktheDatatab,thenclickDataValidation. FromtheAllowdrop-downlist,chooseList IntheSourcebox,typeanequalsignandthelistname:=Produce ClickOK,tocompletethedatavalidationsetup. Totestthedropdown,clickthearrowincellB3,andselectFruitfromthelist AddtheDependentDropDown Next,you'llcreateadependentdropdownlistintheItemcolumn.Thiscellwillhaveaformulatocreatethedatavalidationdropdownlist.ReadmoreabouttheINDIRECTfunctionhere. OntheDataEntrysheet,selectcellC3 OntheRibbon,clicktheDatatab,thenclickDataValidation.. FromtheAllowdrop-downlist,chooseList IntheSourcebox,typeanequalsignandINDIRECTfunction, referringtothefirstdatacellintheProduceTypecolumn: Inthisexample,cellC3isactive,sotheformulawillrefertothe ProductTypecellinrow3:=INDIRECT(B3) Note:IfyouclickoncellB3,Excelwilladdanabsolutereference--$B$3.Removethe$signs,becuseweneedarelativereferenceinthisformula ClickOK. Note:IfcellB3isempty,you'llseethemessageshownbelow. ClickYestocontinue. TesttheDropDownLists Totestthedependentdropdownlists,followthesesteps FruitshouldbeselectedincellB3--ifnot,selectitnow. SelectcellC3,thenclickthearrow,andselectoneofthefruititemsfromthelist Then,presstheTabkey,tostartanewrowinthedataentrytable. IntheProduceTypecolumn,selectVegetablefromtheProducedropdownlist. MovetotheItemcolumn, inthesamerow. IntheItemcolumn,clickthedropdownarrow,andselectanitemfromtheVegetablelist NOTE:IfaProduceTypehasnotbeenselected,theItemdropdown inthatrowwillnotwork. AdvancedDependentDropDowns Theinstructionsintheprevioussectionsshowhowtosetupabasicdependentdropdownlist.Hereareafewadvancedexamples. UseTwo-WordItems UseIllegalCharacters Adda3rdDependentDropDown UseDynamicSourceLists DependentDropDownIFFormula ShowLongorShortList UseTwo-WordItems Insomeworkbooks,youmightneedtohavetwo-worditemsinthefirst Exceldatavalidationdrop-downlist.Forexample,yourchoicesare'Red Fruit','GreenFruit'and'YellowFruit' Createthefirstnamedrangeanddropdownlistasdescribedabove. Createthesupportingnamedlists,usingone-wordnames,e.g.RedFruit, GreenFruit,YellowFruit Fortheseconddropdown,choosetoAllow:List,anduseaformula thatremovesthespacesfromthenames.Forexample:   =INDIRECT(SUBSTITUTE(A2,"","")) MainItemswithIllegalCharacters YoumayneeditemsinthefirstExceldatavalidationdrop-downlist thatcontaincharactersnotallowedinrangenames,suchastheampersand (&). Forexample,yourchoicesare'RedFruit','GreenFruit'and'Yellow &OrangeFruit'.Forthedependentlists,youcancreateranges withone-wordnames,suchasYOFruit.Then,createalookuptable, whichlistseachiteminthefirstExceldatavalidationdrop-downlist, andtherangewhereitsdependentitemswillbestored. Tostart,createtheitemlistsandthefirstExceldatavalidation drop-down: Createthefirstnamedrangeanddrop-downlistasdescribedabove. Inthisexample,therangeisnamedProductList,withvalues incellsA6:A8.Thedrop-downlistincellA2usesProductListas itssource. Createthedependentlists,andnamethem,usingone-wordnames, with"List"attheend,e.g.RedFruitList,GreenFruitList, YOFruitList.Inthisexample,RedFruitListisinA11:A12, YOFruitListisinA15:A16andGreenFruitListisinA19:20. Selectanitemfromthedrop-downlistincellA2. Next,you'llcreatethelookuptable,tomatcheachitemwithitsdependent items'rangename. InthecolumntotherightoftheFruitListrange,enterthecode nameforeachitem'sdependentlist.Forexample,YOFruitisentered asthecodenameforYellow&OrangeFruit. Namethelookuptable.Inthisexample,therangeA6:B8isnamed ProductLookup. SelectcellB2,andfromtheDatamenu,chooseValidation. ChoosetoAllow:List. FortheSource,enteraformulathatusesaVLookupformulatofind thedependentlist'srangename--acombinationofthecodename and"List". Forexample:  =INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"List") WithRedFruitselectedincellA2,theVLookupformulawill returnRedFruitListastherangenameforthedependentlist. TheRedFruitListitemswillbedisplayedincellB2'sdrop-down. Tocreatea3rddependentdropdownlist,usethesametechniques, tocreatelookuptablesanditemslists. Inthescreenshotbelow,therearelookuptablesnamedRedFruitLookup, YOFruitLookupandGreenFruitLookup.Itemslists,withthecodenames and"List",havebeenaddedtotheworksheet. Inthedatavalidationwindow,usethisformulaforthe3rddropdown: =INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup"),2,0)&"List") Theformulafindsthelookuptablebasedontheproducttypeselected incellA2,e.g.RedFruitLook,andgetsthecodefortheselectedproduct --Mac.Itadds"List"tothecode,andshowstheitemsin theMacListrange. AddaThirdDependentDropDown Ifyouneedto,youcanaddanothersetofdependentdatavalidation dropdownliststhatdependontheselectionsinthefirsttwodropdowns.selectacountryandregion,thenselectacityintheselectedcountryandregion. Notethattheregionnamesarenotunique--thereisaWestregioninbothCanadaandtheUSA--sowecan'tshowaCitylistthatisonlybasedontheregionnames. Tomakesurethatthecorrectlistofcitiesappears,thecitylistswillbenamedforthecountryandregion.Thereare2countries,andeachcountryhas3regions,sowewillsetup6namedrangesforthe3rdleveldropdownlists. NOTE:Ifyouneedmorethanafewnamedrangesforyour3rdlevellists,trytheDependentListswithTablestechniqueinstead--itwillbeeasiertosetupandmaintain. Tosetup3-leveldependentlists: Createthemainnamedranges-Country,CanadaandUSA,asdescribedabove. Createanothersetofnamedranges,namingthemfortheavailable combinationsinthefirsttwolists.For example,createrangesnamedCanadaWestandUSAWest. Onthedataentrysheet,setupdropdownlistsintheCountryandRegioncolumns. FortheCitydropdown,adddatavalidation,andchoosetoAllow:List Forthesource,useaformula thatcombinestheentriesinthefirsttwocolumns. Forexample,incellD2,thedatavalidation formulausesINDIRECTandSUBSTITUTE,tocombinethevaluesinB2andC2,andremoveanyspacecharacters:   =INDIRECT(SUBSTITUTE(B2&C2,"","")) UseDynamicLists BecausetheINDIRECTfunctiononlyworkswithreferences,notformulas, thepreviousmethodfordependentdatavalidationwon'tworkwithlists thatuseformula-baseddynamic namedranges,suchasOFFSETranges.Useoneofthefollowingsolutionswhenadynamiclistisrequired: NamedExcelTables(bestoption) DynamicNameWorkaround(ifyoucan'tusetables) NamedExcelTables Toavoidtheproblem,usenamedExceltables,insteadofformula-baseddynamicranges. Createatablewithyourlistitems Selectalltheitemsinthetablecolumn(nottheheading) ClickintheNameBox,typeaone-wordnametherange,andpressEnter Then,useoneoftheINDIRECTfunctionexamplesshownabove,tocreateadependentdropdownlist. DynamicNameWorkaround Ifyoucan'tusetheNamedExcelTables,usethefollowingmethodforcreatingdependentlistsfromformula-baseddynamicnamedranges: Createthefirstnamedrangeanddropdownlistasdescribedabove. Createthesupportingnamedlists,andnamethefirstcellineach range,e.g.cellB1isnamedFruitandcellC1isnamedVegetables. Namethecolumninwhicheachlistislocated,e.g.columnBis namedFruitColandcolumnCisnamedVegetablesCol Fortheseconddropdown,choosetoAllow:List,anduseaformula thatcalculatesthelookuprange.Forexample,ifthefirstdropdown listisincellE2: =OFFSET(INDIRECT($E2),0,0,COUNTA(INDIRECT(E2&"Col")),1) iftwo-worditemswillbeused,youcanincludetheSUBSTITUTEfunction intheformula: =OFFSET(INDIRECT(SUBSTITUTE($F2,"","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($F2," ","")&"Col")),1) DependentDropDownWithIFFormula ThisisanotherexampleofdependentdropdownlistsinExcel,withtheIFfunctionusedwithINDIRECT,foramoreflexibledropdown.Inthisexample: IfacountryisselectedincolumnA,onlythecitiesfromthatcountryareindependent dropdownlist IfNOcountryisselectedincolumnA,alistofworldcountriesisshowninthedependentdropdown Thisvideoshowsthebasicsetupsteps,andthenshowshowtousetheExcelIFfunctionwithadependentdropdown.Therearewrittenstepsbelowthevideo,andyoucandownloadthesamplefilebelow. SelectCountriesandCities Inthisexample,there’sadropdowntoselectacountryincolumnA.IfyouselectUSAasthecountry,citiesfromtheUSAappearinthedependentdropdowninColumnB. Therearecitylistsintheworkbook,andeachlistisnamedtomatchitscountryname. USA:cellsC2:C5 Canada:cellsE2:E4 SimpleDependentDropDownFormula Originally,therewasasimpleformulaforthedatavalidationlistincolumnB: =INDIRECT(A2) So,ifyouselectedUSAincellA2,dependentdropdownlistshowscitiesfromtherangenamedUSA. NoCountrySelected WiththatsimpleINDIRECTformula,thedropdownlistincolumnBdoesNOTwork,ifnocountryisselectedincolumnA. WhenyouclickonthedropdownarrowincellB3,nothinghappens. BecausecellA3isempty,theINDIRECTformulareturnsanerror,sothedropdowndoesnotfunction. AddIFFunctiontoFormula Insteadofadropdownarrowthatdoesn'twork,we'llchangethedependentdropdownformula,andaddtheIFfunction.TherevisedformulawillgiveuserstheoptiontoselectaWorldcity,iftheyhaven’tselectedacountryincolumnA. OntheListsworksheet,there’sanotherrange,namedWorld,highlightedinthescreenshotbelow. JustasyoucanusetheIFfunctionontheworksheet,youcanuseitinadatavalidationformula. ForthedependentdropdowncellsincolumnB,we’llchangetheformulatothefollowing: =IF(A2="",World,INDIRECT(A2)) Afterthischangetothedatavalidationformula,ifyouclickonadropdownarrowincolumnB,andnocountryisselectedincolumnA,thelistofworldcitiesappears. HowtheIFFormulaWorks Here'swhattherevisedformuladoes: First,theIFfunctionchecksthecellincolumnA,toseeifitisblank--equaltoanemptystring"" IFthecellisblank,thedatavalidationdropdownwillshowthelistfromtherangenamedWorld Otherwise,showthelistnamedforthecountryselectedincellA2,usingtheINDIRECTfunction ShowLongorShortDropDownList Withthisdependentdropdowntechnique,fromAlexJ,youcanseeafulllistofcustomersinadropdownlist.Or,switchtoashortlist,withjustyourtopcustomers.Thetechniqueisdrivenbyaformula--nomacrosrequired. Thisshortvideoshowsthesteps,andtherearewrittenstepsbelowthevideo. SetUptheLists Thefirststepistocreatetwonamedlists--theywillbeusedasthesourceforthedropdownlists. Typethefulllistofcustomers Selectallthecellsinthatlist,andnametherange:FullList Inanothercolumn,type"FullList"inrow2. Startinginrow3,typetheshortlistoftopcustomers Selectallthecellsinthatlist,includingthe"FullList"cell Namethatrange:ShortList CreatetheDropDownList Next,you'llcreatethedropdownlist,byusingdatavalidationwithanIFformula: Selectthecell(s)whereyouwantthedropdownlistofcustomers OntheRibbon,clicktheDatatab,thenclickDataValidation IntheDataValidationdialogbox,underAllow,selectList IntheSourcebox,typeanIFformulathatreferstotheactivecell,andthenamedlists. Ifyou'reusingthelistinasinglecell,useanabsolutereference =IF($E$3="FullList",FullList,ShortList) Formultiplecells,usearelativereference =IF(E3="FullList",FullList,ShortList) ClickOK,toclosethedialogbox. UsetheDropDownList Selectthecellwiththedatavalidationlist,andclickthedropdownarrow. Ifthecelldoesnotcontainthetext"FullList",thedropdownwillshowtheshortlistoftopcustomers. Ifyouneedtoseethefulllist,select"FullList"fromthetopofthedropdownlist,andthenclickthedropdownarrowagain. PreventInvalidSelections Aftersomeoneselectsanitemfromadependentdropdown,theycouldgobacktothemaindropdown,andselectadifferentitem.Thatcouldresultinmismatcheditemsinthatrow. Forexample,Fruitcouldbeselectedfromthemaindropdown,andthenLemoninthedependentdropdown.IfFruitischangedtoVegetablelater,theLemonselectionwouldbeincorrect--it'snotavegetable. Topreventinvalidselections,hereareacoupleoftechniquesthatyoucanuse Blockchangesinthemaindropdown(formula) Cleardependentcellifmaindropdownischanged(macro) BlockChangesinFirst DropDown Toblockchangestothefirstlist,youcanchangethedatavalidation formula,sothelistdoesnotappearunlessthesecondcellisempty. Thisvideoshowsyouhowthistechniqueworks,andwritten instructionsarebelowthevideo. BlockChangesinFirstDropDown Withdependentdropdownlists,problemscanoccur,ifsomeonegoes backtothefirstlist,andchangesit.Then,thefirstandsecondselections aremismatched--forexample,inthescreenshotbelow,Vegetableis thefirstchoice,andBananaisselectedinthesecondcolumn. Toblockchangestothefirstlist,youcanchangethedatavalidation formula,sothelistdoesn'tworkunlessthesecondcellisempty. InsteadofjustreferringtotheProducerangefortheProduceType dropdown,theformulawillcheckforanentryintheItemcolumn. IftheItemcellisblank,theProduceTypedropdownwillshow theProducelist Ifanitemhasbeenselected,thedropdownwilltrytoshowthe rangecreatedbytheINDIRECTfunction--"FakeRange".Because thereisnorangewiththatname,theresultisanerror,andthe dropdownwillnotwork. Tochangetheformula: Selectthedatavalidationcellsinthefirstcolumn OntheRibbon'sDatatab,clickDataValidation ForAllow,selectList IntheFormulabox,type: =IF(C2="",Produce,INDIRECT("FakeRange")) ClickOK Now,thedropdowninthefirstcolumnwon'tworkiftheItemhasbeen selectedinthatrow. ClearDependentCellAfterSelecting Intheprevioussection,thedatavalidationformulawasdesignedto preventselectionsfromthefirstdropdown,ifthecelltotheright containeddata. Anotheroptionistouseamacro,toclearthedependentcell,after makingaselectinginthefirstdropdown.Thatwillpreventmismatched selections. Inthisexample,columnBcontainsadropdownlistofRegions.After youselectaRegion,thedropdownlistincolumnCshowsthecustomers intheselectedregion. Gobackandselectadifferentregion,andtheexistingcustomername willbecleared. AddEventCodetoCleartheCustomerCell WhenyouselectaRegion,eventcodeclearsthecelltotheright. Toaddthecode,right-clickthesheettab,andclickViewCode. Pastethefollowingcodeontothesheetmodule.Youmightneedtochange thecolumnnumber,tomatchthecolumnnumberonyourworksheet. NOTE:Thesampleworkbookalsohascodeforclearingmultipledependentcells. PrivateSubWorksheet_Change _(ByValTargetAsRange) OnErrorResumeNext IfTarget.Column=2Then IfTarget.Validation.Type=3Then Application.EnableEvents=False Target.Offset(0,1).ClearContents EndIf EndIf exitHandler: Application.EnableEvents=True ExitSub EndSub DownloadSampleFiles Downloadthesamplefilefordependent datavalidationIntro-Fruit/Vegetableexample DownloadthesamplefilefordependentdropdownlistwithIF-City/Country/Worldexample Downloadthesamplefilefordependent datavalidationAdvanced-3rdlist;2-word/illegal Downloadthesamplefilefordependent datavalidationwithDynamicLists-Fruit/OtherStuff/Vegexample DownloadthesamplefileforClear DependentCellAfterSelecting DownloadthesamplefileforShortorLongDropDownListofCustomers. NOTE:Thereareothertechniquesforsettingupdependentdropdownlists,soseewhichsetupmethodmightbebestforyourneeds. MoreTutorials DataValidationBasics DataValidationIndexPage ChangeProductNametoCode DependentDropDownSetupChoices DependentDropDownfromDynamicArrays DependentDropDownListsVideo DependentDropdownsfromaSortedList DependentListsWithINDEX HidePreviouslyUsedItemsinaDropdownList UseaDataValidationListfromAnotherWorkbook DataValidationCriteriaExamples DataValidationTips DataValidationWithComboBox     DataValidationBasics DependentDropDownSetupChoices       Lastupdated:November24,20213:55PM