Nehad Ulfat
Drop down list can make tasks easy for users by giving them options to insert into a cell. 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 Excel drop-down list depending on selection step by step with proper illustrations in Excel.
You can see a drop down list in Cell C14 which is dependent on the value of Cell C13. In this article, we will show you different examples of creating drop down lists depending on selection.
Download Practice Workbook
You can download the Excel workbook that we used to prepare this article.
Excel Drop Down List Depending on Selection: 5 Suitable Examples
A drop down list can be independent or dependent on another primary selection. You can make a drop down list by using the Data Validation feature, Excel table or VBA in Excel. Here, we will show you 5 suitable examples of creating a excel drop down list depending on selection.
1. Use Data Validation for Selection of Items to Make Independent Drop Down List
In the first example, we will make use of the Data Validation feature for selection of items to make an independent drop down list. An independent drop down list contains the options which are not dependent on any other value.
Step-01: Prepare Excel Data Table with 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 a drop-down in Cells C13. It will occupy the independent drop-down with the list of two smartphone brand names.
Read More: Create Excel Drop Down List from Table (5 Examples)
Step-02: Use Data Validation Feature from Data Tab
Now, we will use the Data Validation feature from the Data tab to create an independent drop down list.
- Select Cell C13 >> Under the Data ribbon, choose the Data Validation.
- 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 and 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.
2. Create Dependent Drop Down List Based on Primary Selection
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.
Step-01: Define Selection Lists with Named Range
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.
A dialogue box will appear.
- Mark on the Top row option only and 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-02: Use INDIRECT Function for Data Validation
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 and reopen the Data Validation dialogue box.
- Select the option List in the Allow box.
- In the Source box, type the following formula.
=INDIRECT($C$15)- Finally, press OK and you’re done.
- Now select a brand from the first drop-down and then click on the second drop-down, and 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. Create Drop Down List Using Excel Table
Excel tables have built-in filter options. So, you can make a drop down list based on specific criteria by using this table.
To do that, you need to make a table using your dataset and then use the Data Validation feature.
Here, we have a dataset containing the name of some Fruits. Now we will create a drop down list in Cell D5 to select any fruit from that list.
Here are the steps to do that.
- Select cell range B4:B11 >> Press Ctrl + T.
- Create Table dialog box will open.
- Turn on My table has headers option and press OK.
- Thus, a table will be created with headers.
- Select Cell D5 and reopen the Data Validation dialogue box going through the steps shown in Example 1.
- Select the option List in the Allow box.
- In the Source box, type the following formula >> press OK.
=INDIRECT("Table1[Fruits]")
- Now, a drop down list will be created in Cell D5 containing all the values we selected.
Read More: Excel Formula Based on Drop-Down List (6 Suitable Examples)
4. Use VBA to Make Drop Down List with Selection Property in Excel
Using VBA you will not have to go through all the steps we showed before to create it.
Instead, you can just run a macro and simply create a list every time you want to make a drop down list. You only will need to change the cell range in the code to change the selection values of your drop down list.
- Go to the Developer tab >> click on Visual Basic.
- Now, the Microsoft Visual Basic Editor will open.
- Lastly, to add a module, click on Insert >> select Module.
- Insert the following code into your module.
Sub Drop_Down_List()
Dim Fruit_List As Range
Set Fruit_List = Range("B5:B11")
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Fruit_List.Address
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End SubCode Breakdown
- We created a Range variable called Fruit_List and set its value as cell range B5:B11.
- Then, we used the Selection property to select a cell and the Validation property to make a drop down list in that cell.
- We used the Delete method to remove if any list is already created there.
- Next, we added a new validation with the following parameters.
- xlValidateList as Add Type to add a list.
- xlValidAlertStop as AlertStyle to display a warning message if an invalid value is entered.
- “=” & Fruit_List.Address as Formula1 to set the cell reference for the drop down list.
- We added some additional properties of the validation, such as ignoring blank cells and showing input and error messages.
- Go back to your Excel worksheet. Select Cell D5.
- Then, go to the Developer tab >> click on Macros.
- Select Drop_Down_List macro and click on Run.
- Thus, you can create a drop down list in Excel using VBA.
Similar Readings
- How to Fill Drop-Down List Cell in Excel with Color but with No Text
- [Fixed!] Drop Down List Ignore Blank Not Working in Excel
- How to Create Dependent Drop Down List with Multiple Words in Excel
- VBA to Select Value from Drop Down List in Excel (2 Methods)
- How to Link a Cell Value with a Drop Down List in Excel (5 Ways)
5. Create Drop Down List Using Selected Range from Another Worksheet in Excel
You can also create a drop down list by selecting the cell range from another worksheet in Excel.
Here, we have a dataset containing the name of some Fruits in the Fruit List named worksheet.
We want to create a drop down list using these values in Cell C4 of Another Worksheet named worksheet.
Here are the steps to do that.
- Select Cell Cell C4 from Another Worksheet named worksheet.
- Under the Data ribbon, choose the Data Validation command.
- Select the option List in the Allow box in the Data Validation dialogue box.
- In the Source box, type the following formula.
='Fruit List'!$B$5:$B$11- Finally, press OK and you’re done.
- Finally, a drop down list will be created containing all the values we selected.
How to Create Dynamic Drop Down List in Excel
A dynamic drop down list is useful when you need to update your list according to the changes in the cell range. It will automatically update the list if any addition or removal of values are done in the dataset or cell range.
1. Define Named Range to Create Dynamic Drop Down List in Excel
You can create a dynamic drop down list by defining named ranges in Excel. To do that, we will use the Define Name feature and the OFFSET and COUNTA functions.
Follow the given steps to do that.
- To define the named range, go to the Formulas tab >> click on Define Name.
- New Name dialogue box will pop up.
- Give a name to your range. Here we inserted Select_Fruit as Name.
- Then, insert the following formula in the Refers to box and click on OK.
=OFFSET('Named Range'!$B$5,0,0,COUNTA('Named Range'!$B:$B),1)
Formula Breakdown
- We used the OFFSET function to create a dynamic range. In the function, we set Cell C5 of the Named Range worksheet as the starting point of the range.
- The next two arguments specify the number of rows and columns to move from the starting point. In this case, both arguments are zero, so the function starts at Cell B5.
- Then, the COUNTA function is used to count the number of non-empty cells in column B of the Named Range worksheet.
- Select Cell D5 and open the Data Validation dialogue box going through the steps shown in Example 1.
- Select the option List in the Allow box.
- In the Source box, insert your named range. Here, we inserted the Select_Fruits range.
- Finally, press OK.
- You can see that a drop down is created containing the values in Column B and if we add new values the list will automatically get updated.
2. Use UNIQUE & SORT Functions to Make Dynamic Drop Down List with Unique Values in Excel
In Excel 365 or Excel 2021, you can make a dynamic drop down list with only unique values in the dataset using the UNIQUE and SORT functions.
Suppose we have a dataset containing some name of Fruits, their sales Region and Quantity. Now, we will create a drop down list of Unique Fruit names in Cell F5.
- To do that, select Cell F5 and insert the following formula.
=SORT(UNIQUE(B5:B13))- Press Enter and you will get all the unique values in that column sorted alphabetically.
- To create a dynamic drop down list using this value, select Cell F10 and open the Data Validation dialogue box going through the steps shown in Example 1.
- Then, select the option List in the Allow box.
- In the Source box, insert Cell F5 and add # after that to make the list dynamic.
- Press OK.
- Thus, a dynamic drop down list will be created using these functions.
How to Allow Multiple Selections in Drop Down List in Excel
Suppose you want to allow multiple selections in your drop down list in Excel. To do that you have to add the following code we have given below to your worksheet where you want to allow multiple selections.
- Open the Microsoft Visual Basic Editor going through the steps shown in Example 4.
- Double-click on the sheet where you want to allow multiple selections in the drop down list. Here we selected Sheet9.
- Insert the following code into that sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim value1 As String
Dim value2 As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$D$5" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
value2 = Target.Value
Application.Undo
value1 = Target.Value
If value1 = "" Then
Target.Value = value2
Else
If InStr(1, value1, value2) = 0 Then
Target.Value = value1 & ", " & value2
Else:
Target.Value = value1
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub- Now, you will be able to allow multiple selections from that drop down list.
How to Create Error Alert for Other Entries in Drop Down List in Excel
When a user inserts a value that is not in the drop down list you can alert them using an error message in Excel.
- Open the Data Validation toolbox.
- Go to the Error Alert option.
- Turn on the Show error alert after invalid data is entered option. Choose a Style and add Title and Error message >> click on OK.
- Now, if you enter a value that is not included in the list it will show an alert message like below.
How to Add Input Message with Drop Down List in Excel
You can also add a message with the drop down list in Excel to make the use of that list easier for the user. It can explain what this list is about.
Open the Data Validation toolbox >> Go to the Input Message option.
- Turn on the Show input message when cell is selected option. Add Title and Input message >> click on OK.
- Thus, you can add an input message with a drop-down list in Excel.
How to Add or Remove Items in Excel Drop Down List
If your drop-down list is not dynamic, then you can follow the steps given below to add or remove data from your dataset which will automatically add or remove items in your Excel drop down list.
- Select the cell above which you want to add data and right-click on it >> Select Insert option.
- Select the Shift cells down option and press OK.
- Now, if you add a value to that blank cell that item will be added to the list automatically.
Read More: How to Add Item to Drop-Down List in Excel (5 Methods)
Frequently Asked Questions
1. How to Copy Already Created Drop Down List to Other Cells?
To copy an already created drop down list, select that cell and press Ctrl + C and then select the cells where you want to paste that list and press Ctrl + V.
2. How to Remove Drop Down List from Cell in Excel?
To remove the drop-down list from a cell in Excel select that cell and open the Data Validation toolbox. Then, click on Clear All.
3. Is a drop-down list the same as data filtering?
No, a drop-down list is not the same as data filtering. You can filter data for different conditions, and it is added to the header of each column. However, you can use a drop-down list to insert a value in a particular cell by selecting an item from that list.
Conclusion
We hope the required steps described above to create and use Excel drop-down list depending on selection will now help you to apply them in your 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.
Related Articles
- Hide or Unhide Columns Based on Drop Down List Selection in Excel
- How to Create a Form with Drop Down List in Excel
- Creating a Drop Down Filter to Extract Data Based on Selection in Excel
- Excel Drop Down List Not Working (8 Issues and Solutions)
- How to Create Multi Select ListBox in Excel (With Easy Steps)
- Excel Formula Based on Drop-Down List (6 Suitable Examples)
- VLOOKUP with Drop Down List in Excel
