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

When analyzing large amounts of data, the use of drop-down lists streamlines the experience. And you can make this drop-down list within a minute in Microsoft Excel. In this article, you’ll get to know all the procedures to make a drop-down list with appropriate illustrations.


Download Practice Workbook

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


How to Make Two Types of Drop Down List in Excel

There are two types of drop-down lists in Microsoft Excel: Independent and Dependent. In the Independent Drop Down list, the values are not assigned to any other criteria. But the values in the Dependent Drop Down list are connected to a particular criterion or a named range.

We can start with an Independent Drop Down list first. Follow the steps carefully and you’ll find them convenient to apply in your Excel spreadsheets.

1. Create an Independent Drop Down List in Excel

In the following picture, you’re seeing a list of popular Samsung smartphones. Now we’ll make a drop-down list in Cell E7 from where you can select any smartphone model listed in the chart.

Create an Independent Drop Down List in Excel

📌 Step 1:

➤ Select the output Cell E7.

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

A dialogue box will open up.

Create an Independent Drop Down List in Excel

📌 Step 2:

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

➤ Click on the Source box and select the range of cells (B5:B11) containing the model names of the smartphones.

➤ Press OK and you’re done.

Create an Independent Drop Down List in Excel

📌 Step 3:

➤ Click on the drop-down button in Cell E7, you’ll find the list of Samsung smartphones that are present in the table.

Create an Independent Drop Down List in Excel

Now you can select any smartphone model and use it later as any other input data in a formula.

Create an Independent Drop Down List in Excel


2. Create a Dependent Drop Down List in Excel

Now we’ll make a dependent drop-down list and this category is mostly used in practical approaches. 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 Dependent Drop Down List in Excel

📌 Step 1:

➤ Select Cell C13.

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

Create a Dependent Drop Down List in Excel

📌 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 Dependent Drop Down List in Excel

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

Create a Dependent Drop Down List in Excel

📌 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 Dependent Drop Down List in Excel

📌 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 Dependent Drop Down List in Excel

📌 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 Dependent Drop Down List in Excel

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 Dependent Drop Down List in Excel

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

Create a Dependent Drop Down List in Excel


An Example of Using Drop Down List in Excel

Let’s go through an example now where we’ll use an independent drop-down list to extract multiple values from a dataset. In the following picture, our dataset is representing several order IDs for smartphone devices with the corresponding details.

In the output table situated at the bottom, an independent drop-down list in Cell E15 has been created containing a list of order IDs. What we’ll do is embed a formula in Cell E17 and extract all the available data for an order ID selected from the drop-down list.

An Example of Using Drop Down List in Excel

📌 Step 1:

➤ Select the output Cell E17 and type the following formula:

=TRANSPOSE(VLOOKUP(E16,B5:G14,{2,3,4,5,6},FALSE))

➤ Press Enter.

An Example of Using Drop Down List in Excel

You’ll see all corresponding details for the selected order ID as displayed in the following screenshot.

An Example of Using Drop Down List in Excel

📌 Step 2:

➤ Now choose another order ID from the drop-down list.

An Example of Using Drop Down List in Excel

The output data will be updated right away with the selection of another order ID.

An Example of Using Drop Down List in Excel


Concluding Words

I hope the procedures of making two types of drop-down lists 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

VLOOKUP with Drop Down List in Excel

VLOOKUP To Compare Two Lists (Same or Different Sheets)

How to Create Custom Sort List in Excel

Generate Random Number from List in Excel (4 Ways)

Excel Compare Two Lists and Return Differences (7 Ways)

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