All language subtitles for 1. Creating Dynamic Drop-down Lists

In this section of the course, we're going to talk about something that I use all the time, it's one

of my favorite things in Excel and that is data validation and what data validation allows you to do

is basically control what goes into your worksheet.

One of our main goals when we're using Excel is that we want to make sure that the data that goes into

the worksheet is accurate and as error free as possible.

And in this day and age where we have lots of people sharing worksheets that can become quite problematic

if we don't put in some controls and data validation is a great way of controlling what's going into

cells.

So let's take a look at our first example and probably the most common use of data validation.

And that is the data validation dropdown list.

Now we have actually seen this a couple of times throughout this course.

I used it when I was showing you index a match and we're going to do another one of those examples now

so you can see exactly how to create a data validation dropdown list.

Now, currently I have a small table of data.

I have some, let's say, employee names.

I have their department and I have this salary.

And what I want to do is I want to be able to select their name from a dropdown list and have it returned,

the department and the salary.

So we need some index and match formulas in here.

But the first thing we need to do is create a data validation dropdown list.

Now why do we need to do that?

Why can I not just type in the name?

So, for example, Sarah Jones?

Well, the answer is I can just type in the name if I want to.

But if I want to flip between lots of different employees typing in their name each time is going to

start to become a little bit tedious.

It also means that if somebody accidentally typed in the wrong word or they misspell, Let's say they

spell Sarah like that instead, it means that these index match formulas aren't going to work because

they're going to be looking for a different value.

So we can control what goes into this cell by creating a dropdown list.

And this is a super easy thing to do.

All we need to do is click in the cell where we want to add our dropdown list, go up to the data tab

and into data validation.

Let's click the button to open up the dialog box.

Now I'm going to put him over there.

Now, notice we have three tabs, settings, input message and error alerts.

So let's deal with settings, first of all.

Now there are a few different types of data validation that you can do.

And as I said, the one that I use most frequently is this option here, creating validation lists.

So let's click on list.

We can now choose the source for our list.

So I want all of the names to appear in this list, so all I need to do is select the cell range that

contains those names.

Click on OK.

And just like that, I now have this little in-cell dropdown, which is going to allow me to select

the different employees from a nice, concise list.

Now I'm quickly going to add in the index and match formulas, so join me back here in a couple of seconds.

So there we go.

I can see that Michael Parks is in the marketing department and there is his salary.

If I switch to somebody else in the list, that is going to update.

So that is how you can control what goes into the cell using a data validation list.

Now this is all well and good, but let's take a look at a couple of different scenarios and issues

that can come up with this data validation list.

Now, the first issue is if you have duplicates of the name in this example, in the list.

So what about if I have missing Sierra a few times?

Let's add more

now if I go back to my dropdown list.

Notice the dropdown list hasn't updated, even though I've added more people onto the end.

So what I would need to do here is go back into data validation and update my range.

So if I delete this out, my source is now going to be this range just here.

But if I click the drop down notice that I have Mencia listed now four times in this list, so data

validation doesn't pick out specifically unique values.

It's just going to list out everything that you've selected, whether it's a duplicate or not.

And a lot of the time, even if you do have duplicate values, you just want them listed once in your

dropdown list.

So how can we get around that?

Well, the way that I do this is I use the unique function to create a unique list, first of all.

So what I would do here is if I did have duplicates, I would choose a column maybe over here and I

would use the unique function.

I'm going to select my range.

Close the bracket and Hansa, and that's going to give me just the unique values from this list, and

I can now use this list as my data validation.

So if you go back into data validation and just clear out what's in there, I can simply select these

values.

Click on OK, and now I get that unique list in my dropdown.

And of course, we don't want people to see these values.

You can simply hide that column.

You could even put it on an entirely different worksheet if you wanted to.

So that is one way you can deal with duplicate values.

Let's take a look at another scenario that can throw off some problems.

So I'm going to analyze this column, and I'm just going to delete everything out of here.

Now, notice as soon as I do that, all the values disappear from this dropdown list.

Now, another issue you'll come across with these dropdown lists is that they don't automatically update

when you add new values on to the bottom.

So if this list grows and I start to add more people in, my data validation isn't going to automatically

update and accommodate the new values.

So how can we get around this?

Well, one way that we could do this is to put this data into a table control T.

Yes, my table has headers.

Click on OK.

And let's just change the formatting.

So that is a little bit ugly.

There we go.

So now if I type a new name on the bottom, the table is going to expand.

So let's just type in some values in here.

So how does this work with our data validation?

Well, let's go up to data into data validation.

When our source this time is going to be this column just here and click on.

OK.

So now I get all of those names.

Now does this update when I add a new name onto the bottom?

Let's see.

Let's click our dropdown and see if Maggie is on the end.

Yes, she is.

So using a table is a great way to get this list to dynamically update.

Let's add another one just to check to make sure let's click the dropdown.

And there we go.