The Ultimate Guide To Excel Drop Down Lists
What if the key to collaborating in Excel was a different way of making tables?
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.
When you are creating a dropdown list, Excel allows you to tailor it to your needs in a variety of ways.
Why Use A Drop Down List?
These types of lists are great for user input.
It’s a great way to ensure that only valid data is entered into a cell.
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.
Creating and maintaining a clear and simple workbook is a very valuable skill.
We will cover everything about how you can make a drop down list here.
Drop Down List – 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.
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.
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!
This is great if you don’t want people to accidentally edit or remove items in your dropdown list.
You can also 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 navigate to the Error Alert Tab.
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 – Drop Down Lists
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 A Drop Down List
For removing drop down menus, Microsoft 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.
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.
Drop Down List – Manual Data Entry
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.
Drop Down List – 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 the cell where you would like your drop down list to be, follow the same steps to create a simple drop down list.
But first, 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, Microsoft Excel provides a few ways to do this. We will cover them all here!
Making A Dynamic Drop Down List
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.
Dynamic Drop Down List – 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.
Dynamic Drop Down List – 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 is checked in the Create Names from Selection Dialog Box 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
Making a drop-down box in Excel is great for making your sheets and dashboards more appealing.
It will speed up your data entry and minimise the risk of errors effecting your work.
Always keep this technique in mind when thinking about collaborating!
