Conditional Drop Down List in Excel (Create, Sort and Use)

In Microsoft Excel, the applications of the conditional or dependent drop-down list are immense in the field of data analysis. And creating this drop-down list in Excel is also fun if you know the steps accurately. In this article, you’ll learn how you can create, sort, and use a conditional drop-down list in Excel with proper illustrations.

How to Create Conditional Drop Down List in Excel: 4 Ways

1. Create a Conditional Drop Down List with Classified Data Table

In our first criterion, we’ll make a dependent drop-down list from some organized data. In the picture below, you’re now seeing two columns containing smartphone models of two different renowned brands.

In Cell C13, we’ll create an independent drop-down list for the brand types. After that, we’ll make a dependent drop-down list in Cell C14 where smartphone models will be shown in a list based on the selected brand from the previous drop-down list.

📌 Step 1:

➤ Select Cell C13.

➤ Under the Data tab, choose the Data Validation command from the Data Tools drop-down.

📌 Step 2:

➤ In the Allow box, select List from the options.

➤ Activate editing in the Source box and select the range of cells (B4:C4) containing the brand names of the smartphones.

➤ Press OK.

So, we’ve just created an independent drop-down list for the smartphone brands in Cell C13.

📌 Step 3:

➤ Now select the entire table or the range of cells B4:C11.

➤ Under the Formulas ribbon, choose the Custom from Selection command from the Defined Names drop-down.

A dialogue box will appear.

📌 Step 4:

➤ Put a mark on the first option ‘Top row’ only and leave other options unmarked.

➤ Press OK.

So, we’ve just created two named ranges for two different smartphone brands with their corresponding models.

📌 Step 5:

➤ Since we have to create a dependent drop-down list in Cell C14 now,  select the mentioned cell first.

➤ Open the Data Validation dialogue box again.

➤ Choose List in the Allow box.

➤ In the Source box, type the following formula:

➤ Press OK and you’re done.

By using the INDIRECT function here, we’ve mentioned the cell reference of C13. The function will store the smartphone models in arrays for two different brands.

Now select a smartphone brand from the independent drop-down in Cell C13 and then click on the dependent drop-down button in Cell C14, you’ll find all the smartphone models of the selected brand.

Alter the smartphone brand and you’ll find the corresponding smartphone models only, as shown in the screenshot below.

 Read More: How to Use IF Statement to Create Drop-Down List in Excel

2. Make a Conditional Drop Down List with Unorganized Data Table

Now we’re going to work with a modified data table. In the following picture, you see the smartphone models of two brands together in a column. They have not been classified into two different lists. With this data table, we have to make a dependent drop-down list in Cell C17. Now in this section, we’ll need a helper table where we’ll store the filtered smartphone models based on the selection from the independent drop-down.

📌 Step 1:

➤ Select Cell C16 where we have to make an independent drop-down list first for the brand names.

➤ Under the Data tab, choose the Data Validation option from the Data Tools drop-down.

A dialogue box will open up.

📌 Step 2:

➤ In the Allow box, select List from the options.

➤ Enable editing in the Source box and then select the range of cells (E7:E8) containing the brand names.

➤ Press OK.

Our independent drop-down list is not ready to show the assigned values. Let’s select an option now from the list Samsung.

Now, by using the FILTER function, we’ll filter the smartphone models based on the selection of the Brand from the drop-down list and show them in a spill range in Cell F7.

📌 Step 3:

➤ Select Cell F7 and type the following formula:

➤ Press Enter.

And you’ll find all models of the Samsung brand here in a vertical array under the Model header in the Helper Table.

📌 Step 4:

➤ Now select Cell C17 where the dependent drop-down list will be formed.

➤ Open Data Validation dialogue box again.

➤ Select the option List in the Allow box.

➤ In the Source box, type:

