Is the a limit on drop down list in Excel?
A question I hear a lot is: how can I create a drop down list in my Excel workbook? And I understand why you want to know. Some spreadsheets in Excel are meant to be tightly controlled. Maybe you just want your data to be clean and consistent. Some fields that you use in your data should only have specific values. Or maybe your Excel file is used as a template or form, like a document for your team to start on a new project. The good news is: Excel can easily help you do this. In this post you’ll find out how to create this drop down menu in Excel, and you’ll get three different ways to set it up, depending on your needs. If you want to skip to the option that’s right for you, here’s the summary: The Summary
What the Excel drop down menu looks likeHere’s an example of what you might want to set up in your Excel workbook: The company you work at might have a Finance or HR form which looks similar to the above. These kinds of forms need to be filled out in a certain way, so it’s helpful to limit values so that Bob in Accounts knows exactly which budget line to allocate the Christmas Party funds to. This is just a simple example that might be used in an office environment, and we’ll go through this example throughout this post. And before we get into the specific points of each method, read on to find out about the Data Validation feature which is a key part of setting up a drop down menu in Excel. Introducing the Data Validation menuYou can’t create a drop down list in Excel without using the Data Validation feature. Think of Data Validation is a restriction or limitation that Excel applies to the cells you specify. You can choose the criteria, of course. You can force cells to be integers, dates, or values from a specific list (which is what you’ll be using for creating these menus) and a few other options. The Data Validation menu is in the Data tab in the Excel Ribbon: And here’s what the menu looks like: In each of the three methods below, you will use this feature to choose the items in our menu. OK, enough of the introduction – let’s create a drop down menu in Excel! If you have a fixed list of values that you want to choose from, you can enter them manually into the “Source:” box when you change the Validation options. There are some dangers with this option (and the other methods will work around these risks) but it is easily the fastest way to implement a drop down menu. ProsCons
ProcessHere’s the quickest way to set up a drop down menu in Excel:
That’s it! You have now created a drop down menu for the cells you selected. You’ll only be able to enter values that are in the list you specified – and you can still type directly into the cell if you want. Excel Drop Down List Method #2: Refer to a Data RangeWhat if you want to update your menu items later? If one of the departments in your company has a name change, then you would have to select all of the cells that use the drop down menu, and manually update the details. (Although, if your dropdown menu is limited to only one cell, it’s not so bad…) This alternate method involves entering the data somewhere else in your Excel workbook, and referring to that data range in the Data Validation settings. Pros
Cons
Process
BenefitsIt should seem fairly obvious why it’s helpful to refer to a data range instead of the original values. Let’s change one of the department names: And here’s what the menu looks like: Of course, I’ve saved the best option for last. The second option is good, but what if you need to easily add additional menu items? Let’s try adding a new option to the list, by adding an extra department at the bottom of the data range. But if we test out the drop down menu, it won’t be there because the data range is $A$1:$A$5. It won’t pick up any additional items. What’s the solution?Your Excel drop down list can be automatically updated if you use an Excel Table to store your menu items. If you haven’t used Excel tables before, they are a distinct object in your Excel workbook that can be given a name. That name can be referred to throughout your work. And a huge benefit of tables is that they will automatically expand when extra data is added to them.
What does this mean for your drop down menu? It means you can simply refer to a column within an Excel table, and the menu will automatically update based on the items in that list. If you anticipate adding extra items to your drop down menu over time, then this method is the best long-term solution as it’s the “cleanest” way to refer to your list of menu items. Pros
Cons
Process
BenefitsI bet you’re thinking: why do I need to bother with a formula like that? The other options were so simple in comparison! Well you’re right, but let’s add some extra items to our Table by entering data immediately underneath it: Now the Excel Table has expanded to include the new menu item. And since your Data Validation is referring to the entire column within the table, you can add as many items as you want and they will automatically be included in the drop down menu! Even better, you can move the Table around your workbook and it’ll still be referenced correctly. Even more benefits!Of course, the Excel Table doesn’t have to have a single column. You can edit the cells to the right and create additional columns that will automatically become part of the table: And then you can leverage this information in a VLOOKUP or INDEX/MATCH formula: For more references on using Tables in Excel formulas, try this helpful article/video from Chandoo, or this helpful explanation from Excel Campus. If you want to make a drop down list dependent on another list, try this article from Excel by Joe. ConclusionSo there you have it – three different methods to create a drop down list in Excel. Each have their own benefits and drawbacks, so you’ll find that you might use different options for different scenarios. What do you use drop down menus for, and which of the above methods did you use? |