How to create a drop down list in Excel?
I will help you build dropdown lists all the way from the ground up.
You can open your own Excel workbook, and follow along step-by-step if you like.
Here’s our starting point.
We have some data in columns I, J and K that we are going to use to build a basic dropdown
list, and a more advanced dropdown list dependent on another cell, and on top of that we will
build a yes or no drop down list.
The teams that we are looking at are going through a contest.
We want to create an Excel spreadsheet that makes it easier to keep track of the progress.
We start with a row at the top listing the round in column A, the team in column B, the
team member in column C, the completion status in column D, and points gained in column E.
In the next row, we are going to work with various types of dropdown lists.
The great thing about drop-down lists in Excel is that they make data entry easier for people,
and you can control the data other people enter by restricting the options they can
choose from.
Let’s make a basic dropdown list first in cell B2.
We go to the data tab, press data validation right here, and then data validation.
Pick from a list of rules to limit the type of data that can be entered in a cell.
Yep, that’s what we want!
In “Settings”, we go to “Allow” and select “List”.
As you can see from the options here, there are many types of data validation that you can do.
Building dropdown boxes is just one of them!
We now click in the “Source” field, and then select cells I1 through K1.
While we are in this Data Validation dialog box, we might as well add some user-friendliness
by going to “Input message”, typing “Select Team” in the Title field, and “Please
select the participating team” in the “Input message” field.
We will see shortly how this comes across to the user in the workbook.
Then in “Error Alert”, we have several options: we can stop the user from continuing
if an input error occurs, we can provide a warning, or simply just show some information.
Let’s go to the toughest option of stopping the user from entering anything but the approved
choices on the list.
In the Title field, we type “Invalid team selected”, and in the Error Message field
“Only select from the list of participating teams”.
Once we have verified what we typed, click OK.
Let’s take a look what our dropdown list looks like.
Once we activate cell B2, we notice the dropdown list input message that we typed earlier:
please select the participating team.
When we click on the list, we can select team 1, 2 or 3.
That all works fine.
Let’s now see if our error alert works.
Some users might be typing in data, rather than selecting an option from the dropdown list.
Let’s type in “Team4” and hit enter.
This gives the user the STOP error message: Invalid team selected - Only select from the
list of participating teams.
There is no way for the user to ignore this message, the choices are to either retry (and
type a valid option), or to cancel which brings the user back to the previous selection.
In cell C2, we are going to build a dependent dropdown list.
Let’s say that we selected team 1 in cell B2.
In cell C2, we want to display only the names of the team 1 members.
In other words, our dropdown list selection in C2 needs to be dependent on what was selected
in another cell: more specifically the drop down list in B2.
In order to achieve this, we need to tell Excel where the data for each team resides.
Let’s select the names of the team 1 members, then go to the Formulas tab, and click “Define
Name” right here in the middle.
We will assign the name “Team1” to the members of that team.
We now select the names of the team 2 members, go to Formulas – Define Name again, and
assign the name “Team2” to the members of that team.
Same action for team 3: select the names, go to Formulas – Define Name, assign “Team3”
to the members of that team.
Now over here on the left, we can verify that we have correctly assigned the names: team 1, team 2, team 3.
Now we go back to cell C2, go to the Data tab, Data Validation, and start making our
dropdown list.
In the source field, we type a formula: =INDIRECT($B$2)
Then on “Input Message”, “Select Team Member” in the Title field,
and “Please select specific team member” in the “Input message” field.
In “Error Alert”, we once again go for the STOP message: in the title “Invalid
team member selected”, and in the Error Message field “Only select from the list
of team members”.
Once we have verified what we typed, click OK.
Here we go!
We have created a dependent dropdown list in Excel.
If team 1 is selected in cell B2, then the team 1 members are available for selection
in cell C2.
If team 2 is selected in cell B2, then the team 2 members are available for selection
in cell C2.
If team 3 is selected in cell B2, then the team 3 members are available for selection
in cell C2.
What if a new team member joined team 1.
I can type the name at the bottom of the list of team 1 members in column I, but that does
not make it immediately available in the dropdown list.
Remember that we assigned names earlier.
We can update the range where the names are stored.
Go to the Formulas tab, Name Manager, select the team you want to edit, and simply extend
the range.
As you see, all four names are now showing in the dropdown list.
If you want the choices in the dropdown list to be stated in alphabetical order, then select
the four cells I2 through I5, right-mouse click, sort, sort from A to Z, continue with
the current selection, sort.
The choices in the dropdown list have now been updated accordingly.
We could also give the users the option to leave cell C2 blank by adding an empty cell.
Formulas tab, Name Manager, extend the range by one cell which happens to be blank.
Now there is a blank option at the bottom of the dropdown list.
So far, we have been linking the dropdown list to cell ranges.
Let’s add another dropdown list where we do the opposite: we are going to input the
items to choose from straight into the data validation area.
For the “completion” column, we want a drop down list with only the options Yes and No.
In cell D2, we go to Data – Data Validation, select “List”, and simply type in the
source field Yes and No, separated by a semicolon.
Some tutorials will tell you to type Yes and No, separated by a comma and space, but that
does not work on my machine.
As “Input Message”, we type “Task completed?”, and “Was the team successful in this round?”.
The user can now simply select either Yes or No.
In cell E2, we can assign points to that Yes or No outcome of cell D2.
If cell D2 is Yes, then show 5, if not show 0.
Now if we change the selection in cell D2, the number of points in E2 changes.
We can now create new rows, and copy over the dropdown boxes.
Let’s select a different team here.
Cell B3 and D3 work without a problem, but for C3 (the dependent dropdown list), we need
to make sure we update the INDIRECT formula that is part of the data validation.
It currently refers to cell B2, we need that to be B3.
And at the bottom of the points column, we can choose to sum the points or make any other
overview of the scores.
Currently, we have the data about the teams in plain view in the same sheet of the workbook,
here in columns I through K.
If we don’t want users to see that data, we could select the data, cut it using control
X, paste it into a new sheet with control V, and hide that sheet by right-clicking on
it, and selecting “Hide”.
This doesn’t change the dropdown list functionality in Excel (I can still select teams and team
members as before), but makes it less transparent to the user where the source data is placed.
Dropdown lists: a very useful piece of functionality in Excel!