The Definitive Guide to Creating a Drop Down List in Excel

You can easily allow users to choose an item from a pre-defined list, by using a dropdown list in Excel.

This is a great way to ensure that only valid data is entered into a cell.

When you are creating a dropdown list Excel allows you to tailor it to your needs in a variety of ways.

An Excel drop down list is comparable to the dropdown menus that one often sees on forms or webpages.

How to create a drop down list in Excel, is covered in our Advanced Excel training course.

 

 

 

How to create a simple drop down list

 

Create a drop down list using a cell range

In the example below, we have a worksheet called Products containing the names of products. This is the original sheet that contains the source data.

Screenshot showing the Products sheet which has the source data

 

1. Now select or create another sheet. In this case, this is the destination sheet where you would like the drop down list to be. This sheet is called ProductsDropDownList in our example.

 

Screenshot showing the destination sheet which will have the drop down list

 

2. Select cell E5 on the destination sheet. This is the cell that is going to have the drop down list. Go to the Data Tab on the ribbon and on the Data Tools group, choose Data Validation.

 

Screenshot showing the Data Validation option in the Data Tools group on the Data tab

 

3. The Data Validation Dialog Box will appear. Under the Settings tab, in the Allow drop down menu click List. Ensure Ignore blank and In-cell dropdown are checked.

Using the Source box, select the cell range on the Products sheet, which in this case is range A2:A9.

Screenshot showing the Data Validation Dialog Box and explaining how to create a drop down list and which Settings to use

 

4. Click Ok.

5. The drop down list is shown below.

 

Screenshot showing the drop down list

 

• Note: If the user tries to type a value in the cell that is not in the drop down list, they will get an error message.

 

Screenshot showing the error message that appears when the user tries to enter data that is not in the list

 

• Note: The drop down box arrow in Excel is only shown when the cell containing the drop down list is selected.

 

• Tip: By default, the items in the actual list are quite small. If you’d like the entries in the list to look bigger then in the worksheet containing the drop down list, click the Select All button to select all the cells.

With all the cells selected decrease the font size by one point. Then increase the zoom level magnification.

In the example below 140% zoom level magnification was used and the items in the dropdown list now look bigger.

Screenshot showing the items in the drop down list at an increased Zoom level in order to make them appear bigger

 

If you’d like to know more about increasing or decreasing font size in Excel then read our guide on Excel font formatting.

• Tip: By default, the items in the list will appear in the same order that they are entered in the original worksheet. If you’d like to have the items in your list sorted in either ascending or descending order, go to your source data.

Then with one cell in the range selected, go to the Data Tab, in the Sort & Filter group choose either ascending sort or descending sort.

 

Screenshot showing how to sort data in the source data sheet so that the items in the drop down list are sorted

 

In the example below, ascending sort was selected. The items in the drop down list are now sorted in alphabetical order.

 

Screenshot showing the items in the drop down list sorted alphabetically

 

• Tip: You can hide, or password protect the worksheet that contains the source data if you don’t want people to accidentally edit or remove items in your dropdown list.

• Tip: You can allow the user to enter another item that is not in the list, when necessary. To do this select the cell containing the drop down list.

Go to the Data Tab on the ribbon and on the Data Tools group, choose Data Validation and in the Error Alert Tab uncheck the check box that says Show error alert after invalid data is entered. Click Ok.

Screenshot showing the check box that says Show error alert after invalid data is entered cleared

 

Add an item to a drop down list

You may have a situation where you want to add a product that is not in the original list.

1. To do this, go to the original sheet containing the names of the products. Select one of the products.

2. Right-click the cell and select Insert.

 

Screenshot showing the Insert...option

 

3. Select the Shift cells down option. Click Ok.

 

Screenshot showing the Insert Dialog box with Shift cells down checked

 

Once you have done this you will see the the following.

 

Screenshot showing what the cell range looks like with an inserted row

 

4. Enter a new product name.

 

Screenshot showing the new item, Product XXEFZ that has been added to the original source data

 

5. Now when you return to your sheet with your drop down menu, you should see the list has been updated.

 

Screenshot showing the drop down box that has been updated with the item added in the source data range

Delete an item from a drop down list

You may have a situation where you want to delete a product from the drop down menu.

1. To do this, go to the original sheet containing the names of the products. Select the item that you want to be deleted from your drop down menu.

 

Screenshot showing the product that needs to be deleted

 

2. Right-click the cell and select Delete.

 

Screenshot showing the Delete... option

 

3. Select the Shift cells up option. Click Ok.

 

The Delete Dialog Box with the Shift cells up option checked

 

Once you have done this you will see the the following.

 

Screenshot showing that Product WKKD has been deleted in the source range

 

4. This item should no longer be in the drop down menu.

 

Screenshot showing the drop down list that no longer has the item that was deleted in the original data source

 

 

Remove the drop down list

To remove a drop down menu Excel lets you do this in two main ways.

• Select the cell with the drop down list. Go to the Data Tab on the ribbon and from the Data Tools group, choose Data Validation.Under the Settings tab, click Clear All and then OK. The drop down list will be removed from the cell.

 

