Excel Drop Down List: Create, Edit, Remove, Filter, Protect

The dataset showcases online shopping information: ‘Order ID’, ‘Customer Name’, ‘Device’, ‘Order Date’, ‘Delivery date’, and ‘Payment Method’.

Created a drop down list with the values in ‘Order ID’ to extract related information.

⏷What Is a Drop Down List in Excel?
⏷How to Create a Drop Down List in Excel?
⏵1. Drop Down List from a Range of Cells
⏵2. Drop Down List from Table
⏵3. Drop Down List with Named Range
⏵4. Drop Down List with Color
⏵5. Drop Down List with Unique Values
⏵6. Drop Down List Based on Data from Another Sheet
⏵7. Drop Down List Based on Data from Another Workbook
⏵8. Creating a Dependent Drop Down List
⏵9. Making a Searchable Drop Down List
⏵10. Creating a Dynamic Drop Down List
⏵11. Creating a Searchable, Dynamic, and Multi-dependent Drop Down List
⏵12. Making a Drop Down List with Custom Message
⏵13. Creating an Editable Drop Down List with Error Alert Message
⏷How to Add or Remove Items from Excel Drop Down List?
⏷How to Protect Drop Down List in Excel?
⏷How to Delete Drop Down List in Excel?
⏷How to Filter Drop Down List & Extract Data Based on Selection in Excel?
⏷How to Solve Issues with Excel Drop Down List?

What Is a Drop Down List in Excel?

A drop down list is a feature that allows users to select an element from a list of options.

How to Create a Drop Down List in Excel?

Use a range of cells, a table, or a named range to create a drop down list in Excel. It can be created from data in another worksheet or workbook and can be dependent, independent, searchable or editable.

1. Creating a Drop Down List from a Cell Range

To create a drop down list based on the values in column B:

Select a cell (C18).
Go to the Data tab and select Data Validation.

In Data Validation, go to Setting.
In List, choose Allow.
Define a range ($B$6:$B$15) in Source.
Click OK.

This is the output.

Enter the following formula in C19 to have the value from column C, based on the Order ID selection.

=VLOOKUP($C$18,$B$6:$G$15,ROW(B19)-ROW($B$18)+1,FALSE)

Use the Fill Handle to extract values.

2. Creating a Drop Down List from a Table

Create a drop down list using a table:

Select a cell range and go to the Insert tab to create a table.
Click Table.

Check My table has headers.
Click OK.

To create a drop down list in C18 with data in Customer Name, select List in Allow.
Enter the following formula with the INDIRECT function in the Source and click OK.

=INDIRECT("Table1[Customer Name]")

Enter the following formula in C19 to extract the device name based on the customer name selection.

=INDEX(Table1,MATCH(C18,Table1[Customer Name],0),3)

This is the output.

3. Creating a Drop Down List with a Named Range

Select a cell range and go to the Formulas tab.
Click Define Name.

Set a name ( ID) in Name and define a range in Refers to.

Create a drop down list of order IDs in C19 by entering the following formula in Source

=ID

Use the following formula in C19 to have the customer name based on the order ID selection.

=INDEX(B6:G15,MATCH($C$18,ID,0),2)

Use the following formula to have the name of the device in C20.

=INDEX(B6:G15,MATCH($C$18,ID,0),3)

This is the output.

4. Creating a Drop Down List with Color

Create a drop down list with color. Define a custom color with Conditional Formatting for each value of the drop down list.

Select List in Allow .
Enter the color names separated by a comma sign (,): ( Black,White,Silver,Gold) in Source.

To define a color for each of the elements of the drop down, select the drop down and go to Conditional Formatting in the Home tab.
Go to Highlight Cell Rules and click Equal To…

In Equal To, enter the name of the color (Black).
Select Custom Format to set a color for the color name.

Choose a color in Fill.

You can also change the font color in Font. Here, white.

Click OK.

Set colors for the other options.

The drop down displays color based on the cell value.

Color will automatically change based on the selection.

Use the Fill Handle to AutoFill the drop down.

5. Creating a Drop Down List with Unique Values

Create a drop down with unique values ignoring the duplicates.

Create a drop down with the unique values of column D and another drop down with unique values of column E.

Use the following formula in B22 to see the unique brand names.

=UNIQUE(D9:D18)

Enter the following formula to create a drop down in C5 with the unique values.

=$B$22:$B$25

Enter the following formula in C22 to see the unique values from column E that match the brand in C5.

