{"id":303601,"date":"2021-07-20T18:00:00","date_gmt":"2021-07-20T15:00:00","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-create-a-dependent-drop-down-list-in-microsoft-excel\/"},"modified":"2021-07-20T18:00:00","modified_gmt":"2021-07-20T15:00:00","slug":"how-to-create-a-dependent-drop-down-list-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-create-a-dependent-drop-down-list-in-microsoft-excel\/","title":{"rendered":"#How to Create a Dependent Drop-Down List in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a288514a9d75\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a288514a9d75\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-create-a-dependent-drop-down-list-in-microsoft-excel\/#Getting_Started\" >Getting Started<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-create-a-dependent-drop-down-list-in-microsoft-excel\/#Adding_and_Naming_Dependent_Drop-Down_List_Items\" >Adding and Naming Dependent Drop-Down List Items<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-create-a-dependent-drop-down-list-in-microsoft-excel\/#Creating_the_Dependent_Drop-Down_List\" >Creating the Dependent Drop-Down List<\/a><\/li><\/ul><\/nav><\/div>\n<p><strong>&#8220;#How to Create a Dependent Drop-Down List in Microsoft Excel&#8221;<\/strong><\/p>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-731027\" srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel Logo\" width=\"1200\" height=\"675\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Does using a drop-down list in Microsoft Excel make data entry easier for you or your co-workers? If you said yes and want to go a step further, you can create a dependent drop-down list just as easily.<\/p>\n<p>With a dependent drop-down list, you select the item that you want in the first list, and that determines the items that display as choices in the second one. For example, you might select a product, like a shirt, and then pick a size, a food item, like ice cream, and then pick a flavor, or an album, and then pick a song.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Getting_Started\"><\/span>Getting Started<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Obviously, you\u2019ll need your first drop-down list set up and ready to go before you can create the dependent list. We have a complete tutorial with all of the details you need to create a drop-down list in Excel for a refresher, so be sure to check that out.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Add a Drop-Down List to a Cell in Excel<\/em><\/strong><\/p>\n<p>Since setting up the second list follows the same basic process, we\u2019ll start there. Then, we\u2019ll move on to the dependency setup.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Adding_and_Naming_Dependent_Drop-Down_List_Items\"><\/span>Adding and Naming Dependent Drop-Down List Items<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>For this tutorial, we\u2019ll use departments in our company for the first drop-down list, and then employees in each department for the second list.<\/p>\n<p>Our departments include Marketing, Finance, and Human Resources (HR), and each has three employees. These employees are who we need to add and name.<\/p>\n<p>List the items for the dependent list, and then select the cells. This places the cells in a group so that you can name the group. With the cells selected, go up to the Name Box on the left side of the Formula Bar and enter a name for the cell group.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-735195\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/NameCellGroupDependentList-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Name a cell group in Excel\" width=\"385\" height=\"160\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>The names for each group should match the list items in your first dropdown.<\/p>\n<p>Using our example, we\u2019ll name our groups with the departments in our first list: Marketing, Finance, and HR.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-735194\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/NamedCellGroupDependentList-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Named cell groups\" width=\"650\" height=\"463\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You can add the items for your dependent list on the same sheet where the list will reside or on a different one. For the purposes of this how-to, you\u2019ll notice that we have everything on the same sheet.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Creating_the_Dependent_Drop-Down_List\"><\/span>Creating the Dependent Drop-Down List<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Once all of your list items are in a sheet and named, it\u2019s time to create the second drop-down list. You\u2019ll use the Data Validation feature in Excel, just like when creating your first list.<\/p>\n<p>Select the cell where you want the list. Then, go to the Data tab and click \u201cData Validation\u201d in the Data Tools section of the ribbon.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-735199\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/DataTabValidationDependentList-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Click Data Validation on the Data tab in Excel\" width=\"650\" height=\"88\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Choose the Settings tab in the pop-up window. Under Allow, pick \u201cList,\u201d and to the right, check the box for In-Cell Dropdown. Optionally, you can check the box to Ignore Blank cells if you like.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-735198\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/DataValidationDependentList-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Select List and In-Cell Dropdown\" width=\"588\" height=\"412\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In the Source box, enter the formula below. Be sure to replace the cell reference in parentheses with the cell containing your first drop-down list.<\/p>\n<pre>=INDIRECT($B$6)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-735197\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/IndirectSourceDependentList-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Add INDIRECT as the Source\" width=\"588\" height=\"412\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<blockquote class=\"admonishment_note\"><p><strong>Note:<\/strong> The INDIRECT function \u201creturns the reference specified by a text string.\u201d For additional details on this function, check out the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/support.microsoft.com\/en-us\/office\/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261\">Microsoft Support<\/a> page.<\/p><\/blockquote>\n<p>If you\u2019d like to include an Input Message or Error Alert, select those tabs in the pop-up window and enter the details. When you finish, click \u201cOK\u201d to add the drop-down list to the cell.<\/p>\n<p>Now, give your list a test. When you select an item in the first drop-down list, you should see the items pertaining to your selection as options in the second list.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-735196\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/06\/DependentDropdownList-Excel.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Dependent drop-down list in Excel\" width=\"380\" height=\"270\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>For quicker data entry for yourself or your collaborators, try a dependent drop-down list in Excel!<\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/735192\/how-to-create-a-dependent-drop-down-list-in-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#How to Create a Dependent Drop-Down List in Microsoft Excel&#8221; Does using a drop-down list in Microsoft Excel make data entry easier for you or your co-workers? If you said yes and want to go a step further, you can create a dependent drop-down list just as easily. With a dependent drop-down list, you select&#8230;<\/p>\n","protected":false},"author":1,"featured_media":303602,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-303601","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/303601","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=303601"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/303601\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/303602"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=303601"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=303601"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=303601"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}