{"id":3410,"date":"2017-09-20T12:15:43","date_gmt":"2017-09-20T18:15:43","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=3410"},"modified":"2022-01-10T22:29:12","modified_gmt":"2022-01-11T04:29:12","slug":"create-a-drop-down-list-in-excel","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/help\/excel-help\/create-a-drop-down-list-in-excel.html","title":{"rendered":"Create a Drop Down List in Excel"},"content":{"rendered":"<p>I love using drop down lists in Excel! They are extremely simple to create and are a great way to make a spreadsheet easier to use. In this article, I'll first show how to create an in-cell drop-down list using data validation, and then I'll show some examples that demonstrate awesome things you can do with drop downs.<\/p>\n<div class=\"feature-image center aligncenter\" itemprop=\"image\" itemscope itemtype=\"https:\/\/schema.org\/ImageObject\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/feature\/drop-down-lists-in-excel.png\" alt=\"Drop Down Lists in Excel\" width=\"600\" height=\"314\" class=\"center imgshadow\"><meta itemprop=\"url\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/feature\/drop-down-lists-in-excel.png\" \/><meta itemprop=\"width\" content=\"600\" \/><meta itemprop=\"height\" content=\"314\" \/><span class=\"caption\"><i>Example drop-down lists in the <a href=\"\/ExcelTemplates\/money-tracker.html\">Money Tracker<\/a> template for the mobile Excel app.<\/i><\/span><\/div>\n<div class=\"contents\">\n<p>Jump Ahead To:<\/p>\n<ul>\n<li><a href=\"#simple-example\">Create a Simple Drop-Down List<\/a><\/li>\n<li><a href=\"#range\">Define a Drop-Down List Using a Range<\/a><\/li>\n<li><a href=\"#checkbox\">Check Boxes and Star Ratings using Drop Down Lists<\/a><\/li>\n<li><a href=\"#relative\">Including a Blank Value and Using Relative References<\/a><\/li>\n<li><a href=\"#copypaste\">Copying and Pasting Drop-Down Lists<\/a><\/li>\n<li><a href=\"#dynamic-ranges\">Customizable Drop-Down Lists Using Dynamic Ranges<\/a><\/li>\n<li><a href=\"#dependent\">Dependent Drop-Down Lists Using CHOOSE or INDIRECT<\/a><\/li>\n<li><a href=\"#fancy\">Fancy Dynamic Drop Downs<\/a><\/li>\n<li><a href=\"#searchable\">Searchable Drop Down Lists<\/a><\/li>\n<\/ul>\n<\/div>\n<p>To see some of the examples from this article in action, download the Excel file below.<\/p>\n<p class=\"downloadlink\"><span class=\"icon16 excel\"><\/span><a href=\"\/Files\/examples\/DropDownLists.xlsx\" onClick=\"ga('send','event', 'Downloads', 'Examples', 'DropDownLists.xlsx');\" class=\"bigbtn\" rel=\"nofollow\"><b>Download the Example File<\/b><\/a> (DropDownLists.xlsx)<\/p>\n<h2>Watch the Video<\/h2>\n<div itemprop=\"video\" itemscope itemtype=\"http:\/\/schema.org\/VideoObject\"><iframe width=\"720\" height=\"405\" src=\"https:\/\/www.youtube.com\/embed\/tvsVxyF8qt4?rel=0\" frameborder=\"0\" allowfullscreen><\/iframe><meta itemprop=\"name\" content=\"Create a Drop-Down List in Excel\"><meta itemprop=\"description\" content=\"Learn how to create a drop down list in Excel using the data validation feature. The video demonstrates how to create the lists using simple comma-delimited text strings, ranges, and named ranges.\"><meta itemprop=\"thumbnailURL\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/video-drop-down-lists-in-excel.png\"><meta itemprop=\"embedURL\" content=\"https:\/\/www.youtube.com\/embed\/tvsVxyF8qt4?rel=0\"><meta itemprop=\"uploadDate\" content=\"2017-10-02\"><\/div>\n<h2 id=\"simple-example\">Create a Simple Drop-Down List<\/h2>\n<div class=\"alignright\" style=\"margin:1em 2em 2em 1em;\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/ExcelTips\/images\/drop-down-list.gif\" alt=\"Drop Down List\" width=\"155\" height=\"103\"><span class=\"caption\"><i>Drop Down List in Excel<\/i><\/span><\/div>\n<p>You can create an in-cell drop down list in Excel by following these 4 easy steps:<\/p>\n<ol>\n<li>Select the cell, or range of cells, where you want to add the drop-down list.<\/li>\n<li>Go to Data &gt; Validation &gt; Settings tab (see image below)<\/li>\n<li>Select \"List\" from the Allow: drop-down box<\/li>\n<li>Enter your list in the Source: field using a comma to separate the items, or select a range of cells from your worksheet.<\/li>\n<\/ol>\n<div class=\"center aligncenter\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/ExcelTips\/images\/data-validation-list_1.gif\" alt=\"Data Validation Drop-Down List\" width=\"408\" height=\"342\" class=\"center imgshadow\"><span class=\"caption\"><i>Entering the Source of a Drop Down List as a Comma-Delimited List<\/i><\/span><\/div>\n<p>This approach is great for simple Yes\/No options and other lists that appear only once in your spreadsheet.<\/p>\n<p>The problem with this approach is that if you use this in a lot of cells and later want to update the list, you have to update all cells that use the list and there is a good chance you'll miss one. The more elegant approach is to use a reference to a range, or even better than that - a <b>named range<\/b>.<\/p>\n<h2 id=\"range\">Defining a Drop-Down List using a Range<\/h2>\n<p>Instead of manually entering the list of items in the data validation dialog box, you can reference a range of cells. For example, let's say I have a separate worksheet with my list defined in cells A1:A3 as shown below. In this case, I've named the range \"myList\". You can later hide the worksheet containing your list to keep your workbook looking nice and clean or to prevent a user from changing the list.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/ExcelTips\/images\/named-range_myList.gif\" alt=\"Named Range\" width=\"116\" height=\"98\" class=\"center aligncenter imgshadow\"><\/p>\n<p>In the data validation dialog box, instead of entering the list manually, you enter a reference to the named range in the Source field as shown below:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/ExcelTips\/images\/data-validation-source-range.gif\" alt=\"Data Validation Referencing a Named Range\" width=\"244\" height=\"47\" class=\"center aligncenter imgshadow\"><\/p>\n<p>You could use a reference for the Source field like =Sheet2!$A$1:$A$3, but I usually prefer to name the list. Why? If you want to change the range, you only need to edit the defined name (via Formulas &gt; Name Manager) rather than finding and editing all cells that use that particular data validation.<\/p>\n<p><b>Note:<\/b> When using a named range for a data validation list, the named range must be defined as a reference to a range of cells, or it must be a formula like OFFSET or INDIRECT or INDEX that returns a reference. If you're thinking of getting fancy and want to define a name without a cell reference such as ={\"Yes\",\"No\"}, the drop-down list won't work.<\/p>\n<p>Another bit of trivia: In old versions of Excel, using a named range was the only way for a drop-down list to reference a range on a different worksheet.<\/p>\n<h2 id=\"checkbox\">Check Boxes and Star Ratings with Excel Drop-Down Lists<\/h2>\n<p>The font used in the drop-down list cannot be changed, so it is always just a black sans serif font. This means that you can't show different colors and fonts within the drop-down list. What I think is awesome, though, is using <a href=\"https:\/\/www.vertex42.com\/blog\/help\/excel-help\/using-unicode-character-symbols-in-excel.html\">Unicode Character Symbols<\/a> to do fun things with drop-down lists, such as star-ratings using &#9733; or checkboxes using the characters &radic;, &#10004;, &#9744;, &#9745; or &#9746;.<\/p>\n<p><b>Important<\/b>: One of the main reasons I like to use checkbox-style drop-down lists is for compatibility and ease-of-use with Excel Online and the mobile Excel apps (Form Field checkboxes don't work in Excel Online or mobile apps). Also, when using a touch screen device, I think the drop-down checkbox is easier and more fun to use than entering an \"X\".<\/p>\n<h3>Example 1: Using a Drop Down List to create a Checkbox field<\/h3>\n<p>This example comes from one of my <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/task-list-template.html\">Task List<\/a> templates. The Source field is just \"&#9744;,&radic;\" (without the quotes).<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/screenshots\/checkbox-using-data-validation.png\" width=\"409\" height=\"298\" alt=\"Checkbox Using Data Validation\" class=\"center aligncenter imgshadow\"><\/p>\n<h3>Example 2: Choose a Star Rating using a Drop Down Menu<\/h3>\n<p>For a star rating, you can use \"&#9733;&#9733;&#9733;&#9733;&#9733;,&#9733;&#9733;&#9733;&#9733;,&#9733;&#9733;&#9733;,&#9733;&#9733;,&#9733;\" in the Source field. This example comes from the <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/feature-comparison.html\">Feature Comparison<\/a> template.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/screenshots\/star-rating-in-excel-using-a-drop-down-list.png\" alt=\"Star Rating Example Using a Drop Down Menu\" width=\"464\" height=\"314\" class=\"center aligncenter imgshadow\"><\/p>\n<h2 id=\"relative\">Including a Blank Value and Using Relative References<\/h2>\n<p>An in-cell drop down will ignore blanks if you enter text manually into the Source field (like \" ,Yes,No\"). So, if you want a blank value as an option, use a reference to a range as in the examples below.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/screenshots\/checkbox-style-drop-down-lists-in-excel.png\" width=\"723\" height=\"270\" alt=\"Checkbox Style Drop Down in Excel\" class=\"center aligncenter imgshadow\"><\/p>\n<p>Usually, you will use absolute references like $C$76:$C$77 for the Source in your drop-down list. However, there may be times when you want the drop-down Source to change when you copy and paste the cell. In the example above, the drop-downs use a relative reference in the Source field (no $ signs in the reference). This makes it easy to create other checkbox examples by just copying the cells to the right.<\/p>\n<p>Using a relative reference is important when creating dependent lists which will be shown a little later in this article.<\/p>\n<h2 id=\"copypaste\">Copying and Pasting Drop-Down Lists in Excel<\/h2>\n<p>When you copy and paste cells, the data validation will also be pasted, but you can't use the Format Painter to copy and paste data validation. Instead, if you only want to copy and paste the drop-down list (and not formulas or formatting), then after copying the cell, use <b>Paste Special<\/b> and select the <b>Validation<\/b> option as shown in the image.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/screenshots\/paste-special-data-validation.png\" alt=\"Paste Special - Data Validation\" width=\"398\" height=\"330\" class=\"center aligncenter imgshadow\"><\/p>\n<h2 id=\"dynamic-ranges\">Customizable Drop Down Lists Using Dynamic Ranges<\/h2>\n<p>I like creating templates that allow a person to customize lists, such as meals for a <a href=\"\/ExcelTemplates\/meal-planner.html\">Meal Planner<\/a> or accounts for an <a href=\"\/ExcelTemplates\/account-register.html\">Account Register<\/a> or products for a <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/purchase-order-price-list.html\">PO with Price List<\/a>.<\/p>\n<p>To allow for a variable number of items within the Source range, you could use a very large range like =$A$1:$A$1000, but the drop-down would end up having a crazy amount of blanks. Instead, you can create a <b>dynamic range<\/b> that extends the list to the <b>last value in the range<\/b>.<\/p>\n<p>Here is a basic example:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/screenshots\/example-drop-down-list-using-dynamic-named-range.png\" alt=\"Example Drop Down List using a Dynamic Named Range\" width=\"496\" height=\"209\" class=\"center aligncenter imgshadow\"><\/p>\n<p>You can see that even though the list range $C$127:$C$133 includes two blank cells, the drop-down only extends to row 131 (the last text value in the Categories column).<\/p>\n<p>Here's how to do it:<\/p>\n<h3>Step 1: Create a Dynamic Named Range.<\/h3>\n<p>Go to Formulas &gt; Name Manager and create a range named <i><b>category_list<\/b><\/i> using the following formula in the <i>Refers To<\/i> field, replacing <i>label_cell<\/i> and <i>list_range<\/i> with the appropriate cell references.<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">OFFSET<\/span>(<span class=\"arg\">label_cell<\/span>,1,0,<span class=\"function\">MATCH<\/span>(\"zzzzz\",<span class=\"arg\">list_range<\/span>,1),1)<\/pre>\n<p>Here is the specific formula used in the example.<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">OFFSET<\/span>(<span class=\"arg\">$C$127<\/span>,1,0,<span class=\"function\">MATCH<\/span>(\"zzzzz\",<span class=\"arg\">$C$127:$C$133<\/span>,1)-1,1)<\/pre>\n<h3>Step 2: Use the Named Range in the Source field for the drop-down list.<\/h3>\n<pre class=\"XLformula light\">Source: =category_list<\/pre>\n<p>See my article \"<a href=\"https:\/\/www.vertex42.com\/blog\/excel-formulas\/dynamic-named-ranges.html\"><b>Dynamic Named Ranges<\/b><\/a>\" to learn more about the various formulas you can use. The formula used in the above example works well for lists that include only text values.<\/p>\n<h2 id=\"dependent\">Dependent Drop-Down Lists Using CHOOSE or INDIRECT<\/h2>\n<p>A dependent drop-down list is a list that changes based on the value of another cell, which might also contain a drop-down list of its own. The Source for a drop-down list can be a formula, and that is the key to making the dependent list. As I mentioned before, the formula must return a reference, so there are only a few types of formulas that will work for drop downs. I personally prefer using CHOOSE or INDIRECT.<\/p>\n<p>The example below is based on an account register where the idea is to choose categories for each transaction. The Type column contains a drop-down list that references cells C179:D179 (the labels \"Expense\" and \"Income\"). We want the dependent drop down box in the Category column to use the list of expenses if the Type is \"Expense\" and the list of income categories if the Type is \"Income.\"<\/p>\n<p>Here is an example showing the CHOOSE method:<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/screenshots\/dependent-drop-down-list-example-using-choose.png\" alt=\"Dependent Drop-Down List Example Using CHOOSE\" width=\"700\" height=\"250\" class=\"center aligncenter imgshadow\"><\/p>\n<p>The formula uses a relative reference for the <i>type<\/i> cell and absolute references for the <i>type_values<\/i>, <i>expense_range<\/i> and <i>income_range<\/i> like this:<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">CHOOSE<\/span>(<span class=\"function\">MATCH<\/span>(<span class=\"arg\">G182<\/span>,<span class=\"arg\">$C$179:$D$179<\/span>,0),<span class=\"arg\">$C$180:$C$183<\/span>,<span class=\"arg\">$D$180:$D$183<\/span>)<\/pre>\n<p>Alternatively, we could create dynamic named ranges called <i>Expense_range<\/i> and <i>Income_range<\/i> and then use the following formula for the Source:<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">INDIRECT<\/span>(<span class=\"arg\">G182<\/span>&amp;\"_range\")<\/pre>\n<p>You can use named ranges within the CHOOSE formula as well, so I'm not sure whether one method is better than the other. Some may argue that CHOOSE is better because INDIRECT is a <a href=\"https:\/\/www.vertex42.com\/blog\/excel-formulas\/volatile-functions.html\">volatile function<\/a>, but I don't think that matters for drop-down lists.<\/p>\n<p>See the <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/grocery-price-book.html\">Grocery Price Book<\/a> template for a practical example of how dependent drop-down lists can be used in a spreadsheet.<\/p>\n<h2 id=\"fancy\">Fancy Dynamic Drop-Down Lists<\/h2>\n<p>If the previous examples aren't fancy enough for you, my article \"<a href=\"https:\/\/www.vertex42.com\/blog\/help\/excel-help\/dynamic-drop-down-lists.html\"><b>Dynamic Drop-Down Lists<\/b><\/a>\" explains how to create drop-down lists that change based on user input, the date, check number, etc.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/ExcelArticles\/Images\/dynamic-drop-down-lists-check-number.png\" alt=\"Dynamic Drop-Down List for Check Number\" width=\"315\" height=\"197\" class=\"center aligncenter imgshadow\"><\/p>\n<h2 id=\"searchable\">Searchable Drop-Down Lists<\/h2>\n<p>If your drop-down list is really long, it can be difficult to find the item you are looking for. Google Sheets provides a great solution, though not a perfect one (yet). In Sheets, you can start typing into the cell and the drop-down list will automatically filter based upon what you type ... as long as it is the start of one or more of the words in the list.<\/p>\n<p>For example, let's say your list contains the names Abe Lincoln, George Washington, Harry Truman, and J. Edgar Hoover. As soon as you type \"h\", the list will be shortened to Harry Truman and J. Edgar Hoover, but Sheets does not recognize the \"h\" in Washington.<\/p>\n<p>Excel: Using a fairly complicated trick, you CAN create a searchable drop-down list in Excel. See this <a href=\"https:\/\/www.youtube.com\/watch?v=vkPoViUhkxU\" target=\"_blank\" rel=\"noopener\">youtube video<\/a>.<\/p>\n<h2>More Examples<\/h2>\n<p>My article <a href=\"\/blog\/help\/excel-help\/add-cool-features-to-your-to-do-lists-in-excel.html\" target=\"_blank\" rel=\"noopener\"><b>Add Cool Features to Your To Do Lists in Excel<\/b><\/a> shows a few other examples, like using <a href=\"\/blog\/excel-tips\/how-to-use-conditional-formatting-in-excel.html\">conditional formatting<\/a> combined with a drop-down box to select a priority value in a to do or task list.<\/p>\n<p><b>Example 1<\/b>: This <a href=\"\/ExcelTemplates\/homework-to-do-list.html\">Homework To Do List<\/a> allows you to choose a High, Medium, or Low value in the Priority column. There are also examples of this on the <a href=\"\/ExcelTemplates\/task-list-template.html\">Task List<\/a> template page.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/screenshots\/example-drop-down-list-via-data-validation.png\" alt=\"Example Drop-Down List via Data Validation\" width=\"543\" height=\"296\" class=\"center aligncenter imgshadow\"><\/p>\n<p><b>Example 2<\/b>: This <a href=\"\/ExcelTemplates\/task-list-template.html\">Task List Template<\/a> uses conditional formatting icon sets for the Priority column and a drop-down list to choose a value between 1 and 4.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/screenshots\/custom-icon-set-for-task-priority.png\" alt=\"Icon Chosen Using a Drop-Down List\" width=\"527\" height=\"315\" class=\"center aligncenter imgshadow\"><\/p>\n<p><b>More Examples<\/b>: Drop-down lists are a common feature in many of my templates, including the <a href=\"\/ExcelTemplates\/meal-planner.html\">Meal Planner<\/a>, <a href=\"\/ExcelTemplates\/money-management-template.html\">Money Manager<\/a>, and many <a href=\"\/Calculators\/financial-calculators.html\">financial calculators<\/a>. You can download the templates to see how the drop-down lists are created.<\/p>\n<p>The <a href=\"\/ExcelTemplates\/customer-list.html\">Customer List Template<\/a> page explains how to copy a customer list worksheet into a spreadsheet, create a drop-down list showing customer names, and then add lookup formulas to display the information for the chosen customer.<\/p>\n<h3>Additional Resources<\/h3>\n<ul class=\"bulletlist\">\n<li><a href=\"https:\/\/support.office.com\/en-us\/article\/Create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b\" target=\"_blank\" rel=\"noopener\">Create a Drop-Down List<\/a> at <i>support.office.com<\/i> - A relatively simple article and video showing how to create a drop-down list in Excel.<\/li>\n<li><a href=\"https:\/\/support.office.com\/en-us\/article\/Create-and-manage-drop-down-lists-28db87b6-725f-49d7-9b29-ab4bc56cefc2\" target=\"_blank\" rel=\"noopener\">Create and Manage Drop-Down Lists<\/a> at <i>support.office.com<\/i> - Includes some very clear videos demonstrating the basics.<\/li>\n<\/ul>\n<p><i>The first version of this article was originally published on 4\/7\/2009 at https:\/\/www.vertex42.com\/ExcelTips\/drop-down-list.html but the original article has been updated and integrated into this blog post.<\/i><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I love using drop down lists in Excel. In this article, I&#8217;ve tried to explain all of the techniques that I use for making drop-down lists, including simple yes\/no options, checkbox substitutes, dependent lists for sub-categories, and using dynamic named ranges.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[37,78],"tags":[],"class_list":{"0":"post-3410","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-excel-help","7":"category-excel-tips"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3410","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/comments?post=3410"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3410\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=3410"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=3410"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=3410"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}