If you need to create a drop-down list that changes based on what you select in another cell, adding a condition to the drop-down list can help you solve this problem. To create a conditional drop-down list, the first method that comes to mind is to use the IF statement, since it is always used to test for conditions in Excel. This tutorial demonstrates 5 methods to help you create a conditional drop-down list in Excel step-by-step.
This section provides two functions: the IF function and the IFS function to help you create a conditional drop-down list based on other cells in Excel with two examples.
As shown in the gif below, you can easily switch between cities in two countries “United States and France” in the drop-down list. Let's see how to use an IF function to get it done.
First you need to create a main drop-down list from which you want to make a conditional drop-down list.
1. Select a cell (E2 in this case) where you want to insert the main drop-down list. Go to the Data tab, select Data Validation.
2. In the Data Validation dialog box, you need to configure as follows.
1. Select the range of cells (In this case I select E3:E6) where you want to insert the conditional drop-down list.
2. Go to the Data tab, select Data Validation.
3. In the Data Validation dialog box, you need to configure as follows.
=IF($E$2=$B$2,$B$3:$B$6,$C$3:$C$6)
The conditional drop-down list is now complete.
As shown in the gif image below, if you want to select a city in United States, click on E2 to select Cities in United States from the drop-down list. Then select any city belonging to United States in the cells below E2. To select a city in France, do the same operation.
As shown in the gif image below, there are two tables. The one column table contains different countries, while the multi-column table contains cities in those countries. Here we need to create a conditional drop-down list that contains cities that will change according to the country you choose in E10, please follow the steps below to complete.
1. Select a cell (Here I select E10) where you want to display the country, go to the Data tab, click Data Validation.
2. In the Data Validation dialog box, you need to:
The drop-down list contains all countries is now complete.
1. Select the entire range of the cities table, go to the Formulas tab, click Create from Selection.
2. In the Create Names from Selection dialog box, only check the Top row option and click the OK button.
1. Select a cell (here I select E11) to output the conditional drop-down list, go to the Data tab, select Data Validation.
2. In the Data Validation dialog box, you need to:
=IF($E$10="Japan",Japan,IF(E10="Tunisia",Tunisia,IF(E10="United States",United_States, France)))
=IFS(E10="Japan",Japan,E10="Tunisia",Tunisia,E10="United States",United_States,E10="France", France)
The above methods are troublesome for most Excel users. If you need an easier way, here the Dynamic Drop-down Listfeature of Kutools for Excel is highly recommended to help you create a conditional drop-down list with just a few clicks.
As you can see, the whole operation can be done in just a few clicks. You just need to:
Instead of the IF and IFS functions, you can use a combination of the INDIRECT and SUBSTITUTE functions as another alternative option to create a conditional drop-down list, which is simpler than the formulas we provided above.
Take the same example used in the multiple conditions above (as shown in the gif image below). Here I will show you how to use the combination of the INDIRECT and SUBSTITUTE functions to create a conditional drop-down list in Excel.
1. In cell E10, create the main drop-down list containing all countries. Follow the above step 1.
2. Name the cell range for the cities under each country. Follow the above step 2.
3. Use the INDIRECT and SUBSTITUTE functions to create a conditional drop-down list.
Select a cell (E11 in this case) to output the conditional drop-down list, go to the Data tab, select Data Validation. In the Data Validation dialog box, you need to:
=INDIRECT(SUBSTITUTE(E10," ","_"))
A conditional drop-down list with a combination of the INDIRECT and SUBSTITUTE functions is now created.
Autocomplete when typing in Excel drop down list
If you have a data validation drop down list with large values, you need to scroll down in the list just for finding the proper one, or type the whole word into the list box directly. If there is method for allowing to auto complete when typing the first letter in the drop down list, everything will become easier. This tutorial provides the method to solve the problem.
Create drop down list from another workbook in Excel
It is quite easy to create a data validation drop down list among worksheets within a workbook. But if the list data you need for the data validation locates in another workbook, what would you do? In this tutorial, you will learn how to create a drop fown list from another workbook in Excel in details.
Create a searchable drop down list in Excel
For a drop down list with numerous values, finding a proper one is not an easy work. Previously we have introduced a method of auto completing drop down list when enter the first letter into the drop down box. Besides the autocomplete function, you can also make the drop down list searchable for enhancing the working efficiency in finding proper values in the drop down list. For making drop down list searchable, try the method in this tutorial.
Auto populate other cells when selecting values in Excel drop down list
Let’s say you have created a drop down list based on the values in cell range B8:B14. When you selecting any value in the drop down list, you want the corresponding values in cell range C8:C14 be automatically populated in a selected cell. For solving the problem, the methods in this tutorial will do you a favor.