{"id":3389,"date":"2015-05-09T22:06:56","date_gmt":"2015-05-10T03:06:56","guid":{"rendered":"http:\/\/www.mdmproofing.com\/iym\/weblog\/?p=3389"},"modified":"2015-05-10T10:12:00","modified_gmt":"2015-05-10T15:12:00","slug":"excel-create-drop-down-menus","status":"publish","type":"post","link":"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/","title":{"rendered":"Excel: Create Drop-Down Menus"},"content":{"rendered":"

Thanks to my popular Excel page<\/a>, I’ve gotten several questions recently about how to create drop-down menus in Excel. Yes, a quick Google search for “Excel drop-down list” or similar would likely educate most folks well enough, but I’ve haven’t posted in a long while … and this seems like a fine little tutorial to add to my collection<\/a>.<\/p>\n

NOTE: I’m using Excel 2010 for this tutorial. Instructions would be pretty much the same for Excel 2007 and later versions.<\/div>\n

Excel Feature: Data Validation<\/h3>\n

The feature in Excel that allows for us to use drop-down menus in cells is called Data Validation<\/b>. <\/p>\n

\"Data<\/p>\n

In truth, Data Validation allows a spreadsheet creator to control all kinds of things about what the user might enter in cell or group of cells. But when it comes to creating a drop-down list for users to choose from, well, that’s where lots of questions tend to arise.<\/p>\n

So let’s pretend that we have a spreadsheet in which we want our users to enter, in Cell B6, one of four choices:<\/p>\n

    \n
  • Visa<\/li>\n
  • MasterCard<\/li>\n
  • American Expresss<\/li>\n
  • Discover<\/li>\n<\/ul>\n

    First, we need to put those options somewhere in our spreadsheet for Excel to find. So I’m going to enter our “list” of options in Cells A1 through A4:<\/p>\n

    \"Data<\/p>\n

    Note that I don’t have to create my list of options in the same worksheet; it could be in a different worksheet, too, and in any group of consecutive cells you like. The list can also be in hidden rows; that’s what I end up doing most often in my own spreadsheets.<\/p>\n

    Now that we have our list, and with Cell B6 as our active cell, we’ll do the not-so-hard part:<\/p>\n

    \n
      \n
    1. In the Excel ribbon, select the DATA tab.<\/li>\n
    2. Click the DATA VALIDATION button.<\/li>\n
    3. In the SETTINGS tab, find the “Allow:” box, and select “List.”<\/li>\n

      Now, below that, in the “Source” field, we’ll tell Excel where to find the list of choices we want users to have. In this case, our list is in the area defined by $A$1:$A$4. (The dollar signs tell Excel that these are absolute references to those rows and columns.)<\/p>\n

    4. In the “Source:” field, type “=$A$1:$A$4” and then click OK.<\/li>\n<\/ol>\n<\/div>\n

      And just like magic, you’ll see the drop-down menu indicator next to Cell B6. Click that, and our four choices appear in a drop-down menu:<\/p>\n

      \"Data<\/p>\n

      And that, folks, is all it takes to create a drop-down list in any cell (or cells) of your Excel spreadsheets! <\/p>\n","protected":false},"excerpt":{"rendered":"

      Thanks to my popular Excel page, I’ve gotten several questions recently about how to create drop-down menus in Excel. Yes, a quick Google search for “Excel drop-down list” or similar would likely educate most folks well enough, but I’ve haven’t posted in a long while … and this seems like a fine little tutorial to […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[35,45],"tags":[],"class_list":["post-3389","post","type-post","status-publish","format-standard","hentry","category-excel","category-tutorials"],"yoast_head":"\nExcel: Create Drop-Down Menu<\/title>\n<meta name=\"description\" content=\"How to create drop-down lists in Excel 2007 and later.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel: Create Drop-Down Menu\" \/>\n<meta property=\"og:description\" content=\"How to create drop-down lists in Excel 2007 and later.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/\" \/>\n<meta property=\"og:site_name\" content=\"Money Musings\" \/>\n<meta property=\"article:published_time\" content=\"2015-05-10T03:06:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2015-05-10T15:12:00+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.mdmproofing.com\/iym\/weblog\/graphics\/drop-down003.gif\" \/>\n<meta name=\"author\" content=\"Michael\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Michael\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/\"},\"author\":{\"name\":\"Michael\",\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/#\/schema\/person\/9d5b5e0cd8c50c2ff0f4a1df112a5a42\"},\"headline\":\"Excel: Create Drop-Down Menus\",\"datePublished\":\"2015-05-10T03:06:56+00:00\",\"dateModified\":\"2015-05-10T15:12:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/\"},\"wordCount\":423,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/www.mdmproofing.com\/iym\/weblog\/graphics\/drop-down003.gif\",\"articleSection\":[\"Excel\",\"Tutorials\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/\",\"url\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/\",\"name\":\"Excel: Create Drop-Down Menu\",\"isPartOf\":{\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/www.mdmproofing.com\/iym\/weblog\/graphics\/drop-down003.gif\",\"datePublished\":\"2015-05-10T03:06:56+00:00\",\"dateModified\":\"2015-05-10T15:12:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/#\/schema\/person\/9d5b5e0cd8c50c2ff0f4a1df112a5a42\"},\"description\":\"How to create drop-down lists in Excel 2007 and later.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#primaryimage\",\"url\":\"http:\/\/www.mdmproofing.com\/iym\/weblog\/graphics\/drop-down003.gif\",\"contentUrl\":\"http:\/\/www.mdmproofing.com\/iym\/weblog\/graphics\/drop-down003.gif\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel: Create Drop-Down Menus\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/#website\",\"url\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/\",\"name\":\"Money Musings\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/#\/schema\/person\/9d5b5e0cd8c50c2ff0f4a1df112a5a42\",\"name\":\"Michael\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/2ed3cc00ccbe418a46908aff759f0164eaa603a5944928ead8ba144229b89d7c?s=96&d=http%3A%2F%2Fwww.mdmproofing.com%2Fiym%2Fweblog%2Fwp-content%2Fthemes%2Fmusings%2Fimages%2Fuser.gif&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/2ed3cc00ccbe418a46908aff759f0164eaa603a5944928ead8ba144229b89d7c?s=96&d=http%3A%2F%2Fwww.mdmproofing.com%2Fiym%2Fweblog%2Fwp-content%2Fthemes%2Fmusings%2Fimages%2Fuser.gif&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/2ed3cc00ccbe418a46908aff759f0164eaa603a5944928ead8ba144229b89d7c?s=96&d=http%3A%2F%2Fwww.mdmproofing.com%2Fiym%2Fweblog%2Fwp-content%2Fthemes%2Fmusings%2Fimages%2Fuser.gif&r=g\",\"caption\":\"Michael\"},\"sameAs\":[\"http:\/\/www.mdmproofing.com\/iym\/\"],\"url\":\"https:\/\/www.mdmproofing.com\/iym\/weblog\/author\/Michael\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel: Create Drop-Down Menu","description":"How to create drop-down lists in Excel 2007 and later.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/","og_locale":"en_US","og_type":"article","og_title":"Excel: Create Drop-Down Menu","og_description":"How to create drop-down lists in Excel 2007 and later.","og_url":"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/","og_site_name":"Money Musings","article_published_time":"2015-05-10T03:06:56+00:00","article_modified_time":"2015-05-10T15:12:00+00:00","og_image":[{"url":"http:\/\/www.mdmproofing.com\/iym\/weblog\/graphics\/drop-down003.gif","type":"","width":"","height":""}],"author":"Michael","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Michael","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#article","isPartOf":{"@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/"},"author":{"name":"Michael","@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/#\/schema\/person\/9d5b5e0cd8c50c2ff0f4a1df112a5a42"},"headline":"Excel: Create Drop-Down Menus","datePublished":"2015-05-10T03:06:56+00:00","dateModified":"2015-05-10T15:12:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/"},"wordCount":423,"commentCount":0,"image":{"@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#primaryimage"},"thumbnailUrl":"http:\/\/www.mdmproofing.com\/iym\/weblog\/graphics\/drop-down003.gif","articleSection":["Excel","Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/","url":"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/","name":"Excel: Create Drop-Down Menu","isPartOf":{"@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#primaryimage"},"image":{"@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#primaryimage"},"thumbnailUrl":"http:\/\/www.mdmproofing.com\/iym\/weblog\/graphics\/drop-down003.gif","datePublished":"2015-05-10T03:06:56+00:00","dateModified":"2015-05-10T15:12:00+00:00","author":{"@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/#\/schema\/person\/9d5b5e0cd8c50c2ff0f4a1df112a5a42"},"description":"How to create drop-down lists in Excel 2007 and later.","breadcrumb":{"@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#primaryimage","url":"http:\/\/www.mdmproofing.com\/iym\/weblog\/graphics\/drop-down003.gif","contentUrl":"http:\/\/www.mdmproofing.com\/iym\/weblog\/graphics\/drop-down003.gif"},{"@type":"BreadcrumbList","@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/2015\/05\/excel-create-drop-down-menus\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.mdmproofing.com\/iym\/weblog\/"},{"@type":"ListItem","position":2,"name":"Excel: Create Drop-Down Menus"}]},{"@type":"WebSite","@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/#website","url":"https:\/\/www.mdmproofing.com\/iym\/weblog\/","name":"Money Musings","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.mdmproofing.com\/iym\/weblog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.mdmproofing.com\/iym\/weblog\/#\/schema\/person\/9d5b5e0cd8c50c2ff0f4a1df112a5a42","name":"Michael","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/2ed3cc00ccbe418a46908aff759f0164eaa603a5944928ead8ba144229b89d7c?s=96&d=http%3A%2F%2Fwww.mdmproofing.com%2Fiym%2Fweblog%2Fwp-content%2Fthemes%2Fmusings%2Fimages%2Fuser.gif&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/2ed3cc00ccbe418a46908aff759f0164eaa603a5944928ead8ba144229b89d7c?s=96&d=http%3A%2F%2Fwww.mdmproofing.com%2Fiym%2Fweblog%2Fwp-content%2Fthemes%2Fmusings%2Fimages%2Fuser.gif&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/2ed3cc00ccbe418a46908aff759f0164eaa603a5944928ead8ba144229b89d7c?s=96&d=http%3A%2F%2Fwww.mdmproofing.com%2Fiym%2Fweblog%2Fwp-content%2Fthemes%2Fmusings%2Fimages%2Fuser.gif&r=g","caption":"Michael"},"sameAs":["http:\/\/www.mdmproofing.com\/iym\/"],"url":"https:\/\/www.mdmproofing.com\/iym\/weblog\/author\/Michael\/"}]}},"_links":{"self":[{"href":"https:\/\/www.mdmproofing.com\/iym\/weblog\/wp-json\/wp\/v2\/posts\/3389","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mdmproofing.com\/iym\/weblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mdmproofing.com\/iym\/weblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mdmproofing.com\/iym\/weblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mdmproofing.com\/iym\/weblog\/wp-json\/wp\/v2\/comments?post=3389"}],"version-history":[{"count":17,"href":"https:\/\/www.mdmproofing.com\/iym\/weblog\/wp-json\/wp\/v2\/posts\/3389\/revisions"}],"predecessor-version":[{"id":3406,"href":"https:\/\/www.mdmproofing.com\/iym\/weblog\/wp-json\/wp\/v2\/posts\/3389\/revisions\/3406"}],"wp:attachment":[{"href":"https:\/\/www.mdmproofing.com\/iym\/weblog\/wp-json\/wp\/v2\/media?parent=3389"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mdmproofing.com\/iym\/weblog\/wp-json\/wp\/v2\/categories?post=3389"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mdmproofing.com\/iym\/weblog\/wp-json\/wp\/v2\/tags?post=3389"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}