{"id":338,"date":"2015-11-04T05:45:39","date_gmt":"2015-11-04T05:45:39","guid":{"rendered":"http:\/\/99excel.com\/?p=338"},"modified":"2021-05-14T16:08:49","modified_gmt":"2021-05-14T16:08:49","slug":"two-methods-to-create-drop-down-list-in-excel","status":"publish","type":"post","link":"https:\/\/99excel.com\/home\/two-methods-to-create-drop-down-list-in-excel\/","title":{"rendered":"Two Methods To Create Drop Down List in Excel"},"content":{"rendered":"<p>Creating a drop down list is very easy. Its is very important function in excel if you want to select cell value from preconfigured items list and save your time in again and again typing it. For example , you are maintaining an inventory of cloths and there are 5 Size of a particular product then it will be really easy for you if you will create a drop down list and store all sizes values in another reference sheet , now you will just have to select the cell values from preconfigured list.<\/p>\n<p>With the help of drop down lists you can avoid errors like typing mistakes \u00a0and \u00a0it will save a lot of time as you are just selecting the options from a preconfigured list. You can also add and edit items in your list any time.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\" size-full wp-image-46 aligncenter\" src=\"http:\/\/i0.wp.com\/99exceltips.com\/wp-content\/uploads\/2015\/05\/Excel-Dropdown-List.jpg?resize=412%2C378\" alt=\"Excel Dropdown List\" width=\"412\" height=\"378\" \/><\/p>\n<p>Lets see and understand how to do it.<\/p>\n<h2>FIRST METHOD: DIRECTLY TYPING THE LIST<\/h2>\n<ul>\n<li>Select cell where you want to create drop down list and go to data tab in upper menu. Now select data validation option.<br \/>\n<img loading=\"lazy\" decoding=\"async\" class=\" size-full wp-image-47 aligncenter\" src=\"http:\/\/i2.wp.com\/99exceltips.com\/wp-content\/uploads\/2015\/05\/Select-data-validation.jpg?resize=454%2C324\" alt=\"Select data validation\" width=\"454\" height=\"324\" \/><\/li>\n<li>Data validation window will open , \u00a0In \u201d settings \u201d tab \u00a0move to \u201d validation criteria\u201d and under \u201callow \u201d select \u201cList\u201d. Now \u00a0go to sources and insert your list as shown in the image below.<img loading=\"lazy\" decoding=\"async\" class=\" size-full wp-image-48 aligncenter\" src=\"http:\/\/i0.wp.com\/99exceltips.com\/wp-content\/uploads\/2015\/05\/dropdown-list-in-source.jpg?resize=412%2C324\" alt=\"dropdown list in source\" width=\"412\" height=\"324\" \/><\/li>\n<li>Now you will get a drop down list in your select cell \u00a0with values you had inserted in sources box.<\/li>\n<\/ul>\n<p>This method is very easy and straight forward but it is only when you have only few entries in your list , what if you have to select from a list of 200 entries then this method will become a big mess . Under such condition you will have to take help of second method.<\/p>\n<h2>SECOND METHOD : CREATING PRE CONFIGURED LIST<\/h2>\n<p>In this method we are going to create a pre configured list in different sheet and we we will used it in our desired cell. This is major method in creating drop down list \u00a0and can be used when you have a lot of entries in your list or you have to use same list many times in a sheet.<\/p>\n<ul>\n<li>First create a list in \u201d sheet2 \u201d or any other sheet.<\/li>\n<li>Select the complete list and right click on it . Then select \u201cName Range\u201d .<img loading=\"lazy\" decoding=\"async\" class=\" size-full wp-image-52 aligncenter\" src=\"http:\/\/i0.wp.com\/99exceltips.com\/wp-content\/uploads\/2015\/05\/Select-Nmae-range-in-Excel.jpg?resize=337%2C396\" alt=\"Select Nmae range in Excel\" width=\"337\" height=\"396\" \/><\/li>\n<li>A \u201d New Name \u201d window will open , provide a name to our list like size , shape , weight or whatever you want.<\/li>\n<li>Now move to sheet where you wan to insert drop down list .<\/li>\n<li>Go to data tab in top menu . Select \u201cData Validation\u201d .<\/li>\n<li>Data validation window will open . Now select \u201cList\u201d in \u00a0\u201cAllow\u201d tab under settings .<\/li>\n<li>It will display a \u201cSource\u201d option . Now insert your list name which you have entered in \u201cName Range \u201d option in previous step. If you have named it \u201cSize\u201d then enter \u201c=size\u201d in source option.<img loading=\"lazy\" decoding=\"async\" class=\" size-full wp-image-53 aligncenter\" src=\"http:\/\/i1.wp.com\/99exceltips.com\/wp-content\/uploads\/2015\/05\/data-validation-selecting-source.jpg?resize=409%2C330\" alt=\"data validation selecting source\" width=\"409\" height=\"330\" \/><\/li>\n<li>Alternatively , \u00a0insert your cells source for example my list is in \u201csheet2\u2033 \u00a0and from A1 to A6 so i will insert \u201c=sheet2!$A$1:$A$6\u2033 \u00a0similarly you will have to insert your cell ranges.<\/li>\n<li>Press OK and you have created your drop down list in excel !<\/li>\n<\/ul>\n<h2>HOW TO INSERT INPUT MESSAGE AND ERROR MESSAGE IN DROP DOWN LIST<\/h2>\n<p>If you want to further increase appearance of your drop down list then you can display a input message and error message in drop down list.Lest see how to do it.<\/p>\n<ul>\n<li>Select the cell in which you have created drop down list.<\/li>\n<li>Go to \u201cData Validation\u201d option and then move to input message and insert your message title and message description.<img loading=\"lazy\" decoding=\"async\" class=\" size-full wp-image-55 aligncenter\" src=\"http:\/\/i0.wp.com\/99exceltips.com\/wp-content\/uploads\/2015\/05\/Input-Message-drop-down-list.jpg?resize=399%2C329\" alt=\"Input Message drop down list\" width=\"399\" height=\"329\" \/><\/li>\n<li>Similarly you can also insert error alert using the same method as input message.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>Well i hope now you can create drop down list in excel. if you have any further problems in creating list then please comment and i will solve your problems.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Creating a drop down list is very easy. Its is very important function in excel if you want to select cell value from preconfigured items list and save your time in again and again typing it. For example , you are maintaining an inventory of cloths and there are 5 Size of a particular product &#8230; <a title=\"Two Methods To Create Drop Down List in Excel\" class=\"read-more\" href=\"https:\/\/99excel.com\/home\/two-methods-to-create-drop-down-list-in-excel\/\" aria-label=\"More on Two Methods To Create Drop Down List in Excel\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[],"class_list":["post-338","post","type-post","status-publish","format-standard","hentry"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/338","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/comments?post=338"}],"version-history":[{"count":1,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/338\/revisions"}],"predecessor-version":[{"id":882,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/posts\/338\/revisions\/882"}],"wp:attachment":[{"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/media?parent=338"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/categories?post=338"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/99excel.com\/home\/wp-json\/wp\/v2\/tags?post=338"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}