Create a drop-down list - Microsoft Support

You can insert a drop-down list of valid entries (also called a drop-down menu or drop-down box) in Excel to make data entry easier, or to limit entries to ... Tableofcontents × IntrotoExcel Createanewworkbook Article Insertordeleteaworksheet Article Moveorcopyworksheetsorworksheetdata Article Printaworksheetorworkbook Article UseExcelasyourcalculator Article Filldataautomaticallyinworksheetcells Article Createadrop-downlist Article Next: Row&columns Skiptomaincontent Officesupport Products Microsoft365 Office Outlook MicrosoftTeams OneDrive OneNote Windows MicrosoftEdge more... Devices Surface PCaccessories Mobile Xbox HoloLens Hardwarewarranties What'snew InstallOffice Account&billing Account MicrosoftStore&billing Templates Moresupport Communityforums Microsoft365Admins SmallBusinessAdmins Developer Education Reportasupportscam Tableofcontents IntrotoExcel Createadrop-downlist ExcelHelp&Training IntrotoExcel IntrotoExcel Createadrop-downlist Createanewworkbook Article Insertordeleteaworksheet Article Moveorcopyworksheetsorworksheetdata Article Printaworksheetorworkbook Article UseExcelasyourcalculator Article Filldataautomaticallyinworksheetcells Article Createadrop-downlist Article Next: Row&columns Excel Enterandformatdata Datavalidation Datavalidation Createadrop-downlist ExcelforMicrosoft365ExcelforMicrosoft365forMacExcelforthewebExcel2021Excel2021forMacExcel2019Excel2019forMacExcel2016Excel2016forMacExcel2013Excel2010Excel2007More...Less Youcanhelppeopleworkmoreefficientlyinworksheetsbyusingdrop-downlistsincells.Drop-downsallowpeopletopickanitemfromalistthatyoucreate. Wanttobewalkedthroughthisprocess?Tryournewonlinetutorialfordrop-downlists(beta). WindowsmacOSWeb Inanewworksheet,typetheentriesyouwanttoappearinyourdrop-downlist.Ideally,you’llhaveyourlistitemsinanExceltable.Ifyoudon’t,thenyoucanquicklyconvertyourlisttoatablebyselectinganycellintherange,andpressingCtrl+T. Notes:  Whyshouldyouputyourdatainatable?Whenyourdataisinatable,thenasyouaddorremoveitemsfromthelist,anydrop-downsyoubasedonthattablewillautomaticallyupdate.Youdon'tneedtodoanythingelse. NowisagoodtimetoSortdatainarangeortableinyourdrop-downlist. Selectthecellintheworksheetwhereyouwantthedrop-downlist. GototheDatatabontheRibbon,thenDataValidation. Note: Ifyoucan’tclickDataValidation,theworksheetmightbeprotectedorshared.Unlockspecificareasofaprotectedworkbookorstopsharingtheworksheet,andthentrystep3again. OntheSettingstab,intheAllowbox,clickList. ClickintheSourcebox,thenselectyourlistrange.WeputoursonasheetcalledCities,inrangeA2:A9.Notethatweleftouttheheaderrow,becausewedon'twantthattobeaselectionoption: Ifit’sOKforpeopletoleavethecellempty,checktheIgnoreblankbox. ChecktheIn-celldropdownbox. ClicktheInputMessagetab. Ifyouwantamessagetopopupwhenthecellisclicked,checktheShowinputmessagewhencellisselectedbox,andtypeatitleandmessageintheboxes(upto225characters).Ifyoudon’twantamessagetoshowup,clearthecheckbox. ClicktheErrorAlerttab. Ifyouwantamessagetopopupwhensomeoneenterssomethingthat'snotinyourlist,checktheShowerroralertafterinvaliddataisenteredbox,pickanoptionfromtheStylebox,andtypeatitleandmessage.Ifyoudon’twantamessagetoshowup,clearthecheckbox. NotsurewhichoptiontopickintheStylebox? Toshowamessagethatdoesn’tstoppeoplefromenteringdatathatisn’tinthedrop-downlist,clickInformationorWarning.InformationwillshowamessagewiththisiconandWarningwillshowamessagewiththisicon. Tostoppeoplefromenteringdatathatisn’tinthedrop-downlist,clickStop. Note: Ifyoudon'taddatitleortext,thetitledefaultsto"MicrosoftExcel"andthemessageto:"Thevalueyouenteredisnotvalid.Auserhasrestrictedvaluesthatcanbeenteredintothiscell." Workingwithyourdrop-downlist Afteryoucreateyourdrop-downlist,makesureitworksthewayyouwant.Forexample,youmightwanttochecktoseeifChangethecolumnwidthandrowheighttoshowallyourentries. Ifthelistofentriesforyourdrop-downlistisonanotherworksheetandyouwanttopreventusersfromseeingitormakingchanges,considerhidingandprotectingthatworksheet.Formoreinformationabouthowtoprotectaworksheet,seeLockcellstoprotectthem. Ifyoudecideyouwanttochangetheoptionsinyourdrop-downlist,seeAddorremoveitemsfromadrop-downlist. Todeleteadrop-downlist,seeRemoveadrop-downlist. Downloadourexamples Youcandownloadanexampleworkbookwithmultipledatavalidationexamplesliketheoneinthisarticle.Youcanfollowalong,orcreateyourowndatavalidationscenarios.DownloadExceldatavalidationexamples. Dataentryisquickerandmoreaccuratewhenyourestrictvaluesinacelltochoicesfromadrop-downlist. Startbymakingalistofvalidentriesonasheet,andsortorrearrangetheentriessothattheyappearintheorderyouwant.Thenyoucanusetheentriesasthesourceforyourdrop-downlistofdata.Ifthelistisnotlarge,youcaneasilyrefertoitandtypetheentriesdirectlyintothedatavalidationtool. Createalistofvalidentriesforthedrop-downlist,typedonasheetinasinglecolumnorrowwithoutblankcells. Selectthecellsthatyouwanttorestrictdataentryin. OntheDatatab,underTools,clickDataValidationorValidate. Note: Ifthevalidationcommandisunavailable,thesheetmightbeprotectedortheworkbookmaybeshared.Youcannotchangedatavalidationsettingsifyourworkbookissharedoryoursheetisprotected.Formoreinformationaboutworkbookprotection,seeProtectaworkbook. ClicktheSettingstab,andthenintheAllowpop-upmenu,clickList. ClickintheSourcebox,andthenonyoursheet,selectyourlistofvalidentries. Thedialogboxminimizestomakethesheeteasiertosee. PressRETURNorclicktheExpand  buttontorestorethedialogbox,andthenclickOK. Tips:  YoucanalsotypevaluesdirectlyintotheSourcebox,separatedbyacomma. Tomodifythelistofvalidentries,simplychangethevaluesinthesourcelistoredittherangeintheSourcebox. Youcanspecifyyourownerrormessagetorespondtoinvaliddatainputs.OntheDatatab,clickDataValidationorValidate,andthenclicktheErrorAlerttab. Seealso Applydatavalidationtocells Inanewworksheet,typetheentriesyouwanttoappearinyourdrop-downlist.Ideally,you’llhaveyourlistitemsinanExceltable. Notes:  Whyshouldyouputyourdatainatable?Whenyourdataisinatable,thenasyouaddorremoveitemsfromthelist,anydrop-downsyoubasedonthattablewillautomaticallyupdate.Youdon'tneedtodoanythingelse. NowisagoodtimetoSortyourdataintheorderyouwantittoappearinyourdrop-downlist. Selectthecellintheworksheetwhereyouwantthedrop-downlist. GototheDatatabontheRibbon,thenclickDataValidation. OntheSettingstab,intheAllowbox,clickList. Ifyoualreadymadeatablewiththedrop-downentries,clickintheSourcebox,andthenclickanddragthecellsthatcontainthoseentries.However,donotincludetheheadercell.Justincludethecellsthatshouldappearinthedrop-down.YoucanalsojusttypealistofentriesintheSourcebox,separatedbyacommalikethis: Fruit,Vegetables,Grains,Dairy,Snacks Ifit’sOKforpeopletoleavethecellempty,checktheIgnoreblankbox. ChecktheIn-celldropdownbox. ClicktheInputMessagetab. Ifyouwantamessagetopopupwhenthecellisclicked,checktheShowmessagecheckbox,andtypeatitleandmessageintheboxes(upto225characters).Ifyoudon’twantamessagetoshowup,clearthecheckbox. ClicktheErrorAlerttab. Ifyouwantamessagetopopupwhensomeoneenterssomethingthat'snotinyourlist,checktheShowAlertcheckbox,pickanoptioninType,andtypeatitleandmessage.Ifyoudon’twantamessagetoshowup,clearthecheckbox. ClickOK. Afteryoucreateyourdrop-downlist,makesureitworksthewayyouwant.Forexample,youmightwanttochecktoseeifChangethecolumnwidthandrowheighttoshowallyourentries.Ifyoudecideyouwanttochangetheoptionsinyourdrop-downlist,seeAddorremoveitemsfromadrop-downlist.Todeleteadrop-downlist,seeRemoveadrop-downlist. Needmorehelp? YoucanalwaysaskanexpertintheExcelTechCommunity orgetsupportintheAnswerscommunity. Seealso Applydatavalidationtocells AllExcelfunctions(bycategory) Creatingdrop-downlists(freepreview) Needmorehelp? Expandyourskills ExploreTraining Getnewfeaturesfirst JoinMicrosoftInsiders Wasthisinformationhelpful? Yes No Thankyou!Anymorefeedback?(Themoreyoutellusthemorewecanhelp.) Canyouhelpusimprove?(Themoreyoutellusthemorewecanhelp.) Whataffectedyourexperience? Resolvedmyissue Clearinstructions Easytofollow Nojargon Pictureshelped Other Didn'tmatchmyscreen Incorrectinstructions Tootechnical Notenoughinformation Notenoughpictures Other Anyadditionalfeedback?(Optional) Submitfeedback Thankyouforyourfeedback! ×