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 :)
