Hi there. Want to learn how to create a drop-down list in Excel? Let me help.
Hey, I'm Sharyn from Excel at Work where we offer straightforward non-technical assistance
to help you save time, work smarter and improve your Microsoft Office skills. So, let's look at
how you can quickly create a drop down list in Excel. I'll show you two different ways and
explain the advantages of each. If you're using an older version of Excel be sure to watch right
to the end and I'll show you a wee step you will need to add so that you can easily create an Excel
drop-down list from another worksheet and if you love learning new things about Excel don't forget
to like and subscribe. Right, let's get going. Right, let's have a look at how we can create a drop
down list in Excel. So, what I have here is a wee spreadsheet where I have a SUMIF function in B4.
Now the SUMIF function is giving me the total of sales made by Anne and as you can see Anne's
name is in B3 up here. So, the SUMIF function is, I'll just show you, looking at B3
and looking down here and finding Anne's name and bringing back her total. Just pressing my
Escape key. But what I would like to do is have a drop-down option here where I could select
any member of my Sales Team from over here so that I can then have their Total Sales update
per member. So, this is actually really simple and I want to step you through two different options.
So, we're going to start by looking at creating a drop-down from a list that's on the same worksheet.
Now, over here you can see that I've got a unique list of every person in our Sales Team. You can
also see that I've sorted this already and that's quite a good idea when you have a drop-down list.
Most people look alphabetically from the top to the bottom when they're looking for text.
So, there's just an idea you don't have to do it. So, to create your drop-down make sure you're on
the cell that you're wanting to have the drop-down information on and your list already exists.
Then what we'll do is we'll make our way over to the Data tab and we're looking for this option here.
So, it's called Data Validation. On the drop-down, you can see it here. We're going to click onto
that once and down here under the Settings tab, we are now going to select from Allow,
List. Now, I'll just move this out of the way. In the Source box, you'd click down into the Source box
and this is where you highlight your list and of course we can see it in the background here. So, I'm
just going up to the top and selecting right to the bottom and of course we can now see the range
for our list inside the Source box. All we need to do now is click onto OK and you've now created
your list and if we come over here and have a look you'll see there's our list. If I just scoot
up a little bit, there's Able and you can see all the way down to the bottom, Xavier at the bottom here
and because that range is dynamic, if I come over here and I change the spelling of somebody's name,
you can see we've changed Abel's name there and if I come back up here and click. Just scoot up
again. You'll see that it is dynamic and it will update for you. So, I've shown you how to create
a drop-down list based on a list that belongs in the same worksheet but a lot of the times I see
people putting their lists onto another worksheet and then most times they hide their worksheet.
So, let's have a look at what that looks like. So, I'm just going to undo and remove the drop-down. So,
let's just make sure that it's gone. Yes, it's gone. Now, I'm going to move the Sales Team list over to
this worksheet over here. So, we're going to cut and move it over to the worksheet and just Paste
it. You can Paste it anywhere you like. Right. I'll make my way back to the drop-down list
and onto the cell that I want to have my drop- down on. Again, making my way to the Data tab
and across the Data Validation and then down to List and Source. Now, all you need to do is click
onto the worksheet that holds the list. Now, if at this point you're trying to actually click
onto the worksheet and Excel isn't allowing you to go to another worksheet, it could be because
you're using an older version of Excel. So, what I'd ask you to do is pop to the end
of this video, you can use the jump links if you want to, in the description underneath this video,
to get to it and look for information on how to do this in an older version. For those of
you that are in the newer version this will work for you. So, we're going down to Sales
Team and all you need to do is the same as what I showed you before. Just highlight over that area
and you'll see that we have the addition of the worksheet name in here. So, you can see
Sales Team worksheet and then the range inside it or beside it. Clicking onto OK
and look at that. So, we can actually have the information in there. No problem at all and now,
what I normally see people do at this point is they will hide the worksheet and the reason they
do this is just so that the list is out of the way from other people if they're using this worksheet.
So, what they normally do is they come down and they just go Hide and they will hide it and of
course, now it looks like that drop-down list is there and of course it is on the hidden
worksheet. Right, if you've jumped to this spot, it's possibly because you are using an older version of
Excel and you couldn't click onto the Sales Team worksheet tab. So, I want to show you a work around.
If you are using an older version of Excel and you do need to create a drop-down list
that will be held on another worksheet, this is how you're going to need to do it. So, make your way to
the other tab that holds the list and what you're going to do is, you're going to go over your list
and give it a name. So, up here in the Name box, you can just click into it and type a name. So, I'm just
going to call this SalesTeam but you'll see that I don't put a space in between the two words. Run
them together. Press your Enter key and you've now given that list a name. Now, coming back to
the drop-down list option over here. We're going to do the same thing again. We're going to Data,
we're going across to Validation and List. Just move this over here and when we come down to
Source, what we're going to do is, we're going to use that named range. So, we're going to go Equals,
SalesTeam, it doesn't need to be in proper case, click onto OK and look at this.
Perfect. So, if you are using an older version you will need to do that. Okay and that helps
Excel identify the list that you're going to use and locate it on the other worksheet. So, I really
hope this instruction has been helpful and you are now able to quickly insert Excel drop-down lists
and if you use Excel and want to stay connected, please consider liking and subscribing because
I'm sharing new videos regularly and if you know someone who would benefit from this help,
please be sure to share this video with them. Hey, thanks for watching and have a fabulous day.