In this article, we will learn How to create Dynamic Cascading Drop-Down Lists in Microsoft Excel?
The key point we will cover in this article
1. How to create a Drop Down list in Microsoft Excel?
2. How to create a Dynamic Drop list or Cascading List?
The following image shows What we will create in this video.
As per the requirement, we need to create three separate drop-down lists. The first drop-down list is for all available countries. Next, we will create another drop-down list of states. It is dependent on the first list. It means we need to display the States based on the selected country. The last and the third list is for the City. It will display cities based on the selected Country and State.
For our lists, we need sample data. Please download the following Excel file. We used the latest version of the available Excel at the time of writing this article.
|Login to Download|
Let's open the downloaded excel file. In the excel file, we have two sheets. The first one is the List, and the second is the Metadata. In the Metadata sheet, we have all the required data for the demo. We will use it for creating names. On the first sheet, we will create the required lists using the Names, which we will create in the Metadata sheet in a while.
For the drop-down list, we will first need to create all the required names. To create the first name for "Country" open Metadata sheet. In column B we have all the available countries.
Select all available countries in column B. Then in the name box provide a valid name.
In the above example, we use the quickest method to create a name.
Next, We will create a name for the Afghanistan States. This time we will use Define Names available in the Formulas tab. Select all the states for the country Afghanistan also include Afghanistan. Then click on Formulas tab and then select Define Name.
I love to use a keyboard shortcut. To open Define Name window, you can also use Alt > M > M > D keyboard key. Again Alt > M > M > D
It is a very lengthy process to create names. For our demo, we still need to create 7 more names. Let's check another method to create a name. This time select all the data for which you want to create the names. Ensure for each column you have a proper header row. For us, we need to create the name from column D to J. On A2 row we have the actual names which we need to use. Once you have selected the required data, then press Ctrl + Shift+ F3 from your keyboard. On "Create Names for Selection" dialog, select "Top row" option and then click on the OK button. Check the following image for more details.
With the above method, we created 8 names simultaneously. Next, we need to use names in the Drop down list.
Create Drop Down List
To create our first DropDown List, we need to switch back to the first sheet. Select the cell where you want to create the drop-down. In the next step, select Data -> Data Validation. In the Data Validation dialog, select List and Source equal to Country. Finally, click on OK. It will create our first list.
For the second drop-down list will use the value from the first drop-down list. To create the second drop-down list, select the cell where we want to create the state drop-down list. Again open the data validation dialog. Select the List for Allow drop-down in the source we need to use a value for the country cell. The country cell is C2. But it is not enough. For this, to work, we need to use another Excel function INDIRECT.
Similarly, create the last drop-down list for the city.
Our all the drop-downs are now ready. Let's clear all the fields. Select "Aland Islands" from the city drop-down. But for this selection, our state drop-down is not working. Why? Let's open Name manager. Available in Formulas toolbar.
Check the Country and the respective Name in the Name Manager. Selected country is Aland Islands. Name manager name is "Aland_Islands". One thing to note here. You can only use letters and underscore in names. We need to handle this limitation. To fix this issue we will use the SUBSTITUTE function to replace all not supported letters. From "Aland Islands" we need to replace space with an underscore. Following is the final value for State and City Data Validation source.
State: =INDIRECT(SUBSTITUTE($C$2," ","_"))
City: =INDIRECT(SUBSTITUTE($C$3," ","_"))
Following image shows the final output.
What is next? We already discuss the space limitation. We already need to handle other special characters such as single quote, parenthesis, commas and others.
Please login to post your valuable comments.