=FILTER(E9:E18,C5=D9:D18)

Go to Data Validation.
Use the following formula in Source.

=$C$22$

This is the output.

6. Creating a Drop Down List Based on Data from Another Sheet

Create a drop down list with the data from another worksheet. Here, mobile brands and their phone models are in the PhoneList worksheet.

Use the brand names and phone models to create two drop down lists in another worksheet (AnotherSheet).

To create a drop down list of brand names in D6 with the data in the PhoneList worksheet, enter the following formula in Source.

=PhoneList!$B$3:$E$3

Use the Fill Handle to create the same drop down list in column D.
Use the following formula with the INDIRECT function in Source to have the phone models from the PhoneList worksheet in a drop down based on the brand names in column D.

=INDIRECT($D6)

Having Values in Drop Down from Another Sheet

Click here to enlarge the image.

This is the output.

7. Creating a Drop Down List Based on Data from Another Workbook

You can create a drop down list based on data of another workbook.

In the AvailablePaymentMethods workbook, there is a list of payment methods for online shopping. Use the data to create a drop down list in the Drop down in Excel workbook.

Go to Formulas and select Define Name.

In New Name, define a name (PayType) in Name.
Define a range (Sheet1!$B$3:$B$7) in Refers to.
Click OK.

Go to the Drop down in Excel workbook and set a named range (PayType).
Enter [AvailablePaymentMethods.xlsx]Sheet1!PayType to define the name range in the AvailablePaymentMethods workbook.
Enter the following formula in Source to create a drop down list with the named range in G6.

=PayType

You can select a payment method from the drop down.

Use the Fill Handle to AutoFill the drop down.

Note

i. Both workbooks must be open while creating the drop down.
ii. Don’t create multiple dependent drop down lists from another workbook.

8. Creating a Dependent Drop Down List

Create a dependent drop list in Excel. Values will change based on the selection of another independent drop down list.

To create an independent drop down list of brands in D6, enter the following formula in Source (Data Validation).

=$B$18:$E$18

To create the dependent drop down list of phone models in E6, enter the following formula with the INDIRECT function in Source.

=INDIRECT($D6)

Click here to enlarge the image.

The dependent drop down based on the selection of brand from an independent drop down list is created.

Use the Fill Handle to AutoFill the drop down lists.

9. Creating a Searchable Drop Down List

Create a searchable drop down list in C19 with the brand names. There will be a dependable and searchable drop down with phone models in C20 based on the selected brand.

Insert new columns (G and H) to have unique brand and model names. Use the following formula in G6.

=UNIQUE(OFFSET(D6,0,0,COUNTA(D:D)-1,1))

Enter the following formula in Source to create a searchable drop down in C19 with the brand names.

=$G$6#

You will be able to search brand names in C19.

Enter the following formula in H6 to see phone models based on the brand selection of the searchable drop down.

=UNIQUE(FILTER(E6:E17,C19=D6:D17))

Enter the following formula in Source to create a searchable drop down in C20 with the phone model names.

=$H$6#

You have independent and dependent searchable drop down lists.

10. Creating a Dynamic Drop Down List to Add & Auto Update New Entries

Create a dynamic drop down list. New entries will auto update the drop down list.

Create a dynamic drop down based on the values in column B to extract values based on that ID.

Click here to enlarge the image.

Insert a separate column and enter the following formula in L6 to see unique IDs based on column B values.

=UNIQUE(OFFSET(B6,0,0,COUNTA(B:B)-3,1))

Click here to enlarge the image.

Create a drop down list with the unique IDs in J5 by entering the following formula in Source.

=$L$6#

Click here to enlarge the image.

Select an ID from the drop down and use the following formula in J6 to see the customer name for the selected ID.

=VLOOKUP($J$5,B:G,ROW(I6)-ROW($I$5)+1,FALSE)

Use the Fill Handle to AutoFill the rest of the values.

Extracting Value Based on Drop Down Value

Click here to enlarge the image.

The extracted dates will be shown as integer numbers. Change the number format to Short Date or Long Date in Number Format.

Add extra IDs and their related values. They will automatically be updated in the drop down and in column L.

Click here to enlarge the image.

This is the output.

11. Creating a Searchable, Dynamic, and Multi-dependent Drop Down List

Create an independent searchable drop down list with brand names, a brand name dependent searchable drop down list of phone models, a phone model dependent searchable drop down list of chipsets and a cell with the price of the product be dependent on all three drop down lists.