How to Create Drop Down Lists in Cells - The Complete Excel Guide

The Opinion Lighthouse
32 followers
809 Video Views·Nov 8, 2022
How to Create Drop Down Lists in Cells - The Complete Excel Guide
Welcome to Excel Campus. My name is John and in this video I'm going to explain how to create
drop-down lists in cells. So these are also called data validation lists and we can see when we click
on this cell here we get this little drop-down icon next to the cell. When we click on that we
get all these different choices to choose from and when we choose one of those that entry will be
input into the cell. So in this video I'm not only going to explain how to create the drop-down list
but I will also share how to use keyboard shortcuts to make selections from the list,
how to copy and apply the list to multiple cells, how to handle errors and invalid inputs,
and how to update the drop-downs with new data. So let's take a look at how to create these. I'm
going to go over to this inventory sheet here and we're going to create a few different drop-down
lists. The first one will be in this column here. We just have these values of yes and no so we want
to create a drop-down list for those values. So we can first just select this cell here. We're
going to go to the data tab on the ribbon and then over here there's a data validation button. This
is a split button and we're going to click the top half of the button. That'll open the data
validation window here and on the settings tab we have under the allow drop-down we're going to
choose list. So this will create our drop-down list and really the most important setting here
is the source. So in this source box we can either reference a range or we can just put values in
here. We can just type values in here that are comma separated. So if we just want to have yes
and no as two options there we can just type yes comma no hit okay and then we'll see we have the
drop-down list or the icon added right here to the cell and when we click that we'll get our
options for yes or no. We can choose one of those and then that value will be input into the cell.
Now we might also have a much longer list that we want to choose from. For example we have this
product column here. We also have a products sheet that has a table here. It's a long list
of product names and we might want to add these to our drop-down menu. So we can do that by
referencing a range. So go back over to our inventory sheet and right here in cell A2 again
we'll just select that cell. Data tab on the ribbon. Click data validation button. That'll
open the data validation window. We're going to choose list and now in this source section we can
either click right here in this box or we can click this little icon right here and that'll
allow us to select a range. We're going to go over to the products sheet. I'll select cell A2
and I'm going to use control shift down arrow that keyboard shortcut to select all of the cells here
all the used cells in that column. So that's all the way down to row 83. We'll just click this
button again and then we can see that we have our source range there. That's great. We'll click okay
and that will add that drop down there. When we click that now we can see that we have
all of those values that are in that range listed right here. Again same thing we can
select one of those to add it to the cell. Now there are actually a few different ways to input
data here. As we just saw we can use the mouse. We can also just type a value right here. If we
know the value if we know the names of one of these items maybe like this one here of course
we can just type that in and hit enter and that will be input there. We can also use the keyboard
shortcut alt down arrow. So hold the alt key press the down arrow key that'll bring up the list.
Use the down arrow or the up arrow to go through these and then hit enter when you want to select
one. Now if you want to search through this list unfortunately there's no built-in way to do that
in Excel but I do have a free add-in called list search that allows you to search through these
data validation lists. I'll put a link to in the description below this video with more info on
that. So now that we have our data validation list in this cell right here we might want to
copy it down to the other cells in this column because these columns here do not contain those
data validation lists yet. So one easy way to do that is if we just select the cell the first cell
that contains a data validation list then select the rest of the cells in the column here we have
this is our active cell we can see the data validation list there. We'll go to the data tab
again click the data validation button and Excel will warn us here that the selection contains
some cells without data validation settings. Do you want to extend the data validation to these cells?
So we can just click yes right here and that will open the data validation window just to make sure
everything looks good and then we'll just click okay and that will then extend or add the data
validation to all the cells in that selection. So we can see it right here for all those cells
and it also applies it to blank cells. So we have blank cells down here we can still see the list
here and we can choose a new value for that cell. It's also important to note that we can also copy
and paste cells that contain data validation to other places. So if we just right click copy or
hit ctrl c we can go somewhere else in our workbook or worksheet and just paste right here
ctrl v or just paste and we'll see that we have the data validation in this cell as well. So that's
another way to copy and just copy and paste to new locations to apply data validation to other cells.
If you're enjoying this video please click that big red subscribe button below the video to
subscribe to our channel and also click the notification bell icon there to get notified
when new videos are published. So let's now take a look at the errors and rules with data
validation. So I'm going to go ahead and just hit escape here to undo that and if we're in this cell
right here and let's say we type in a product that might not be in our list I'll just type baseball
and hit enter you can see that we get a warning here. So we're getting this message saying the
value doesn't match the data validation restrictions defined for this cell and it's
telling us to either cancel or retry. So we don't get any options here we're limited or prohibited
from inputting data that does not exist in that source data range and we can control these rules.
So I'm going to go ahead and hit cancel here and again we'll go open our data validation window so
we can look at some of these rules. So right here on these tabs we have this error alert tab and
this is where we can control those rules. So right now the default setting has this checkbox
checked that just says show an error alert after invalid data is entered which is exactly what
just happened when we entered baseball. We can uncheck this if we want to allow the user to just
input anything even though there is a drop down list there if you want them to be able to input
any other value that's not in the list just uncheck that and then hit okay and that will allow that.
The other thing you can do is check this and then we have some options here for what happens
when invalid data is entered. So we can the default was stop which we just saw we can also
display a warning and in this case the warning would come up you can put a title here or maybe
an error message something like please add the data to the products list we'll do something like
that and then we can hit okay and we'll see what happens now. So now when I type baseball in this
cell and hit enter we get this warning message that just says you can still continue and it's
asking you yes or no but you see the warning message here that we just typed in please add
the data to the products list and then you can choose to continue we'll say yes and that will
allow us to continue. Now you'll notice that we do have an error warning here in the bottom I'm
sorry the top left corner of this cell we get that little error box warning if we click that drop down
we can see we have a data validation error we can even click here to get more info on that error.
It's important to note that this when we entered baseball here this did not enter that into our
source data range if we go over to our products list here baseball has not been entered in this
list anywhere it's not down at the bottom or anything like that we'd have to go take that
extra step to add this value to the list. So let's jump back over to our inventory sheet
and then the other option we had there if we open our data validation menu again for error alerts
is just information and this will really bring up kind of the same window there but we don't
have that yes no option the user will just continue the entry will be input here in this cell
and it'll just give a give the user a little information letting them know they might need
to take an additional action. So those are the three different options we have there for the
invalid entries in our data validation list and it's also good to know that any existing values
in the cells will not be impacted by any changes you make here so for example if I was to change
this to stop that means we can't enter any invalid data any invalid entries and I hit okay
this value will not change this would be an invalid entry it would kind of break the rules of
data validation but Excel is not going to go change this for us. We do still get that warning
box here that we can go make changes to but Excel is not going to make any changes to any existing
values even when we change the data validation list for the cell. And finally let's look at adding
new data to our source range. So let's say we did want to add baseball as a product and go over here
to our products table and down at the very bottom here and we can just type baseball in the list
and hit enter. Now that's not going to automatically add baseball to our data validation list. If we go
back over here to one of these lists and we go all the way to the bottom we can see that we don't see
baseball here. It's also important to point out that this list is in the same order as the source
data range. So if you want it in alphabetical order you can go back over to the source data
range and sort that range but we don't have baseball here so we need to update our data
validation list. So I'm going to select this cell go to the data validation and then we're going to
go to the settings tab here and in the source box we can either just type our new row here so delete
83 replace that with 84 or you can select this button and go reselect the range. Now the important
step here is this check box right here that says apply these changes to all other cells with the
same settings. We want to make sure we click this and when we do that we can see that the other
cells that contain the same data validation list are also selected and so all of these changes that
we make or any changes we make will be applied to those cells as well. So we'll go ahead and click
okay and if we hit this drop down here we can now see that we have our new item right here at the
bottom of the list and even if we go look at some of these other lists here they also have the new
item right there at the bottom. Now there are ways to automate this process so we don't have to update
that source range every time our product list changes or whatever your source list is here
every time this changes we don't necessarily have to go make changes to our data validation settings
and I'm going to create a whole other video on that explaining how to use tables and named ranges
you can also use dynamic named ranges for that and I'll create another video that explains that in
more detail. And then finally if you want to clear the data validation list the drop down menus from
the cells you can do that by again selecting a cell that contains data validation open the data
validation menu. We have a few options for this as you can see there's a clear all button right here
that will clear all of the settings including the error alerts any input messages you create
that will clear all of the data validation list settings for this cell or if we check this box
here for all of the cells you could also change this back from a list to just any value just know
that when you do that the error alerts and input messages will still be applied so instead of doing
that we might just want to again check this box and hit the clear all button and then we'll hit
okay and that will clear our drop down lists from all of these cells. So I hope that helps get you
started with data validation lists of course if you have any questions please leave a comment below.
If you enjoyed that video there are a few simple things you can do to help me out.
If you are watching this video on YouTube click the like button below the video and leave a
comment with any questions or feedback and please don't forget to subscribe to my free email newsletter
to get more tips and tricks that will help you learn Excel. Thanks again for watching and I'll see you soon.
