Nehad Ulfat
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.
Read More: Create Excel Drop Down List from Table (5 Examples)
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.
➤ 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.
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.
Read More: How to Make a Drop Down List in Excel (Independent and Dependent)
Similar Readings
- Hide or Unhide Columns Based on Drop Down List Selection in Excel
- How to Select from Drop Down and Pull Data from Different Sheet in Excel
- Remove Used Items from Drop Down List in Excel (2 Methods)
- How to Remove Duplicates from Drop Down List in Excel (4 Methods)
- Unique Values in a Drop Down List with VBA in Excel (A Complete Guide)
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.
➤ Mark on the Top row option only.
➤ Leave other options unmarked.
➤ Press OK.
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.
Read More: How to Create Drop Down List in Excel with Multiple Selections
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.
➤ 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.
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.
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.
Read More: How to Make Multiple Selection from Drop Down List in Excel (3 Ways)
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)
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.
📌 Step 2:
➤ Now from the Model drop-down list, select any of the smartphone models.
📌 Step 3:
➤ In Cell C18, type the following formula:
=XLOOKUP(C17,C5:C14,D5:D14,,0,1)➤ Press Enter.
And the function will return the price of the selected smartphone.
Let’s change the brand name and the corresponding smartphone model from the drop-downs again.
And the price of the selected device in Cell C18 will be updated right away.
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.
