A Dynamic dropdown list or dependent dropdown list is very useful while working in Excel. Which makes our work easier and time get saved.
Here we have an example of how to create a dynamic dropdown list or dependent dropdown.
Example-
We have names of some states and their cities. While working in Excel, you should create a dropdown list so that you do not have to type the name of the state or it’s city repeatedly. If we select the state name in dropdown the list of cities of that state will appear automatically.
We have the following data. In which dropdown list is to be created in column F3 to G6.
Steps-
To create a dropdown list of state names
- Select F3 to F6
- In the Data tab, Click on Data Validation.
- In the dialog box that opens, select “List” in Allow.
4. Place the cursor in the source, and select A2 to D2.
5. Click OK.
6. The state name dropdown is ready. See the image below.
To create dropdown list of cities according to states.
- Select all source data with headers ( A2 to D7)
- Press Ctrl + T.
- In the dialog box that opens, Check on “my table has headers”
- Click OK.
5. Select all source data with headers ( A2 to D7)
6. Go on Formula Tab.
7. Click on “Create from selections”
8. The dialog box that opens, Check on “Top row”
9. Click OK.
10. Select G3 to G6.
11. In the Data tab, Click on Data Validation.
12. In the dialog box that opens, select “List” in Allow.
13. In the source box, Type =indirect(F3)
14. Click OK
Result- When you select a state in column F, a dropdown list of its cities appears in column G. see the image below.