With the use of Hash (#) here, we’re defining the spill range starting from Cell C17.

➤ Press OK.

So, our dependent drop-down list is also prepared to display the assigned items.

Alter the brand name from the Brand drop-down and you’ll find the corresponding smartphone models in the Model drop-down list.

Read More: Excel Formula Based on Drop-Down List

3. Construct Multiple Conditional Drop Down Lists in Excel

In our data table, we’ve added a new column with the RAM header. We’ll create two conditional drop-down lists here in Cells D17 and D18, one is for RAM and another one is for the smartphone model. It means we’ll select the available RAM first from the drop-down list based on the selected smartphone brand and then the corresponding smartphone models will show up in the second dependent drop-down.

📌 Step 1:

➤ Select Cell C16 and open the Data Validation dialogue box.

➤ Choose the option ‘List’ in the Allow box.

➤ Select the range of cells (F7:F8) containing the brand names for the Source box.

➤ Press OK.

We’ve just created an independent drop-down in Cell C16 where the brand names will be shown only from the list.

📌 Step 2:

➤ Select Cell G7 and type the following formula consisting of UNIQUE and FILTER functions:

➤ Press Enter.

We’re using the UNIQUE function here to show each different RAM only once in the spill range.

So, now you’re seeing a vertical array in Cell G7 with the available RAMs from the data table for the Samsung devices only.

📌 Step 3:

➤ Now select Cell C17 where we’ll assign the RAMs based on the selection of the brand.

➤ Open Data Validation dialogue box.

➤ Select List from the options in the Allow box.

➤ In the Source box, type:

➤ Press OK.

So, our first dependent drop-down list is now complete. Once you click on the drop-down from Cell C17, you’ll find the available RAMs for the selected smartphone brand.

📌 Step 4:

➤ Select Cell H7 and type:

➤ Press Enter.

In this cell, we’re now assigning the model names for the selected brand and RAM with the FILTER function.

So, the smartphone model names under the selected criteria are now visible with a spilled range from Cell H7.

📌 Step 5:

➤ Select Cell C18 to create the second drop-down list.

➤ Open Data Validation dialogue box.

➤ Choose List in the Allow box from the options.

➤ In the Source box, type:

➤ Press OK and you’re done.

Now you’ll see the smartphone model names in the drop-down list in Cell C18.

Alter the brand name and RAM from the first two drop-downs and you’ll be displayed the corresponding device models in the drop-down list in Cell C18

Read More: How to Create Dependent Drop Down List with Multiple Words in Excel

4. Prepare an Expandable Drop Down List in Excel

Let’s think of a scenario where you’ll add data in the table and these data will be automatically assigned in the drop-down lists. This is why five more blank cells at the bottom of the table have been created now to add more data. Assuming that our data table is already prepared to display the assigned items from the drop-down lists. But these drop-down lists are unable to add new data. So, we’ll make some modifications here to make the drop-down lists work perfectly with the newly added data in the table.

📌 Step 1:

➤ Select Cell E7 and type the following formula:

➤ Press Enter.

With this formula, we’re filtering the values from the range of cells (B5:B19) in Column B to show each different brand name only once. And this formula will ignore the empty cells and won’t return them as another distinct value.

So, from Cell E7, the brand names are now visible with a spilled range.

📌 Step 2:

➤ Now select Cell F7 and type:

➤ Press Enter.

With this formula, we’re defining the smartphone models based on the selected brand from the drop-down list in Cell F16.

Assuming that Cell F16 has the brand name Xiaomi, so the available and corresponding smartphone models will be shown in a vertical array from Cell F7 after embedding the mentioned formula.

📌 Step 3:

➤ Select Cell F16 now and activate the Data Validation dialogue box. We have to assign an updated drop-down list here.

➤ Choose List from the options in the Allow box.

➤ In the Source box, type:

➤ Press Enter.

So, now when you’ll add a new brand name to the table, the spilled range from Cell E7 will be updated. And if you click on the Brand drop-down then, you’ll find the updated brand name there in the list.

Finally, add some smartphone models in the table for the corresponding brand and you’ll find those newly added names of the devices in the Model drop-down list. This is how you can easily create an expandable drop-down list with the referred steps.

Sort a Conditional Drop Down List in a Specified Order

The assigned items in the drop-down list will not be in any order if you don’t define the order. So, we have to use the SORT function in the Helper Table for our dataset to show the selected values in a specified order.

1. Sort a Dependent Drop Down List with Alphabetical Order in Excel

In our first example, as shown in the following picture, you’ll find the smartphone model names in a random order in the drop-down list which might look quite odd. So, we can sort these data alphabetically (From A to Z) by modifying the formula in Cell H7.

So, in Cell H7, the modified formula combining the SORT and FILTER functions will be:

In this formula, we’re defining the order with ‘1’ in the third argument (sort_order) of the SORT function. This will sort the data in ascending order (from A to Z for alphabets). If we use ‘-1’ as the sort_order, it’ll denote the descending order for numerical data and Z to A for alphabets.

After pressing Enter, the formula will return the sorted data from  Cell H7 as shown in the picture below. Now click on the Model drop-down icon and you’ll find the items in the specified order. Here the items starting with numerical digits will always be at the beginning.

2. Sort a Conditional Drop Down List with Ascending or Descending Order

Let’s sort the memory storage of the RAMs now that are visible from Cell G7 with a spilled range. The RAM drop-down list also stores the order followed by that spilled range.

To sort the memory storage of RAMs in ascending order, we have to assign the following formula in Cell G7:

After pressing Enter, the spilled range from Cell G7 will show the numerical values in ascending order. Now click on the RAM drop-down and you’ll find the items too in the specified order.

How to Use Conditional Drop Down List in Excel: A Practical Example

Let’s go through an example to understand how we can use this conditional drop-down in Excel to extract data based on a lookup value. In the following dataset, the Price column has been added to the primary table containing two smartphone brands and corresponding model names only. What we’ll do here is select a brand and the corresponding smartphone model and then display the price of the product with a lookup (XLOOKUP in this section) function.

For example, we want to display the price of the handset ‘Xiaomi 11T’ after two selection processes from the drop-down lists.

📌 Step 1:

➤ Select Xiaomi from the Brand drop-down list first.

➤ Choose the handset model 11T from the Model drop-down list.

📌 Step 2:

➤ In the output Cell C18, where the price will be displayed, the required formula to look up the price of the selected device will be:

After pressing Enter, the price of the selected device will be shown right away.

Now change the smartphone brand and the corresponding model from the drop-down lists.

And the output data in Cell C18 will be updated immediately for the newly selected device.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Concluding Words

I hope all the methods and criteria to create a conditional drop-down list as well as a suitable use of the drop-down lists will now help you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

Related Articles

• How to Create Dynamic Dependent Drop Down List in Excel
• How to Make Dependent Drop Down List with Spaces in Excel
• Create Excel Filter Using Drop-Down List Based on Cell Value
• How to Extract Data Based on a Drop Down List Selection in Excel
• How to Populate List Based on Cell Value in Excel
• How to Change Drop Down List Based on Cell Value in Excel

<< Go Back to Excel Dependent Drop Down List | Excel Drop-Down List | Data Validation in Excel | Learn Excel