Posted at 15:05h in Excel Formulas 0 Comments
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. A great way to accomplish this is creating a dropdown in Excel that the end user can select values from. This is achieved by using data validation to create a dropdown list in a cell, as shown here.
There are a handful of different ways to populate the list and, depending on the situation, they can all be viable options. 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. We will cover off on each of these individually in this article and discuss some pros & cons I’ve discovered over the years.
- Begin by selecting the dropdown range C3:C5
- Open the Data Validation window by clicking DATA > Data Validation
- Select List in Allow dropdown
- Type your formula in Source input =COUNTIF($I$3:$I$7,B2)
- Click OK to continue and apply your settings
Excel’s Data Validation feature is a great tool to ensure that users enter correct data. It also allows you to create your own lists by using formulas. Selecting List option in Allow drop down shows the Source input that you can enter your list reference or formula.
By using formula support, you can determine the range of cells that you want to use as a list. Alternatively; you can send an empty string, which represents an empty list as well, to make drop down items hidden.
Simple IF function is enough to to make this a conditional Excel drop down. All you need to do is to set a condition and the list range that is populated if the condition is TRUE. The last argument of the IF function is the two double quotes (“”) that represents empty string.
Hard-coding a Dropdown in Excel
The simplest method to make a dropdown in Excel is to simply hard-code your list into the data validation rule. Let’s assume we were creating a travel request form and wanted to have a dropdown list of countries to select from. Using this technique, we’d just type the country names in separated by commas as shown in the screen grab. The result is a dropdown that is populated with Canada, Mexico, and United States.
This method is useful for short Excel dropdowns that aren’t going to change very often (or ever). 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. However, it is not practical for longer lists, lists that could change regularly, or lists that need to be more dynamic in nature. I generally use this method for “this or that” type lists where the user is only selecting from one of two options.
Creating a Dropdown by Referencing a Range of Cells
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. 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. To do that, click the underlined-up arrow (highlighted in screen grab below) in the data validation window, and then select your range. If you’re more comfortable typing in the range reference, you can do that as well.
As you can see, we’ve created a dropdown in Excel by referencing the ranges in which the data is stored. This saved a significant amount of time over typing in the list of states separated by a comma. However, this dropdown is very long and contains all states from the three countries in our first dropdown. This is the primary issue with simply selecting a range of data. To separate the lists by state, we’d have to select three dropdowns in this fashion. This is not efficient and is definitely not great for user experience (something you should always keep in mind when building templates). However, by using functions in our data validation source, we can make this dropdown list dependent on the value selected in the country dropdown. That brings us to…
Creating a Dependent Dropdown in Excel
Creating a dependent dropdown in Excel is great from both an efficiency and user experience perspective. It requires a little extra effort at setup but is easy to maintain in the long run. For this approach, you’ll need to separate your data out so it’s easy to identify what belongs where. For this example, I’ve broken out the state lists by country. From here, we will want to name the range of cells that contain the states for each country. Be sure you only select the states and not the header as whatever you select will show up in your dropdown. There are two ways to name a range in Excel. You can highlight the range you wish to name and type the name in the “Name Box” to the left of the formula bar, or you can use the “Name Manager” on the Formulas ribbon tab. I’ve shown an example of both in screen grabs below.
I’ve named the ranges after the values in my original dropdown. This was done on purpose to simplify step two. For this step, we will be utilizing the INDIRECT function. The indirect function returns a reference based on a text string you enter. If you use the indirect function in conjunction with your named ranged, it will return a reference to the range. This way, by using the initial dropdown as the input for our indirect function (shown below in screen grab), we have created a dynamic dropdown in Excel that will change based on what’s selected in the first.
The end user can now change countries and the list will automatically change based on their input from the first dropdown. Creating a dropdown in Excel like this will keep your templates organized and make the end user’s experience much more enjoyable. Still, this approach is not without its disadvantages. If the list is ever extended or condensed, you will have to go back to the name manager and change the size of your named range or you could be missing data or have blank selections in your dropdown. You could always ensure you insert a row into the middle of a named range but that isn’t practical if you ever hand this off to someone who isn’t aware of this. To make this even easier, let’s use tables to create our dropdown in Excel.
1 – Excel Table
Convert the list of values you need in your dropdown menu into an Excel table. You can go into DESIGN and rename the table under the ‘Properties’ section. We have given it the name ‘Table_Name’.
2 – From Excel Table to Named Range
Press CTRL + F3 or go to FORMULAS > Name Manager and select New. This is just to name our newly created Excel table.
In the ‘Name’ field, assign a unique name for the range. Hence, ‘Table_Name’ cannot be used. We have used ‘Days’ here. In the ‘Refers to’ field, use the syntax below. When done, click on OK.
< table name >[< field name >]
3 – Data Validation List
Now select the cell you want to apply the dynamic dropdown menu to and go to DATA > Data Validation. Select ‘List’ from field ‘Allow’ and, within the ‘Source’ field, write the new name of the table with ‘=’ as a prefix. Now click OK and you’re are done.