Excel Drop Down List Depending on Selection (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Drop Down List Selection

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.

Excel Data Table with List of Items

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.

Using Data Validation Feature From Data Tab

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

Opening Data Validation toolbox

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

Making Independent Drop Down List


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.

Dataset to Create Dependent 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.

Defining Selection Lists with Named Range

A dialogue box will appear.

  • Mark on the Top row option only and Leave other options unmarked >> press OK.

Selecting Top row as 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.

Named Ranges

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.

Using Data Validation feature to create Dependent Drop Down List

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

Using INDIRECT Function for Data Validation

Here, insert we used the INDIRECT function which can store cell references to connect the list with Cell C13 to make a dependent drop down list.
  • 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.

Drop Down List for Samsung

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

Drop Down List for Xiaomi

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.

Dataset to Create Drop Down List Using Excel Table

Here are the steps to do that.

  • Select cell range B4:B11 >> Press Ctrl + T.

Creating Excel Table

  • Create Table dialog box will open.
  • Turn on My table has headers option and press OK.

Opening Create Table box

  • Thus, a table will be created with headers.

Fruits table

  • 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]")

Using INDIRECT Function on Excel Table

In the formula, insert your table and column name in the function to create your desired drop down list.
  • Now, a drop down list will be created in Cell D5 containing all the values we selected.

Drop Down List with Excel Table

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.

Clicking on Visual Basic to open Microsoft Visual Basic Editor

  • Now, the Microsoft Visual Basic Editor will open.
  • Lastly, to add a module, click on Insert >> select Module.

Inserting Module

  • Insert the following code into your module.

Code to Make Drop Down List in Excel

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 Sub

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

Clicking on Macros

  • Select Drop_Down_List macro and click on Run.

Running Macro

  • Thus, you can create a drop down list in Excel using VBA.

Creating Drop Down List with Excel VBA


Similar Readings


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.

Worksheet with Selection of Items

We want to create a drop down list using these values in Cell C4 of Another Worksheet named worksheet.

worksheet to create drop down list

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.

Drop down list in another worksheet


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.

Defining Named Range to Create Dynamic Drop Down List in Excel

  • 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)

Creating New Name

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.

Inserting Named Range as Source

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

Dataset to make Dynamic Drop Down List with Unique Values

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

Using SORT & UNIQUE functions to Make Dynamic Drop Down List in Excel

In the formula, we used the UNIQUE function to find the unique values in cell range B5:B13 and the SORT function will sort them 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.

Inserting Dynamic cell range as Source

  • Thus, a dynamic drop down list will be created using these functions.

Dynamic Drop Down List


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.

Inserting Code into worksheet to Allow Multiple Selection in Drop Down List

  • Insert the following code into that sheet.

Code to Allow Multiple Selection in Drop Down List in Excel

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
The above code is for a Worksheet_Change event that triggers whenever a cell on the worksheet is changed. The code checks if the changed cell is Cell D5 and has data validation enabled, and then concatenates any new value entered in the cell with the existing value separated by a comma and a space.
  • 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.

Creating Error alert for values other than the drop down list

  • Now, if you enter a value that is not included in the list it will show an alert message like below.

Error MsgBox


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.

Inserting Input Message

  • Thus, you can add an input message with a drop-down list in Excel.

Input Message Example


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.

Adding items

  • Select the Shift cells down option and press OK.

Opening Insert box

  • Now, if you add a value to that blank cell that item will be added to the list automatically.

Adding item in drop down list

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

Nehad Ulfat
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo