The clear all option in the clear button drop-down list removes everything from a cell, including

We have all used tables in Excel, but what if you are looking to make this table into a list for others to use?

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

These types of lists are great for user input.

This is also 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.

Why Use A Drop Down List?

Drop down lists help you to organise your data and limit the amount of entries people can make to each cell.

Whenever you create an Excel spreadsheet for a user to input data, a drop down list will be very helpful!

They help you to streamline the experience you are creating for your users, and design a smooth looking document for them to use.

We will cover everything about how you can make a drop down list here.

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.

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.

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.

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.

4. Click Ok.

5. The drop down list is shown below.

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

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

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.

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

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

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.

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

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

4. Enter a new product name.

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

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.

2. Right-click the cell and select Delete.

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

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

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

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.

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

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

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

Once you have done this you will see the following.

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.

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…

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

Once you have done this you will see the following.

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.

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.

3. Click Ok.

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

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

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.

4. Click Ok.

Dynamic drop down lists

A dynamic drop down list refers to a list that updates automatically as an item is added in the original data set.

Dynamic drop down lists are great for when you want to add new items and have it update automaticaly!

If you need to create a dynamic list excel provides a few ways to do this. We will cover them all here!

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.

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]

2. Click Ok.

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

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

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

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.

3. Choose 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.

You should see the following.

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.

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.

You should see the following.

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

You should see the following.

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.

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]]

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

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

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.

Dependent drop-down lists

A dependent drop down list is a new drop down list, which is based on the value taken from another drop down list.

This may seem complicated but it is simple in practice – take a look below!

Create a simple dependent drop-down list

In this 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.

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

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

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.

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

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.

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

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]

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.

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.