Screenshot showing the Data Validation Dialog Box and the Clear All button highlighted

 

 

Once you have done this you will see the the following.

 

Screenshot showing that the drop down list has been removed from cell E5

 

• You can also remove the drop down list by selecting the cell with the drop down list and then going to the Editing group in the Home Tab. Click the arrow next to the Clear button and select Clear All.

 

Screenshot showing the Clear option in the Editing group on the Home Tab

 

 

Once you have done this you will see the the following.

 

Screenshot showing the Clear All option

 

Once you have done this you will see the following.

 

Screenshot showing that the drop down list has been removed from cell E5

 

 

Remove all the drop down lists in a worksheet

If you would like to remove all the drop down lists in your worksheet then you can do the following.

 

Screenshot showing a worksheet with two cells that contain drop down lists

 

1. With any cell in the worksheet selected, go to the Home tab and in the Editing group click on Find and Select. Choose Go to Special…

 

Screenshot showing the Go to Special option... and how to access it from the Home Tab

 

2. Select Data Validation and choose All. Click Ok.

 

Screenshot showing the Go To Special Dialog Box with the Data Validation option selected and All chosen

 

Once you have done this you will see the following.

 

Screenshot showing both cell C4 and cell C6 which contains the drop down lists selected

 

3. On the Home Tab, in the Editing Group Click the arrow next to the Clear button and select Clear All.

4. Now all the drop down lists will be removed.

• Note: This will remove all the data validation (not only drop down lists) from your worksheets.

 

Create a simple drop down list by entering the data manually

You can manually enter the source data for the drop down list by using the Source box.

 

1. Select the cell which you would like to contain the drop down list.

 

Screenshot showing the worksheet for the manual data entry with cell C4 selected

 

2. Go to the Data Tab and choose Data Validation from the Data Tools group. Select List and this time in the Source box manually type the entries as shown below. Make sure that each entry is separated by a comma.

 

Screenshot showing the values being typed into the Source box in the Settings tab of the Data Validation Dialog Box

 

3. Click Ok.

 

Screenshot showing the drop down list with the entries (yes and no) that were manually typed in

 

• Note: You can manually enter as many entries as you’d like but it makes sense to only use the manual entry option when you have a few options in case you need to sort the list for example.

Create a simple drop down list by using a named range

You can use a named range as your source for your drop down menu.

1. Create a named range first. Select the range of cells that you would like to name.

 

Screenshot showing the source worksheet for the drop down menu with the cell range for the named range selected

 

2. Type the name Attendees into the Name Box and press Enter.

 

Screenshot showing Attendees typed into the Name Box

 

3. In a cell, on the sheet where you would like your drop down list to be, follow the same steps to create a simple drop down list.

However, in the Source box type an equals sign first and then the name of your named range.

 

Screenshot showing the named range Attendees being entered into the Source box preceded by an equals sign

 

4. Click Ok.

 

Screenshot showing the dropdown list that has its source from the named range

 

How to create a dynamic drop down list

If you need to create a dynamic list excel provides a few ways to do this. A dynamic drop down list refers to a list that updates automatically as an item is added in the original data set.

Create a dynamic drop down list using a formula

We have a worksheet containing the source data called DynamSourceData and a destination sheet called DynamicDropDownList.

 

Screenshot showing the worksheet containing the source data

 

Screenshot showing the destination worksheet where the dynamic drop down list is going to appear

 

1. Select cell B5 in the destination worksheet and go to the Data Tab and choose Data Validation from the Data Tools group. Select List and in the Source box, enter the following formula

=OFFSET(DynamSourceData!$A$1,0,0,COUNTA(DynamSourceData!$A:$A),1)

 

Screenshot showing the OFFSET formula explanation which is entered into the Source box of the Data Validation Dialog box

 

2. Click Ok.

3. Now go to the worksheet containing the source data and add the colour Blue.

 

Screenshot showing the colour blue was added to cell A5 in the source worksheet

 

4. You will see that the drop down list on the destination worksheet is updated.

 

Screenshot showing that the drop down list on the destination worksheet is now updated automatically

 

• Tip: Sometimes when building formulas, you may get errors.

To provide more insight into what is causing the error and how to fix it, copy the formula to a cell in Excel and go to the Formulas Tab and then the Formula Auditing group. Click on Error Checking.

To learn more about Formula Auditing, view our detailed guide on formula auditing in Excel here.

 

Create a dynamic drop down list by using Table Formatting

 

When creating this specific dynamic drop down list Excel requires you to format your source data as a table. We have a sheet containing the names of popular sports and we would like to create a dynamic drop down list on the destination sheet.

 

Screenshot showing the worksheet containing the source data

 

1. Select a cell in the source data range. It can be any cell in the source data range, but in this case cell A2 is selected.

2. On the Home Tab, in the Styles group, click Format as Table.

 

Screenshot showing the Format as Table option in the Styles group on the Home Tab

 

3. Choose a table style.

 

Screenshot showing how to select a table style

 

4. The Create Table Dialog Box will appear. Ensure the My table has headers box is checked. Click Ok. This automatically creates a table from your range.

 

Screenshot showing the Create Table Dialog Box with the My table has headers option checked

 

