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.

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. However, 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.

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

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

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

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

Read More: How to Create Drop Down List with Filter 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.

📌 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 Create a Drop Down List with Unique Values 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 represents 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.

📌 Step 1:

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

➤ Press Enter.

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

📌 Step 2:

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

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

Download Practice Workbook

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

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.