Hi there, need to edit 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.
Right, I'm going to show you how to find the list items update and remove items and how
to add items to the drop-down list. If you're using an older version of Excel be sure to watch
right to the end and I'll show you a wee extra step you will need to add
so that you can easily locate and update an Excel drop-down list from another sheet
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 to edit a drop-down list in Excel. So, the first
thing you'll need to do is click onto the cell that holds the drop-down list and in this case you
can see it's on cell B3. Now, one of the trickiest things about editing an Excel list is finding
where the list belongs. So, this is how you can do it. On the Data tab, come across to Data Validation.
Now, once you're in there. If you're on a cell that has a drop-down list on it already.
You will see the source information or the source location for the list and in this case it's saying
that this list, the drop-down list, is coming from the Sales Team tab, A2 through to A13. I'm
just going to come out of here. Now, sometimes you'll see that information but you won't see
the tab down here and that's normally because somebody's hidden the tab. So, in this instance
it would look like this. So, even though you can see it. I'll just come across to here.
Here you may not be able to see the tab down here. Just come out of here. So, what i'd ask you to do is,
right-click any tab down the bottom here, go to unhide and you'll see the worksheet name
in here. Click on to OK. Now, if the worksheet has been protected you may not be able to get to it
but if it hasn't been protected you'll be in there straight away. Now, that's a nice and easy way to
find a list. However, sometimes you may come across something like this. So, i'm just coming across to
this worksheet and Data Validation and you see something like this, that doesn't look like a range
does it? It's actually a named range. So, I'm going to come out of here and show you how to work with
this. This is a named range called Sales Team and what i'd need you to do here is i want you to come
up to this little search box or Name box up the top, next to the formula bar. When you click onto
the drop-down arrow at the end of the name box you will see that named range when you click onto it,
it will take you to the list. Now, if at this point you haven't actually gone anywhere it could be
the worksheet that this named range lives in is actually hidden and if that's the case what you
could do is you go to the Formulas tab, come across to Name Manager, you will see the named range in
here, it will actually tell you the worksheet that it lives in. If that's the case, then hopefully you can
do the same as what I've just shown you by doing a right-click, unhide, unhide the worksheet and get
to the list. Okay, now once you're in here, of course, you can easily go and update information. If I
needed to edit Abel's name, I can just over type it and it'll instantly be updated in the list. So, if I
go back to the drop down list over here, you will see that it's now updated but what if I want to
remove or add items to the list. Now, removing's easy. I'm just going to make my way back to the
Sales Team list down here. Obviously, all you need to do is delete the entire row and I would ask you
to delete the entire row because otherwise you're going to have a lot of blank spaces in your drop-
down menu. So, that's easy enough but what if I wanted to add maybe somebody at the bottom of
my list. So, I'm just going to come down here and pop another name at the bottom of my list here.
Now, I'll make my way back to the drop-down list over here and you will see that I don't
have that added to my list and that's because I haven't extended out the range for my list
in the Data Validation box. So, what we're going to do is, we're going to make our way back to Data,
over to the Data Validation box and you can see that it's stopping on A13. So, if I click into the
box and I'm going to extend that out now to 14. So, it's obviously including 14 in there. Click onto OK
and now you will see that you have your extra item or name in the list but it's a little different
for named ranges. So, if I come down to here and you're working with a named range. If I come up
to here, Data, Data Validation, of course that's our Sales Team there and when I click onto OK,
you'll see that we don't have Zander's name at the bottom here. So, it's a little different for
a named range drop-down list. To add items to this list you need to go to the Formulas tab,
Name Manager, find the named list and edit it and in here you can extend it out. Click onto OK
and close out of here and now you'll see that you have that added to your list. So, I really hope this
instruction has been helpful and you are now able to quickly edit, remove and add to drop-down lists
in Excel 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.