In this video tutorial, we're going to learn a little bit more about
Microsoft Excel, and I consider this an
advanced feature or technique in using excel
and it's called Data Validation and I have
here is the Spread Sheet that I made in
a previous video and if you haven't seen
my other excel's video, I highly recommended that you do
watched those other tutorials before watching this one. But, anyway as you see here, I have a spread sheet listing some
Synthpop or New Wave bands
and then also here on Sheet 1 I have a
list of DVDs um Movie Inventory
basically. And what I like to do right know
with this spread sheet is add a little bit of data validation
So what is Data Validation? Basically, what it is
is a way for you to almost guarantee
pretty much ensure that you have in a particular column
data
that is logical data that fix certain
parameters and if we're talking about text
it helps you to ensure that the text
is spelled correctly. So how could we set this up?
Well, what we are gonna do, is we are gonna use the data tab
When you click on the data tab, the data ribbon appears
and you can see here in the data tools group, we have
a button that says Data Validation.
Now, how do I use this? Well step 1 is to select
something that I want to affect. I would like
to affect the data in column B
So, I'm gonna select column B. The quickest way to do that is just
to click on B, it highlight the entire column all the way down to
infinity basically. Now that selected here
in a data tools group, I'm gonna click on data validation. And I get this nice pop up here that I can use to make
some decisions and some choices about the data
that would be in column B. First off, noticed that it says
any value. Right now, it will
allowed anything to be type into this column. I wanna change
that and make it so that's not just any value
its a Whole Number
A year should showed up as a Whole Number
And as soon as I selected Whole Number, did you noticed what happened?
The information below changed.
Now, I can go and said I would like that data to be between
two numbers that I would put here or
maybe not between those two numbers or greater than
or less than or less than or equal to there's all of this great
options that you can put in there. So I'm gonna say between
I don't know
when did the New Wave and Synthpop etcetera begin
Probably, mid 70s. Im gonna say
1974 and the maximum, it will be
relevant forever. So, let's just put the year
maybe 3900. That should covered it.
And then I can click OK. And now, watch!
If I type something into this box, that doesn't
fit those parameters. Hit enter.
It warns me 'this value does not match the data
validation restrictions defined for this cell' and I can
either cancel which gets rid of the text that I put in there
or I can retry. And so
with Data Validation, this would prevent
people from entering data that just makes no
senses. That doesn't fit certain parameters.
And now if I select that again, and go back into
data validation, you can see that I have some advance options
as well. And the advance options are here.
There's a tab, actually a couple of tabs, that I can click,
to put in some messages to whoever using this
Spread Sheet. There's a check box that says 'Show input
message when cell is selected'. So when the user
clicks to enter data into one of the cells
in column B, this 'Input Message' could
appear. Maybe the messages : Make sure
the year is 1974,
or later. I can click OK. So now,
anytime someone clicks, it will remind them "Make sure
the year is 1974 or later". Because that's about when
the best genre of music was starting to developed.
Similarly, I could also just select column B
go to the Data Tab, go to the Data Validation
and I could go in and put in an Error Alert.
And, Error Alert are pretty similar to Input Messages.
But, instead of trying to catch the mistake before it happens,
an Error Alert just alert the user
to the fact that they made a mistake. So what can I do here is
I can decide to change 'The Style' of the Alert
if I would like to, I can change it to a 'Warning' signal
I could change it to an 'Informational' signal or have it be
a 'Stop' signal. And that's the default. I'll just leave it to that.
And here, on 'Error Message' I'll type in the date you enter
doesn't fix the
parameters
of New Wave/Synthpop.
There you go. Now, you've probably noticed that in both of these cases
'Input Message' and 'Error Alert' I skipped entering a tittle.
That's okay. I mean I could put in the title if I want to.
But, it is also okay just to skip that and leave it blank.
I'll just click okay. And so now,
if I click in this column, I do get a message that says
'Make sure that the year is 1974 or later'
and then, if I type and put in something that doesn't fit the parameters,
I'll get the warning that I entered, "The date that you
entered doesn't fit the perimeters, the New Wave/Synthpop."
So, this Data Validation can be very very helpful.
As you try to make your Spread Sheet
have accurate information. Especially, if you're sharing
a Spread Sheet with other people that maybe don't know Excel as well
as you do. And of course, because you've been watching my videos,
you know it pretty well. Real quick, I want you notice
that this works also with text. So I've selected Column A,
that mostly text. And when I went into Data
Validation, it gives me the option to change it, and
I can affect this text, based on the length of the text.
So that's just one example, of how it can affect text.
So the length of the text in this case would have to be less
than 22 characters.
So you click OK. And if I'm trying to typing something way too long
Let's see what happens. And you can guess what
happens. It says 'It doesn't match'
So, I'll just cancel that out. Ok, so that's
the most common kind of Data Validation.
Where you set parameters for numbers or for text
including date something like that. But there is one more
kind. And it's pretty useful, in my opinion.
And for this I'm gonna switch back to sheet number 1,
Where I have the list of some of the movies that I have in my collection.
If you aren't familiar with some of these like 'The Princess Bride'
like 'The Dirty Rotten Scoundrels' and even 'Mystery Man'
is pretty sweet. If you interested in learning about these and
maybe picking 'em up, lookin' in the description below this video,
and there's a link to some these movies. I know, most people have heard of
'The Princess Bride' but if you're not familiar with some of these others
there's some really good one there that I recommend in the description below.
But here in this particular sheet, what I like to do is use some
Data Validation to force myself and anyone else using this
spread sheet to spell the 'genre' correctly.
Looks like, I've done a pretty good job spelling them correctly already.
But, that may not always be the case. So how could I do
this? How could I use Data Validation to force myself
to spell these correctly? Well, what I could do is set up
a list over here on the right let say.
And I'm gonna scoot over just a little bit more to the right.
And I'll make a list of 'The Genres of Movie'
that I'm gonna
have in my collection. So, science fiction,
fantasy, horror and give me
a minute to create a complete list of the kind of movies
that I'm gonna have
and then we'll resume the tutorial :) OK, so there's my list
and it's pretty good list for me and my collection. So now,
what I can do is I can go over here, to my 'genre' column,
I select Column E, it highlights the entire column,
and then I go to the Data Tab, Data Ribbon
and the Data Tool's group, I click on the
Data Validation, but this time, instead of choosing
a value or a whole number or something like that,
I'm gonna go to list, and it have
a few options here. But the main thing you need to know about
is this : Source. What you are supposed to do, is click here,
next to source, and then select
the source for these, K.
So this the source for what's gonna go into this
column. I hit enter on the keyboard.
Click OK. And now watch what happens! Whenever,
I click in this column, anywhere in the
column, I get a little button here after the right,
that I can click and I choose science fiction,
or I can choose childrens,
or I can choose comedy, whatever I want to choose.
And it forces me to spell it correctly
Now, let say I just type something in there,
Maybe I wanna typing family, I misspelled it.
Look! It doesn't let me. Because it is a Data Validation's
issue. And it's not fitting the parameters of
this list that I created. So this really is a great
method for preventing people from misspelling word.
Now, if you don't wanna have to go over here and click on the button each time
you can hold [ALT] and tap the [down arrow] key
on the keyboard and then you can use the [down arrow] key again
to select the one that you wanna choose. So that's another option
if you don't wanna use the mouse to click the button and select
from the list that you made. So isn't that great? I really
like Data Validation. There are couple more things
tho that you might wanna know about Data Validation. The first
is that noticed the word 'genre' has
a button as well. So if I try to change this
from 'genre' to something else, its gonna
force that change to match this parameters. If I don't
want that to be the case. I can click on the cell like I am,
and then I can go to the Data Validation, here. On the Data
Ribbon. And click. And then I can choose,
'Clear All' and that is gonna clear just all
of the Data Validation for this particular cell.
Not for the one below it. So now, the cell with the title 'genre'
in it doesn't have that button that the other cells do have.
The next thing, that you should also know about Data Validation
is that the way thing appeared in
a list does affect how they appeared over here.
So these word are not in alphabetical order. And
it would be nice if they were. So I'm gonna click and drag
to select just the range that I would like to alphabetize
and then I'll go to data and I'll choose 'sort A to Z' and it's
sort it just that text, nothing else. A to Z.
So now, let's try it! When I click on the button, all
my options coming alphabetically in that list.
So that's nice to know. Now what if, heaven forbid
I get a romance's DVD. A romantic comedy
or something like that. If I go in and just add that
to my genre list over here on the right
let see if it works. Is it gonna add it over here
to my drop down list? No. It didn't. It didn't work.
Now, the reason why is because my original list
was set up to be this. And if I add something
below that to the right, above, to the left,
it is not gotta be added to my drop down list over here on the left.
But, if I want to add somewhere in the middle
Then, it might work. So, let's try that out! I'm gonna
click on this and try to drag it up into
the middle of this list somewhere. Now, if I just
click and drag it says 'There's data already here.
Do you want to replace it?' I'll say NO. I'll cancel that.
So the trick is, to HOLD [SHIFT] on the keyboard
and then you can click and drag and drop it in
and now let's try it! If I click on a cell
and my 'genre' column, click the button.
Look! Romantic now included there in it and Science Fiction
which is at the bottom, you might have thought it would've been pushed off
the list. It's still there. Thank goodness. But um
unfortunately romance is also included now in
this list. But it's not alphabetical. So again, I might
want to click on it and HOLD [SHIFT] and then drag it down
in a more alphabetical order. So those are
some nice things to know about with regard to Data Validation.
The last thing that I think, you really need to know
regarding Data Validation is 'What do we do
with this listed sitting out here?' I cant just delete it. And if I do
it will ruin my drop down list over here
and what if someone that I share this spread sheet with,
what if they delete it? What if someone on my family accidentally delete it and
messed it up? So what you can do, you can do a number of
things. You could highlight that text and
you could right click and cut it. And
then paste it onto another sheet and at least
in the modern version of Excel, the last
few version of Excel, that would work
for you. See that? The drop down list still work, even
though the list has been moved. It's been moved to sheet 5
in this case. Another thing you could try if you want,
you can right click on the column number and you can choose 'Hide'
and it hide the column. And you can do here on Sheet 5
or Sheet 3 OR just here on Sheet 1.
I've could have just hidden it there. The other thing that you can do of course is,
you can put way over to the far right where
it's probably not gotta be seen. There's a lot of ways that you can
hide that list once you've established it. Of course, it's
a good idea that you remember where it is so you could add into it
if you ever need too. So I'm gonna un-hide that.
So I hope that you find Data Validation to be helpful as
a way to required the contents of a cell or
a column or a row to fall within
certain parameters whether it be numbers,
whether it be text. And it's also wonderful
for creating the drop down list.
Thank you for watching this tutorial and please consider connecting
with me on my social media website like Facebook, Twitter
and Pinterest. And please do
subscribe to my you tube channel for more videos about technology
for teachers and students. And if you enjoy this video, please click
like below and watch for a new video at least every Monday :)