Excel Drop Down List Depending on Selection

In Microsoft Excel, two types of drop-down lists are used. Creating a drop-down list is way too easy with only a few quick steps that will need a maximum of one or two minutes. In this article, you’ll learn how you can create and then use a drop-down list depending on the selection step by step with proper illustrations.


Download Practice Workbook

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


Create a Drop Down List in Excel Depending on Selection

Step 1: Prepare an Excel Data Table with a List of Items

To create a drop-down list in Excel, first of all, we need a data table or a dataset. In the following picture, a random data table has been shown. There are two columns in the table representing several smartphone models of two popular brands.

In the secondary table at the bottom, we have to make two drop-downs in Cells C13 and C14. Cell C13 will occupy the independent drop-down with the list of two smartphone brand names and Cell C14 will show the drop-down list of smartphone models depending on the selection of the brand from the previous cell.

Prepare an Excel Data Table with a List of Items


Step 2: Use Data Validation for Selection of the Items to Make an Independent Drop Down

Let’s go through the following steps now to create an independent drop-down list first in Cell C13.

➤ Select Cell C13.

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

A dialogue box will open up.

Use Data Validation for Selection of the Items to Make an Independent Drop Down

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

➤ Enable editing in the Source box and then select the range of cells: B4:C4.

➤ Press OK.

Use Data Validation for Selection of the Items to Make an Independent Drop Down

So you’ve just made the first drop-down list with a list of smartphone brands in Cell C13. If you click on the drop-down icon in Cell C13, the list will show the defined brand names.

Use Data Validation for Selection of the Items to Make an Independent Drop Down

Read more: How to Create Dynamic Dependent Drop Down List in Excel


Step 3: Define Selection Lists with Named Range

Now we have to follow the procedures to make the next drop-down list in Cell C14 and this drop-down list will be dependent. That means the smartphone models here will be shown based on the selection from the initial drop-down list.

To do this, we have to create named ranges for two different lists of smartphone models depending on their brand names with the corresponding headers. Then we’ll assign these named ranges in the source section of the Data Validation dialogue box.

Now let’s define the named ranges first.

➤ Select the range of cells: B4:C11.

➤ Under the Formulas tab, select the Create from Selection option from the Defined Names drop-down.

A dialogue box will appear.

Define Selection Lists with Named Range

➤ Mark on the Top row option only.

➤ Leave other options unmarked.

➤ Press OK.

Define Selection Lists with Named Range

Now if you click on the Name Box drop-down situated at the top-left corner of the spreadsheet, you’ll find two brand names there as shown in the picture below. So, we’ve just created two named ranges for the smartphone models from two different brands.

Define Selection Lists with Named Range


Step 4: Create a Dependent Drop Down List Based on the Primary Selection

Here’s the final step and we have to make a dependent drop-down list in Cell C14 now. Let’s go through the following procedures.

➤ Select Cell C14.

➤ Reopen the Data Validation dialogue box.

Create a Dependent Drop Down List Based on the Primary Selection

➤ Select the option List in the Allow box.

➤ In the Source box, type the following formula:

=INDIRECT($C$13)

➤ Press OK and you’re done.

Create a Dependent Drop Down List Based on the Primary Selection

Now select a brand from the first drop-down and then click on the second drop-down, you’ll be shown the corresponding smartphone model names.

Create a Dependent Drop Down List Based on the Primary Selection

If you alter the brand names in Cell C13 and then click on the Model drop-down, another list of smartphone models will be found there.

Create a Dependent Drop Down List Based on the Primary Selection


An Example of Using Drop Down List with Selection

Let’s go through an example now which will represent the methods of using drop-down lists for analytical purposes. In the following picture, the first table on the left is showing several brand names, the corresponding smartphone models, and their prices in dollars. On the right, we’ve used a helper table to assign the model names based on the selection from Cell C16 drop-down list.

In Cell G7, the embedded formula is as follows:

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

An Example of Using Drop Down List with Selection

Now let’s find out how we can use the drop-down lists from Cell C16 and C17 to extract the price of the corresponding smartphone model.

📌 Step 1:

➤ From the Brand drop-down, select a smartphone brand, let’s say it’s Samsung.

An Example of Using Drop Down List with Selection

📌 Step 2:

➤ Now from the Model drop-down list, select any of the smartphone models.

An Example of Using Drop Down List with Selection

📌 Step 3:

➤ In Cell C18, type the following formula:

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

➤ Press Enter.

An Example of Using Drop Down List with Selection

And the function will return the price of the selected smartphone.

An Example of Using Drop Down List with Selection

Let’s change the brand name and the corresponding smartphone model from the drop-downs again.

An Example of Using Drop Down List with Selection

And the price of the selected device in Cell C18 will be updated right away.

An Example of Using Drop Down List with Selection


Concluding Words

I hope the required steps described above to create and use a drop-down list depending on the selection will now help you to apply them in your Excel spreadsheets when necessary. 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

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