Create a Dropdown List by using Data Validation Command
Creating a drop-down list by using Data Validation Command is easy and useful. It reduces the input time and maintains the data integrity. There are actually four ways to create a drop-down list depending on the use of it. The last two methods require knowledge of dynamic referencing. Please see the following steps to create one.
First way: Use data on the worksheet as the drop-down list.
File to download: Example of Dropdown List.xlsx
Related articles: Data Referencing, Dynamic Referencing
Step 1: Click the “Data” tab on the ribbon, and then select the “Data Validation” command.
Step 2: A “Data Validation” pop-up screen will appear. Please select “List” under the “Allow:” box in the “Settings” tab.
Step 3: Put in the source data in the “Source:” box. In my example, I used the range of B4:H4. Click “OK” button to close the pop-up screen.
Now you have a drop-down list from which you can select the data.
Second way: Type in the parameters for the drop-down list.
Another way of doing it is to put the listed parameters in the “Source:” box. This way is good for drop-down menu with few choices and the choices are not supposed to be changed frequently. Please see the below example.
Third way: Name the data range and use INDIRECT function to refer to the data range.
This method uses the INDIRECT function to perform the dynamic referencing for the dropdown list. The drop-down list depends on the preceding selection. For example, the states or provinces depend on the choice of the country (USA, Canada, or China). Please follow the below steps to create the drop-down list.
Step 1: Name each data list with a name. For example, I named “Canada” for the data range of H4:H15. The name of “Canada” includes all 15 provinces of the country. I continued to name the data ranges for the United States and China. Please note that Excel does not allow you to define a name with a space between the two words. Therefore, I had to name United States as “UnitedStates” instead. I also named the data range of the country names (G3:I3) to be “StateList2”.
The “StateList2” will become the drop-down list for the “Country” field. This is actually the same way as the “first way” that is illustrated above. The only difference is that I named the data range. However, there is no necessity of naming the data range. If you put in G3:I3 in the “Source:”, it will work the same way.
Step 2: The drop-down list of the “State or Province” depends on the selection of the “Country” drop-down list. So I used the INDIRECT function to refer to the choice of the country. If the choice of the country (cell B14) is Canada, it will pick up the list of Canada. However, if the choice of the country is “United States”, Excel will not be able to pick up the list of “UnitedStates” because the two terms are different by a space. Therefore, we have to use the SUBSTITUTE function to get rid of the space in the term of “United States”. The function to be used is: SUBSTITUTE(B14,” “,””). When it is wrapped by the INDIRECT function, it becomes: =INDIRECT(SUBSTITUTE(B14,” “,””)).
Fourth way: Use the combination of OFFSET, MATCH, and INDIRECT functions to create the list.
The last method is the most complicated one and requires advanced knowledge of Excel dynamic referencing. Please refer to this article, Dynamic Referencing for Excel, for more information how to use OFFSET and MATCH functions.
The advantage of using this method is that you don’t need to name any data range as stated in the third way above. If you have more than 10 countries, naming each country’s data can be a pain. All you need is to create just one formula and define such formula with one name.
The entire formula is as follow:
=OFFSET(‘Dropdown Menu’!$G$4,0,MATCH(‘Dropdown Menu’!$B$5,’Dropdown Menu’!$G$3:$I$3,FALSE)-1,COUNTA(INDIRECT(“C”&(MATCH(‘Dropdown Menu’!$B$5,’Dropdown Menu’!$G$3:$I$3,FALSE)+6),FALSE))-1,1)
What makes this formula so complicated is that the number of states or provinces different from country to country. For example, the US has 50 states (plus Washington DC, Puerto Rico, and Virgin Islands). So the list has 53 items. But there are only 12 provinces in Canada. So the length of the list must also be taken into consideration. The COUNTA function is used to count the length of the state or province list.
The OFFSET function is used to referencing the position of the data. It starts from cell G4 because the drop-down data list starts from such cell in our example.
The MATCH function determines how many columns from cell G4. In our example, United States is in the first column from cell G4, Canada is in the second column, and China is the third column.
The INDIRECT function references the selected column. Please note that I put FALSE as the argument. So the INDIRECT refers to R1C1 cell definition instead of defaulted A1 cell definition.
