That brings us to… Creating a Dependent Dropdown in ExcelĬreating a dependent dropdown in Excel is great from both an efficiency and user experience perspective. However, by using functions in our data validation source, we can make this dropdown list dependent on the value selected in the country dropdown. This is not efficient and is definitely not great for user experience (something you should always keep in mind when building templates). To separate the lists by state, we’d have to select three dropdowns in this fashion. This is the primary issue with simply selecting a range of data. However, this dropdown is very long and contains all states from the three countries in our first dropdown. This saved a significant amount of time over typing in the list of states separated by a comma. If you’re more comfortable typing in the range reference, you can do that as well.Īs you can see, we’ve created a dropdown in Excel by referencing the ranges in which the data is stored. To do that, click the underlined-up arrow (highlighted in screen grab below) in the data validation window, and then select your range. For instance, if we want the user to be able to select a state, we could create a separate sheet with list of possible states and reference those lists. If you have a list that is a bit longer or needs to be updated regularly, it is generally easier to input the information in a separate sheet and then reference it from the data validation interface. Creating a Dropdown by Referencing a Range of Cells I generally use this method for “this or that” type lists where the user is only selecting from one of two options. However, it is not practical for longer lists, lists that could change regularly, or lists that need to be more dynamic in nature. It does not require any of the information to be populated in a hidden sheet that a user could potentially unlock and edit so is slightly more secure. This method is useful for short Excel dropdowns that aren’t going to change very often (or ever). The result is a dropdown that is populated with Canada, Mexico, and United States. Using this technique, we’d just type the country names in separated by commas as shown in the screen grab. Let’s assume we were creating a travel request form and wanted to have a dropdown list of countries to select from. The simplest method to make a dropdown in Excel is to simply hard-code your list into the data validation rule. We will cover off on each of these individually in this article and discuss some pros & cons I’ve discovered over the years. You can create a dropdown in Excel that is hard-coded into the data validation rule, it can reference a range of data, a table of data, or even be defined by using formulas. There are a handful of different ways to populate the list and, depending on the situation, they can all be viable options. This is achieved by using data validation to create a dropdown list in a cell, as shown here. A great way to accomplish this is creating a dropdown in Excel that the end user can select values from. Howdee! There will be occasions, when working with Microsoft Excel, when you want to control the data that can be entered in a certain cell.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |