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.


Download Practice Workbook

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


4 Ways to Create Conditional Drop Down List in Excel

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.

Create a Conditional Drop Down List with Classified Data Table

📌 Step 1:

➤ Select Cell C13.

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

Create a Conditional Drop Down List with Classified Data Table

📌 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.

Create a Conditional Drop Down List with Classified Data Table

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

Create a Conditional Drop Down List with Classified Data Table

📌 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.

Create a Conditional Drop Down List with Classified Data Table

📌 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.

Create a Conditional Drop Down List with Classified Data Table

📌 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:

=INDIRECT($C$13)

➤ 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.

Create a Conditional Drop Down List with Classified Data Table

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.

Create a Conditional Drop Down List with Classified Data Table

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

Create a Conditional Drop Down List with Classified Data Table


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’re seeing 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.

Make a Conditional Drop Down List with Unorganized Data Table

📌 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.

Make a Conditional Drop Down List with Unorganized Data Table

📌 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.

Make a Conditional Drop Down List with Unorganized Data Table

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

Make a Conditional Drop Down List with Unorganized Data Table

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:

=FILTER(C5:C14,B5:B14=C16)

➤ Press Enter.

Make a Conditional Drop Down List with Unorganized Data Table

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

Make a Conditional Drop Down List with Unorganized Data 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:

=$F$7#

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

➤ Press OK.

Make a Conditional Drop Down List with Unorganized Data Table

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

Make a Conditional Drop Down List with Unorganized Data Table

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

Make a Conditional Drop Down List with Unorganized Data Table


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.

Construct Multiple Conditional Drop Down Lists in Excel

📌 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.

Construct Multiple Conditional Drop Down Lists in Excel

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

Construct Multiple Conditional Drop Down Lists in Excel

📌 Step 2:

➤ Select Cell G7 and type the following formula:

=UNIQUE(FILTER(C5:C14,B5:B14=C16))

➤ Press Enter.

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

Construct Multiple Conditional Drop Down Lists in Excel

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

Construct Multiple Conditional Drop Down Lists in Excel

📌 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:

=$G$7#

➤ Press OK.

Construct Multiple Conditional Drop Down Lists in Excel

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.

Construct Multiple Conditional Drop Down Lists in Excel

📌 Step 4:

➤ Select Cell H7 and type:

=FILTER(D5:D14,(B5:B14=C16)*(C5:C14=C17))

➤ Press Enter.

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

Construct Multiple Conditional Drop Down Lists in Excel

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

Construct Multiple Conditional Drop Down Lists in Excel

📌 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:

=$H$7#

➤ Press OK and you’re done.

Construct Multiple Conditional Drop Down Lists in Excel

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

Construct Multiple Conditional Drop Down Lists in Excel

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

Construct Multiple Conditional Drop Down Lists 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.

Prepare an Expandable Drop Down List in Excel

📌 Step 1:

➤ Select Cell E7 and type the following formula:

=UNIQUE(FILTER(B5:B19,B5:B19<>""))

➤ 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.

Prepare an Expandable Drop Down List in Excel

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

Prepare an Expandable Drop Down List in Excel

📌 Step 2:

➤ Now select Cell F7 and type:

=FILTER(C5:C19,B5:B19=F16)

➤ Press Enter.

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

Prepare an Expandable Drop Down List in Excel

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.

Prepare an Expandable Drop Down List in Excel

📌 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:

=$E$7#

➤ Press Enter.

Prepare an Expandable Drop Down List in Excel

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.

Prepare an Expandable Drop Down List in Excel

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.

Prepare an Expandable Drop Down List in Excel


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.

Sort a Dependent Drop Down List with Alphabetical Order in Excel

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

=SORT(FILTER(D5:D14,(B5:B14=C16)*(C5:C14=C17)),,1)

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.

Sort a Dependent Drop Down List with Alphabetical Order in Excel

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.

Sort a Dependent Drop Down List with Alphabetical Order in Excel


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 is also storing the order followed by that spilled range.

Sort a Conditional Drop Down List with Ascending or Descending Order

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

=SORT(UNIQUE(FILTER(C5:C14,B5:B14=C16)),,1)

Sort a Conditional Drop Down List with Ascending or Descending Order

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.

Sort a Conditional Drop Down List with Ascending or Descending Order


An Example of How to Use Conditional Drop Down List in Excel

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.

An Example of How to Use Conditional Drop Down List in Excel

📌 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:

=XLOOKUP(C17, C5:C14, D5:D14)

An Example of How to Use Conditional Drop Down List in Excel

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

An Example of How to Use Conditional Drop Down List in Excel

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

An Example of How to Use Conditional Drop Down List in Excel

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

An Example of How to Use Conditional Drop Down List in Excel


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.


You May Also Like to Explore

How to Make a Drop Down List in Excel (Independent and Dependent)

VLOOKUP with Drop Down List in Excel

How to Create Dynamic Dependent Drop Down List in Excel

How to use IF Statement to Create a Drop-Down List in Excel

How to Remove Drop-Down List in Excel

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo