How to Create a Drop Down List in Excel (the Only Guide You ...

Select a cell where you want to create the drop down list. · Go to Data –> Data Tools –> Data Validation. · In the Data Validation dialogue box, within the ... SkiptocontentHowtoCreateaDropDownListinExcel(theOnlyGuideYouNeed)--BySumitBansalFREEEXCELTIPSEBOOK-ClickheretogetyourcopyAdrop-downlistisanexcellentwaytogivetheuseranoptiontoselectfroma pre-definedlist.Itcanbeusedwhilegettingausertofillaform, orwhilecreatinginteractiveExceldashboards.Drop-downlistsarequitecommononwebsites/appsandareveryintuitivefortheuser.WatchVideo–CreatingaDropDownListinExcelInthistutorial,you’lllearnhowtocreateadropdownlistinExcel(ittakesonlyafewsecondstodothis)alongwithalltheawesomestuffyoucandowithit.ThisTutorialCovers:HowtoCreateaDropDownListinExcel#1UsingDatafromCells#2ByEnteringDataManually#3UsingExcelFormulasCreatingaDynamicDropDownListinExcel(UsingOFFSET)CopyPastingDrop-DownListsinExcelCautionwhileWorkingwithExcel DropDownListHowtoSelectAllCellsthathaveaDropDownListinitCreatingaDependent/ConditionalExcelDropDownListHowtoCreateaDropDownListinExcelInthissection,youwilllearntheexactsstepstocreateanExceldrop-downlist:UsingDatafromCells.EnteringDataManually.UsingtheOFFSETformula.#1UsingDatafromCellsLet’ssayyouhavealistofitems asshownbelow:HerearethestepstocreateanExcelDropDownList:Selectacellwhereyouwanttocreatethedropdownlist.GotoData–>DataTools–>DataValidation.IntheDataValidationdialoguebox,withintheSettingstab,selectListastheValidationcriteria.AssoonasyouselectList,thesourcefieldappears.Inthesourcefield,enter =$A$2:$A$6,orsimplyclickintheSourcefieldandselectthecellsusingthemouseandclickOK.ThiswillinsertadropdownlistincellC2.MakesurethattheIn-celldropdownoptionischecked(whichischeckedbydefault).Ifthisoptioninunchecked,thecelldoesnotshowadropdown,however,youcanmanuallyenterthevaluesinthelist.Note:Ifyouwanttocreatedropdownlistsinmultiplecellsatonego,selectallthecellswhereyouwanttocreateitandthenfollowtheabovesteps.Makesurethatthecellreferencesareabsolute(suchas$A$2)andnotrelative(suchas A2,orA$2,or$A2).#2ByEnteringDataManuallyIntheaboveexample,cellreferencesareusedintheSourcefield.Youcanalsoadditemsdirectlybyenteringitmanuallyinthesourcefield.Forexample,let’ssayyouwanttoshowtwooptions,YesandNo,inthedropdowninacell.Hereishowyoucandirectlyenteritinthedatavalidationsourcefield:Selectacellwhereyouwant tocreatethedropdownlist(cellC2inthisexample).GotoData–>DataTools–>DataValidation.IntheDataValidationdialoguebox,withintheSettingstab,selectListastheValidationcriteria.AssoonasyouselectList,thesourcefieldappears.Inthesourcefield,enterYes,NoMakesurethattheIn-celldropdownoptionischecked.ClickOK.Thiswillcreateadrop-downlistintheselectedcell.Alltheitemslistedinthesourcefield,separatedbyacomma,arelistedindifferentlinesinthedropdownmenu.Alltheitems entered inthesourcefield,separatedbyacomma,aredisplayedindifferentlinesinthedropdownlist.Note:Ifyouwanttocreatedropdownlistsinmultiplecellsatonego,selectallthecellswhereyouwanttocreateitandthenfollowtheabovesteps.#3UsingExcelFormulasApartfromselectingfromcellsandenteringdatamanually,youcanalsouseaformulainthesourcefieldtocreateanExceldropdownlist.Anyformulathatreturnsalistofvaluescanbeusedtocreateadrop-downlistinExcel.Forexample,supposeyouhavethedatasetasshownbelow:HerearethestepstocreateanExceldropdownlistusingtheOFFSETfunction:Selectacellwhereyouwanttocreatethedropdownlist(cellC2inthisexample).GotoData–>DataTools–>DataValidation.IntheDataValidationdialoguebox,withintheSettingstab,selectListastheValidationcriteria.AssoonasyouselectList,thesourcefieldappears.IntheSourcefield,enterthefollowingformula:=OFFSET($A$2,0,0,5)MakesurethattheIn-celldropdownoptionischecked.ClickOK.Thiswillcreateadrop-downlistthatlistsallthefruitnames(asshownbelow).Note:Ifyouwanttocreateadrop-downlistinmultiplecellsatonego,selectallthecellswhereyouwanttocreateitandthenfollowtheabovesteps.Makesurethatthecellreferencesareabsolute(suchas$A$2)andnotrelative(suchas A2,orA$2,or$A2).HowthisformulaWorks??Intheabovecase,weusedanOFFSETfunctiontocreatethedropdownlist.ItreturnsalistofitemsfromtheraItreturnsalistofitemsfromtherangeA2:A6.HereisthesyntaxoftheOFFSETfunction:=OFFSET(reference,rows,cols,[height],[width])Ittakesfivearguments,wherewespecifiedthereferenceasA2(thestartingpointofthelist).Rows/Colsarespecifiedas0aswedon’twanttooffsetthereferencecell.Heightisspecifiedas5astherearefiveelementsinthelist.Now,whenyouusethisformula,itreturnsanarray thathasthelistofthefivefruitsinA2:A6. Notethatifyouentertheformulainacell,selectitandpressF9,youwouldseethatitreturnsanarrayofthefruitnames.CreatingaDynamicDropDownListinExcel(UsingOFFSET)Theabovetechniqueofusingaformulatocreateadropdownlistcanbeextendedtocreateadynamicdropdownlistaswell.IfyouusetheOFFSETfunction,asshownabove,evenifyouaddmoreitemstothelist,thedropdownwouldnotupdateautomatically.Youwillhavetomanuallyupdateiteachtimeyouchangethelist.Hereisawaytomakeitdynamic(andit’snothingbutaminortweakintheformula):Selectacellwhereyouwanttocreatethedropdownlist(cellC2inthisexample).GotoData–>DataTools–>DataValidation.IntheDataValidationdialoguebox,withintheSettingstab,selectListastheValidationcriteria.AssoonasyouselectList,thesourcefieldappears.Inthesourcefield,enterthefollowingformula:=OFFSET($A$2,0,0,COUNTIF($A$2:$A$100,”<>”))MakesurethattheIn-celldropdownoptionischecked.ClickOK.Inthisformula,I havereplacedtheargument5withCOUNTIF($A$2:$A$100,”<>”).TheCOUNTIFfunctioncountsthenon-blankcellsintherangeA2:A100. Hence,theOFFSETfunction adjustsitselftoincludeallthenon-blankcells.Note:Forthistowork,theremustNOTbeanyblankcellsinbetweenthecellsthatarefilled.Ifyouwanttocreateadrop-downlistinmultiplecellsatonego,selectallthecellswhereyouwanttocreateitandthenfollowtheabovesteps.Makesurethatthecellreferencesareabsolute(suchas$A$2)andnotrelative(suchas A2,orA$2,or$A2).CopyPastingDrop-DownListsinExcelYoucancopypastethecellswithdatavalidationtoothercells,anditwillcopythedatavalidationaswell.Forexample,ifyouhaveadrop-downlistincellC2,andyouwanttoapplyittoC3:C6aswell,simplycopythecellC2andpasteitinC3:C6.Thiswillcopythedrop-downlistandmakeitavailableinC3:C6(alongwiththedropdown,itwillalsocopytheformatting).Ifyouonlywanttocopythedropdownandnottheformatting,herearethesteps:Copythecellthathasthedropdown.Selectthecellswhereyouwanttocopythedropdown.GotoHome–>Paste–>PasteSpecial.InthePasteSpecialdialoguebox,selectValidationinPasteoptions.ClickOK.Thiswillonlycopythedropdownandnottheformattingofthecopiedcell.CautionwhileWorkingwithExcel DropDownListYouneedtotobecarefulwhenyouareworkingwithdropdownlistsinExcel.Whenyoucopyacell(thatdoesnotcontainadropdownlist)overacellthatcontainsadropdownlist,thedropdownlistislost.TheworstpartofthisisthatExcelwillnotshowanyalertorprompttolettheuserknowthatadropdownwillbeoverwritten.HowtoSelectAllCellsthathaveaDropDownListinitSometimes,it‘shardtoknowwhichcellscontainthedropdownlist.Hence,itmakessensetomarkthesecellsbyeithergivingitadistinctborderorabackgroundcolor.Insteadofmanuallycheckingallthecells,thereisaquickwaytoselectallthecellsthathavedrop-downlists(oranydatavalidationrule)init.GotoHome–>Find&Select–>GoToSpecial.IntheGoToSpecialdialoguebox,selectDataValidationDatavalidationhastwooptions:AllandSame.Allwouldselectallthecellsthathaveadatavalidationruleappliedonit.Samewouldselectonlythosecellsthathavethesamedatavalidationruleasthatoftheactivecell.ClickOK.Thiswouldinstantlyselectallthecellsthathaveadatavalidationruleappliedtoit(thisincludesdropdownlistsaswell).Nowyoucansimplyformatthecells(giveaborderorabackgroundcolor)sothatvisuallyvisibleandyoudon’taccidentallycopyanothercellonit.HereisanothertechniquebyJonAcamporayoucanusetoalways keepthedropdownarrowiconvisible.YoucanalsoseesomewaystodothisinthisvideobyMr.Excel.CreatingaDependent/ConditionalExcelDropDownListHereisavideoonhowtocreateadependentdrop-downlistinExcel.Ifyoupreferreadingoverwatchingavideo,keepreading.Sometimes,youmayhavemorethanonedrop-downlistand youwanttheitemsdisplayedintheseconddropdowntobedependentonwhattheuserselectedinthefirstdrop-down.Thesearecalleddependentorconditionaldropdownlists.Belowisanexampleofaconditional/dependentdropdownlist:Intheaboveexample,whentheitemslistedin‘DropDown2’aredependentontheselectionmadein‘DropDown1’.Nowlet’sseehowtocreatethis.Herearethestepstocreateadependent/conditionaldropdownlistinExcel:Selectthecellwhereyouwantthefirst(main)dropdownlist.GotoData–>DataValidation.Thiswillopenthedatavalidationdialogbox.Inthedatavalidationdialogbox,withinthesettingstab,selectList.InSourcefield,specifytherangethatcontainstheitemsthataretobeshowninthefirstdropdownlist.ClickOK.ThiswillcreatetheDropDown1.Selecttheentiredataset(A1:B6inthisexample).GotoFormulas–>DefinedNames–>CreatefromSelection(oryoucanusethekeyboardshortcutControl+Shift+F3).Inthe‘CreateNamedfromSelection’dialogbox,checktheToprowoptionanduncheck alltheothers.Doingthiscreates2namesranges(‘Fruits’and‘Vegetables’).FruitsnamedrangereferstoallthefruitsinthelistandVegetablesnamedrangereferstoallthevegetablesinthelist.ClickOK.SelectthecellwhereyouwanttheDependent/ConditionalDropDownlist(E3inthisexample).GotoData–>DataValidation.IntheDataValidationdialogbox,withinthesettingtab,makesureListinselected.IntheSourcefield,entertheformula=INDIRECT(D3).Here,D3isthecellthatcontainsthemaindropdown.ClickOK.Now,whenyoumaketheselectioninDropDown1,theoptionslistedinDropDownList2wouldautomaticallyupdate.DownloadtheExampleFileHowdoesthiswork?– Theconditionaldropdownlist(incellE3)refersto=INDIRECT(D3).Thismeansthat whenyouselect‘Fruits’incellD3,thedropdownlistinE3referstothenamedrange‘Fruits’(throughtheINDIRECTfunction)andhencelistsalltheitemsinthatcategory.ImportantNoteWhileWorkingwithConditionalDropDownListsinExcel:Whenyouhavemadetheselection,andthenyouchangetheparentdropdown,thedependentdropdownwouldnotchangeandwould,therefore,beawrongentry.Forexample,ifyouselecttheUSasthecountryandthenselectFloridaasthestate,andthengobackandchangethecountrytoIndia,thestatewouldremainasFlorida.HereisagreattutorialbyDebraonclearingdependent(conditional)dropdownlistsinExcelwhentheselectionischanged.Ifthemaincategoryismorethanoneword(forexample,‘SeasonalFruits’insteadof‘Fruits’),thenyouneedtousetheformula =INDIRECT(SUBSTITUTE(D3,”“,”_”)),insteadofthesimpleINDIRECTfunctionshownabove.ThereasonforthisisthatExceldoesnotallowspacesinnamedranges.Sowhenyoucreateanamedrangeusingmorethanoneword,Excelautomaticallyinsertsanunderscoreinbetweenwords.So‘SeasonalFruits’namedrangewouldbe‘Seasonal_Fruits’.UsingtheSUBSTITUTEfunctionwithintheINDIRECTfunctionmakessurethatspaces areconvertedintounderscores. YouMayAlsoLiketheFollowingExcelTutorials:ExtractDatafromDropDownListSelectioninExcel.SelectMultipleItemsfromaDropDownListinExcel.CreatingaDynamicExcelFilterSearchBox.DisplayMainandSubcategoryinDropDownListinExcel.HowtoInsertCheckboxinExcel.UsingaRadioButton(OptionButton)inExcel.FREEEXCELBOOKGet51ExcelTipsEbooktoskyrocketyourproductivityandgetworkdonefasterNameEmailYES-SENDMETHEEBOOK39thoughtson“HowtoCreateaDropDownListinExcel(theOnlyGuideYouNeed)”howtodownloadcalculationotformulaWhenItrytouseanOFFSETformulainmySourcefieldinDataValidation,I’mgettinganerrormessage(“there’saproblemwiththisformula…”)eventhoughtheformulaworkscorrectlywheninacellbyitself.Anyideaswhythiswouldhappen?Howdoyoucreateadropdownlistwithdates?Forexample,November1,2020,November1,2021,November1,2022etc.Isthereanywaytomakearelationaldatabaseinexcelwhereicankeepentrycellsdifferentandlinkthemtoanothersetofentries.foreg:categoryinonetablelinkedtoproductsofferedinanothertable.Ifthereispleaseanswer?Itispossible.Iseetwooroptions:(a)fomulaicially,largelyusingreferencingfunctions,egindex,lookupetcwithlogicalfunctionsprobably,or(b)programaticallyusingExcelVBA.Aprogrammaticsolution,usingExcelVBA,wouldneedtobesupportedbyusingVBAequivalentsofspreadsheetfunctionsorwheretheydon’texistinExcelVBAusingtheExcelVBA“Application.Spreadsheetfunction…”methodtoaccessspreadsheetfunctions.Knowledgeofconditionalbranching,eg“If”,“SelectCase”andlooping,For/Next,“DoUntil”constructionswouldbeveryuseful,ifnotessentialinacheivingaprogrammaticsolution.InadditionyoucanmakeuseofVBAUserformsforeditingrecords,orcreatingaspreadsheet-basedformwhichwritesandreadsrecorddataitemsfromothersheetsofdatatables.IfusingeitherVBAUserFormoraspreadsheettemplatetodisplayrecordstheywouldneedtobeaccompaniedbyuserentrytextfields,buttons,dropdowns,etc(ControlsinVBAspeak)orcell(s)forfields,Shapesforbuttonsandspreadsheetdropdowns(datavalidation)cells.VBAbuttonsandspreadsheetswouldhaveVBAcodeassignedtothemthatwouldbeexecutedwhenthecuttonsareclicked.Ifollowedyourvideotodosomeassignment.Ididitverysuccessfulandmysupervisorwasveryhappy.Thank-youforthegoodworkyouhavedonebyputtingallthesteps.Hi,I’mworkingonanoffsetdropdownlistsothateachitemislistedonce.SayforexamplethatIhave10itemstoselect,acrossapossible20rows.Inoticedthatonce10rowsarefilledinwiththeitems,thedropdowndoesn’tshowanyitemstoselect(asplanned)butI’mabletoincludeanyfreetextintheothervoidrows,effectivelybypassingthedropdownvalidation.WhatamIdoingwrong?thanks!Hello,IwaswonderinghoworifitisevenpossibletousetheDynamicDropDownListfunctiontoworkacrossdifferentsheets?TokeepmyspreadsheetscleanIkeepinfousedtopopulatethevariouspulldownlistsononesheetbutusethemonanother.ThankyouHiSumit,Yourvideosareveryhelpfulandyoumakeitveryeasyandcleartounderstand.KeepupthegreatworkandthankyouformakingExcellooknotnearlyasdauntingasIthoughtitwouldbeIwanttocreateadropdownlistwhereIhavefullnames,butwhenIchooseoneofthose,insteadofthecellshowsthefullname,itdisplaysonlythefirstletter.Forexample,thedropdownshowsJavier,butwhenIchoosethatthecellonlyshowsJ.HowcanIdothis?Thankyou.I’vegotonespreadsheetthathasahugenumberofcellsthathavedynamicdrop-downlists,anditisrunningpainfullyslow,whichIthinkiscausedbyhavingINDIRECTinthousandsofcells’validation.Isthereawaytogetadynamicdrop-downlistthatdoesn’tuseINDIRECTorOFFSET?I’vetriedusingINDEX():INDEX()formulasbutit’sjustthrowinganerror.WOWTHENKSQuestion,InourPetanqueclub,wehaveclubmatchesmemberAandBplayingagainstmembersXandY.CreatingasheetfortheresultsIcanuseinputdropdownlists,withallthemembers(200+)in,butoftenIknowthefirstname,butwhatwasthesurnameagain?IsthereawaytostarttypinginthelistsoitstartsfilteringalltheJohn’sorMary’sinthelist?Wouldhelpmealot,thanksGreatThistutorialisverycomprehensiveandveryhelpful.Domorevideos,wouldlovetosubscribetoyourtutorials.Goodjob!IHaveaquery,ihavedrop-down(pre&postasinputs)nowiwantthatwheniselect“pre”ishouldgetthevalue“x”andwheniselectpostishouldgetvalueasblank(liketotalemptyfield),canyousuggestanymethod.Thiswashelpful.Question:IfIwantanoptionwhichadds2itemsfromthelisttogether,howdoIdothat?Ipullindatafromaseparatesheetusingalistwhichupdatesmyformulas.IneedtoseePOS(pointofsale)numbersandAPOS(afterpointofsale)numbersseparatelyandbothaddedtogether.howdoIdothis?Hi,Ireallyenjoyedtheinstructionalvideosandhavecreatedmyowndynamicdropdownlist.Ihavetwoquestions:1.Isthereawaytohavethedropdownlistworkingwiththekeyboard?(EverytimeIpressthedownbuttonmyexcelworkbookcloses.)2.AftertypinginthenameIwanttosee,canIhavetheoptiontoscrolldown?Iwouldverymuchappreciatethehelp!thanksfortheexcelbookPl.addapdffiletoreadtheexceltrainingI’musingtheindirectfunctiontocreatedependentdropdownlists.WhydoIneedtoplaceanunderscore“_”infrontofsomeofmydropdownitemswithsimilarbutnottheexactname?Forexample,DV,DV0andDV30.Excelisforcingmetouse_DV0and_DV30asmydropdownitemsratherthansimplyDV0andDV30.‘DV30’couldbeacellreference(columnDV,row30)andyoucan’tgivearangeanamethatcouldalsobeacellreference.VERYHELPFULItwasexplainedinaverysimpleandclearmanner.Appreciatedit.ThankyouforthevideoThanksfortheexcellentworkVeryhelpfultome.ThanksDoesn’twork.Downloadedexampledoesnothingwhenyouselectfruits.FormulaisdifferentthanexampleonthispageforuseofINDIRECTIunderstandallthathasbeenwrittenaboveaboutindependentdropdownlists,worksgreat.TheproblemIhaveisdoingexactlythesamebutwithdifferentworkbooks.Ihaveusedtwoworkbooksforonedropdownanditworkswithnoproblem.Ihavealsotriedandindependentdropdowninthesameworkbookandthatalsoworksfine,butassoonasItrytodoanindependentlist,usingindirect,intwodifferentworkbooksitdoesnotwork.Ihavespentsomuchtimetryingtogetittowork…mylatestattemptisthatIhavetwodropdownsinmytargetfilethatbuttheyarenotindependent….canyouhelp!!!NiceHi,Iamcreatingarotawheredifferenttasks/assignmentsareselectedandIwanttousedropdownliststoselectnames.Theproblemis,becauseitisbasedonskilllevels,Iwantonlythosepeoplewhohavethoseskillstoshowoneachrelevantdropdownlist.ThesourceIwanttouseisanothersheetwithnamesandcheckboxesthatcanbetickedtosaytheyhavetherelevantskill/s.Idon’tknowhowtolinkthenames-tickboxes-dropdownlistsalltogether.Dearbrother,I’mverygratefultoyou.Veryhelpfulindeed!Thisisextremelyhelpful!Thankyousomuch!Doyouknowifthereisawaytokeeptheformattingfromtheoriginallistsothatitpopulatesthoseformatchangesintothelistswhentheyarecreated?Anexamplewouldbethatwhenanoptionischoseninthelist,thatcellisformattedredwhereotherscells(options)aredifferentornocolours.Ican’tseemtofigurethisoneout.Thankyou!Byfarthemosthelpfulwebsiteonthistopic!!HiHiCanyoumakethedropdownbiggerandreadable,.likeihavereallyalonglistofsuppliernamesanditsrathersmall,butwaswonderinganyideatomakethedropdownbigger.SerenahowdoIremoveanentryfromthedropdownlistselection?Iwouldliketobeabletoselectthesameentryagaintoremove.Essentiallyselectingagainshouldaddbacktheentry.PlskindlyhelpmewithallthenecessaryinfoonEXCELhowdoyouusedependentdropdownlistwiththefirstconditionbeingarangeofvalue?forexample,ifcellA2=100,D,E,Fwillappearondropdownlist?Thankyou…excellentexplanation.ButhowdoIprovideadropdownlistthatprovidestheoptiontocompletearesponse(e.g.aname)thatwasnotintheoriginallist)andbetterstill…havethisnameautomaticallyincludedinthedatabaseforfuturedropdowns?Consideralistofemployeesthatchanges,staffcomingin,goingout,beingabletoaddanameintheformratherthansearchingthelistmustbeanadvantageothershavesoughtVeryNice…..Commentsareclosed.BESTEXCELTUTORIALSBestExcelShortcutsConditionalFormattingCreatingaPivotTableExcelTablesINDEX-MATCHComboCreatingaDropDownListRecordingaMacroVBALoops©TrumpExcel.com–FreeOnlineExcelTrainingPrivacy Policy |Sitemap