You should see the following.

 

Screenshot showing the Table

 

5. With a cell in the Table still selected, go to Table Tools and in the Table Design Tab, in the Properties group rename the table to SportsT.

 

Screenshot showing how to rename a Table in the Properties group in the Table Design tab

 

6. Now on the destination sheet in cell B4, go to the Data Tab and choose Data Validation from the Data Tools group. Select List and in the Source box enter the following formula =INDIRECT(“SportsT[Sports]”)

Since we are using a table, we can use structured references in our formula.

 

Screenshot showing the INDIRECT formula that has been entered into the Source box in the Settings tab of the Data Validation Dialog Box

 

You should see the following.

 

 

Screenshot showing the dynamic drop down list that has been created

 

7. Now as entries are added or deleted in the SportsT table, the drop down list is automatically updated to reflect these changes.

 

Screenshot showing an entry has been added to the table in cell A6

 

You should see the following.

 

Screenshot showing that the drop down list is dynamic and that it updates automatically

 

Create a dynamic drop down list by using the UNIQUE Function

Let’s say you have a situation where you have an existing table like the one shown below.

 

Screenshot showing an existing table on a worksheet and cell H1 selected where the dynamic drop down list needs to be created

 

We’d like to create a dynamic drop down list using the source data in column B. However, as you can see there are a lot of duplicates and we need unique values.

We can use the UNIQUE Function in order to do this. The UNIQUE Function is one of the new dynamic array functions available in Office 365.

• Extract the items for the unique list

1. We need to extract the unique cities from our table which is named CompanyT. So, in cell D1 enter the following formula
=UNIQUE(CompanyT[Company Headquarters])

 

 

Screenshot showing the UNIQUE Function syntax in cell D1

 

2. Once you press the Enter key, all the unique values are extracted and the results spill into the needed cells as shown below.

 

Screenshot showing how the spill over of the cells through using the UNIQUE Function

 

• Setup Data Validation

3. Now while still on the same worksheet, with cell H1 selected go to the Data Tab and in the Data Tools Group, select Data Validation.

4. Select List. In the Source box enter
=$D$1 #

This is the way you need to refer to a cell that spills over. Click Ok.

 

Screenshot showing how to refer to a cell that spills over in the Source box of the Data Validation Dialog Box

 

5. You now have a dynamic drop down list that updates as values are added to column B but only with unique values.

6. If another company entered their company headquarters as Leeds, the dynamic drop down list would not add Leeds again. However, if they entered their company headquarters as Honolulu then the dynamic drop down list would update with this value.

 

Screenshot showing that the duplicate value is not added to the dynamic dropdown menu

Screenshot showing the unique value Honolulu is added to the dynamic dropdown menu when it is added to the table

 

How to create a dependent drop-down list

Create a simple dependent drop-down list

You may have a situation where you need to create a dependent drop-down list, where based on the value chosen from one drop-down list another drop-down list is populated.

In the example, attendees attending an event need to select whether they want a savoury snack for lunch or a sweet snack. If the attendee selects the savoury option, they get three choices. However, if they select the sweet option then they get three other choices.

 

Screenshot showing the original source data arranged in a horizontal tabular layout

 

The first thing we need to do is create named ranges. So, select the range B3:E4.

 

Screenshot showing the range B3:E4 selected

 

1. Go to the Formulas Tab, and in the Defined Names group choose Create from Selection.

 

Screenshot showing the Create from Selection option in the Defined Names group on the Formulas Tab

 

2. Ensure that only the Left Column option is checked in the Create Names from Selection Dialog Box since the data is horizontally arranged and click Ok.

 

Screenshot showing the Left column option checked in the Create Names from Selection Dialog Box

 

3. Now go to Name Manager and you should see the two named ranges Savoury and Sweet.

 

Screenshot showing the named ranges Savoury and Sweet in Name Manager

 

4. Now with cell B7 selected, go to the Data Tab on the ribbon and on the Data Tools group, choose Data Validation. Under Settings select List. Using the Source box select the cell range B3:B4. Click Ok.

 

Screenshot showing how to create the first drop down list

 

5. Select one of the options either savoury or sweet.

 

Screenshot showing one of the options selected in the first drop down box

 

6. With cell C7 selected, go to the Data Tab select Data Validation from the Data tools group. Under Settings select List. In the Source box enter the formula
=INDIRECT(B7)

Screenshot showing the INDIRECT formula entered into the Source box in the Settings tab of the Data Validation Dialog Box

 

7. Click Ok. Now if the user selects savoury from the drop-down box in cell B7, they get the savoury options when they click the drop down in cell C7. When they select sweet in cell B7 they get the sweet options in cell C7.

Screenshot showing the options in the dependent list when Savoury is selected

 

Screenshot showing the options in the dependent list when Sweet is selected

 

Conclusion

It is advisable to know how to create a drop-down box in Excel to visually enhance your own sheets and dashboards, speed up data entry and reduce the chance of errors.

If you need to create a dropdown list Excel provides ways to not only create a standard drop down list but also to create other kinds of drop down lists.

This guide has given a comprehensive overview of some of the options you have for creating a dropdown list in Excel.