How to make multiple choice in Excelexcel drop down list教學Excel drop down list multiple selectiondrop-down list htmlExcel drop-down list editCreate drop-down list in ExcelExcel drop-down list conditionalChoose multiple options in excelExcel multiple item selection setupMultiple choice ExcelExcel drop down list autocompleteExcel drop down list formulaExcel dependent drop down list

Create Drop-down Lists in Excel (In Easy Steps)

文章推薦指數： 80 %
投票人數：10人

Drop-down List · 1. To add an item to a drop-down list, go to the items and select an item. Select Item · 2. Right click, and then click Insert. Click Insert · 3. Drop-downList   CreateaDrop-downList|AllowOtherEntries|Add/RemoveItems|DynamicDrop-downList|RemoveaDrop-downList|DependentDrop-downLists|TableMagic Drop-downlistsinExcelarehelpfulifyouwanttobesurethatusersselectanitemfromalist,insteadoftypingtheirownvalues. CreateaDrop-downList Tocreateadrop-downlistinExcel,executethefollowingsteps. 1.Onthesecondsheet,typetheitemsyouwanttoappearinthedrop-downlist. Note:ifyoudon'twantuserstoaccesstheitemsonSheet2,youcanhideSheet2.Toachievethis,rightclickonthesheettabofSheet2andclickonHide. 2.Onthefirstsheet,selectcellB1. 3.OntheDatatab,intheDataToolsgroup,clickDataValidation. The'DataValidation'dialogboxappears. 4.IntheAllowbox,clickList. 5.ClickintheSourceboxandselecttherangeA1:A3onSheet2. 6.ClickOK. Result: Note:tocopy/pasteadrop-downlist,selectthecellwiththedrop-downlistandpressCTRL+c,selectanothercellandpressCTRL+v. 7.YoucanalsotypetheitemsdirectlyintotheSourcebox,insteadofusingarangereference. Note:thismakesyourdrop-downlistcasesensitive.Forexample,ifausertypesyes,anerroralertwillbedisplayed. AllowOtherEntries Youcanalsocreateadrop-downlistinExcelthatallowsotherentries. 1.First,ifyoutypeavaluethatisnotinthelist,Excelshowsanerroralert. Toallowotherentries,executethefollowingsteps. 2.OntheDatatab,intheDataToolsgroup,clickDataValidation. The'DataValidation'dialogboxappears. 3.OntheErrorAlerttab,uncheck'Showerroralertafterinvaliddataisentered'. 4.ClickOK. 5.Youcannowenteravaluethatisnotinthelist. Add/RemoveItems Youcanaddorremoveitemsfromadrop-downlistinExcelwithoutopeningthe'DataValidation'dialogboxandchangingtherangereference.Thissavestime. 1.Toaddanitemtoadrop-downlist,gototheitemsandselectanitem. 2.Rightclick,andthenclickInsert. 3.Select"Shiftcellsdown"andclickOK. Result: Note:ExcelautomaticallychangedtherangereferencefromSheet2!$A$1:$A$3toSheet2!$A$1:$A$4.Youcancheckthisbyopeningthe'DataValidation'dialogbox. 4.Typeanewitem. Result: 5.Toremoveanitemfromadrop-downlist,atstep2,clickDelete,select"Shiftcellsup"andclickOK. DynamicDrop-downList Youcanalsouseaformulathatupdatesyourdrop-downlistautomaticallywhenyouaddanitemtotheendofthelist. 1.Onthefirstsheet,selectcellB1. 2.OntheDatatab,intheDataToolsgroup,clickDataValidation. The'DataValidation'dialogboxappears. 3.IntheAllowbox,clickList. 4.ClickintheSourceboxandentertheformula:=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1) Explanation:theOFFSETfunctiontakes5arguments.Reference:Sheet2!$A$1,rowstooffset:0,columnstooffset:0,height:COUNTA(Sheet2!$A:$A)andwidth:1.COUNTA(Sheet2!$A:$A)countsthenumberofvaluesincolumnAonSheet2thatarenotempty.WhenyouaddanitemtothelistonSheet2,COUNTA(Sheet2!$A:$A)increases.Asaresult,therangereturnedbytheOFFSETfunctionexpandsandthedrop-downlistwillbeupdated. 5.ClickOK. 6.Onthesecondsheet,simplyaddanewitemtotheendofthelist. Result: RemoveaDrop-downList Toremoveadrop-downlistinExcel,executethefollowingsteps. 1.Selectthecellwiththedrop-downlist. 2.OntheDatatab,intheDataToolsgroup,clickDataValidation. The'DataValidation'dialogboxappears. 3.ClickClearAll. Note:toremoveallotherdrop-downlistswiththesamesettings,check"Applythesechangestoallothercellswiththesamesettings"beforeyouclickonClearAll. 4.ClickOK. DependentDrop-downLists Wanttolearnevenmoreaboutdrop-downlistsinExcel?Learnhowtocreatedependentdrop-downlists. 1.Forexample,iftheuserselectsPizzafromafirstdrop-downlist. 2.Aseconddrop-downlistcontainsthePizzaitems. 3.ButiftheuserselectsChinesefromthefirstdrop-downlist,theseconddrop-downlistcontainstheChinesedishes. TableMagic YoucanalsostoreyouritemsinanExceltabletocreateadynamicdrop-downlist. 1.Onthesecondsheet,selectalistitem. 2.OntheInserttab,intheTablesgroup,clickTable. 3.Excelautomaticallyselectsthedataforyou.ClickOK. 4.Ifyouselectthelist,Excelrevealsthestructuredreference. 5.Usethisstructuredreferencetocreateadynamicdrop-downlist. Explanation:theINDIRECTfunctioninExcelconvertsatextstringintoavalidreference. 6.Onthesecondsheet,simplyaddanewitemtotheendofthelist. Result: Note:tryityourself.DownloadtheExcelfileandcreatethisdrop-downlist. 7.Whenusingtables,usetheUNIQUEfunctioninExcel365toextractuniquelistitems. Note:thisdynamicarrayfunction,enteredintocellF1,fillsmultiplecells.Wow!ThisbehaviorinExcel365iscalledspilling. 8.Usethisspillrangetocreateamagicdrop-downlist. Explanation:alwaysusethefirstcell(F1)andahashcharactertorefertoaspillrange. Result: Note:whenyouaddnewrecords,theUNIQUEfunctionautomaticallyextractsnewuniquelistitemsandExcelautomaticallyupdatesthedrop-downlist. 6/9Completed!Learnmuchmoreaboutdatavalidation> NextChapter:KeyboardShortcuts Chapter DataValidation Learnmore,it'seasy RejectInvalidDates BudgetLimit PreventDuplicateEntries ProductCodes Drop-downList DependentDrop-downLists Cmtoinches Kgtolbs DownloadExcelFile drop-down-list.xlsx NextChapter KeyboardShortcuts FollowExcelEasy BecomeanExcelPro 300Examples   Drop-downList•©2010-2021 ExcelisAwesome,we'llshowyou:Introduction•Basics•Functions•DataAnalysis•VBA

請為這篇文章評分？

延伸文章資訊