One of the most useful types of validation we can add is a drop-down list.
This allows us to restrict the data entry to a predefined list.
If we have a look at our location for example,
it should be either showroom or basement.
Using a drop-down list here will avoid spelling
mistakes and entering invalid entries altogether.
It will also speed up the process for those who enter the data.
Okay, let's have a look at how we do this.
Once again, we're going to start by selecting the cells we want to add the validation to.
I'm then going to click "Data Validation" and this time,
in the "Allow" option I will choose "list".
You'll notice that a "Source box" appears and here is where you either type in
your list or you refer to a lookup list somewhere in your workbook.
In this case, we're going to start with
just typing in the values we want to see in the list.
Now one thing to keep in mind here is that
the drop-down list will be populated in the order that you type it in.
So, if you want the list to be sorted in alphabetical order,
make sure you type it in alphabetically.
You also must make sure that you separate your values with a comma.
You will notice that the In-cell drop-down is automatically ticked.
Leave that because this is what's going to give you your drop down list.
You also have the option to ignore blank and what that means,
like we said in the last video,
is that while the cell remains blank, it won't be flagged as
an error by Excel and generally that's much easier to work with.
So once we're happy, we're going to click "OK".
Now if I come and check my drop-down list,
here are my two options: showroom and basement.
Okay. So this is fine for short lists but where you have lots
of values or where those lists are likely to change frequently,
you don't want to be constantly changing your source and the data validation dialog.
In those cases, having a lookup list somewhere in your workbook is a much better idea.
I'm going to take you through the steps of creating such a lookup list now from scratch.
Okay, let's start with our column origin.
I'm actually going to select not just the heading but all the data and copy,
so I click and click again.
I'm going to come to my list sheet now which is where I'm going
to put my lookup lists and paste the data from the column here.
Now, of course this has lots of duplicates in it.
So the first thing I have to do is to remove the duplicates and I do that by coming up to
my data tab and click "remove duplicates" and then click "OK", too easy.
Now I'm going to change the heading name to origins,
just because I don't want it to be the same as I had in my data.
At this point, I have my lookup list and I could use it exactly as it is.
But the only problem is that if I do that,
when I try and add values to the list,
the data validation won't pick it up automatically.
Now, I would like my validation to be as automated as possible.
So, I'm going to add another two steps.
I'm going to, first of all,
name the range and then add that range to
a table and what this will mean is as I add new values,
the table grows and the named range grows along with it.
Total Excel magic.
Alright, let's see how we do this.
I'm going to press "Control A" to select all my data and then I'm going to come to
my formulas tab and I'm going to click on "Create From Selection" and then click "OK".
Easy as. What this has done is,
it has named the set of cells, origins.
If you look at my name box,
you can see it there.
Now the next step is to convert this to a table.
So again, make sure you click somewhere in your data and then
insert and table or Control T and then just click "OK" or press Enter.
Now if you don't like all that formatting,
it's easy you just come up to your table tab,
select light in the table styles and it will look like it did before.
But what you will notice is in the bottom right hand corner,
there's this tiny little blue triangle and that demarcates the end of your table.
But when you come to add data to the bottom,
your table will automatically grow and that's what's going to give us the automation.
One final step, let's sort the data so that
the items in the drop-down list will appear in alphabetical order.
Okay, let's now watch this whole thing work in practice.
So we're going to come back to our inventory and I'm
going to add validation to my origin column.
So with one click,
we select the column and then we go back up to data,
click on "data validation".
Okay. We will choose again a list but now in my source box,
I now need to define our named range.
Now if I just type the word "Origins",
it's going to give me the word origins in that list,
so we can't do that.
What we have to do instead is we have to type "equals" by
either your cell reference or in our case, the named range.
If you don't remember what you named range is called,
a nice little shortcut,
press "F3", choose origins and click "OK".
Now when we come back and check all of these,
you will see how we have a nice little drop-down list with all of
our origins as shown in our list worksheet and here is the magic.
Let's come to our list tab and at the bottom,
let's add a new region as Norton's have now started importing from Italy.
When I press "Enter",
you will see that the little blue triangle has moved down.
So my table has grown.
When you came back to the inventory and click your drop-down list,
you will see you now have Italy added to that list.
So now we have seen how we can create
automated drop-down lists to help add validation to our workbooks,
to prevent errors and to make our data much more efficient.
In the next video,
we're going to make our data validation even more powerful by looking how we can
use formulas in data validation as well. I'll see you there.
