Configure data validation

- Data validation lets you control what data can be entered into a cell. After watching this video you will know how to limit entries so that only a whole or decimal number can be entered, only items from a list, only dates and times that fall within specific ranges, and I'll show you how you can control the number of characters being entered into a cell. Let's take a look at our data validation workbook. First, we will start looking at the data validation options. We'll go to data, data validation, and open up the data validation window. So, we have three tabs. Settings, the input message, and the error alert. Your settings that you have available to you allow you to set the validation criteria. Now, by default, any value is allowed in a cell, right? We can do a date, we can do text, we can do numeric entry, but in data validation we control what's entered. Can it only be a whole number? Must it include a decimal and if so is it going to be a minimum and a maximum amount that you allow? Can it be equal to a certain amount? Does it have to be less than a certain amount? So, for instance if you're entering expenses into an expense report for food, let's say that they have a $50 limit so you could set a limit to less than or equal to $50. We also can create a list of items and that list shows up in a drop down list in the cell. And our users have to choose an item from the list. We can control dates. Do we want the date to be less than or equal to today or greater than or equal to today? We can also specify a time range. Text length. And we can set up a custom validation rule that uses a formula. Let's go ahead and start with the one at the top, the whole number. We're going to set it up so our mileage can only be entered in whole numbers. Now, when we start we begin by selecting all the cells that we want to apply the data validation to. So, let me cancel this window. And come over to the D column where our mileage will be located. Select the cells that I want to apply this to, go back to data validation, and we'll choose whole numbers. Now, do we want it to be between a certain amount? Equal to, greater than? These are all of the options that you have for your rules. We're just going to leave it as it is. The minimum of course is going to be zero. The maximum amount of mileage, we could put an amount in here. We can say it can't be more than 1,000 miles. And if it is they'll need to get approval before they can get reimbursed. Now, it's really not fair to apply data validation rule to a range of cells without telling people what you're looking for and that's where the input message comes into play. So, the title that will show up in the dialogue window will be insert mileage. And then we want to let them know what we're looking for. Mileage must be entered in whole numbers. Okay. Now, what happens if they try and put a number in that has a decimal place such as 26.5 miles? Well, we want it to stop them and let them know that they need to try again. And you can type anything in here that you want. You want to be nice though 'cause you're actually causing an error message to pop up if they enter something incorrect. Please enter a whole number. We'll click okay. All right, so, as we click on each of these cells, here is the first message that we included. Insert mileage, must be entered in whole numbers. Let's come down to row 14, to D14 and let's type in something that has a decimal place. 10.2. And as soon as I exit out of that cell I get my error message that I created. Please enter a whole number. Remember I titled it try again. And we'll let them retry and they can just type in 10. And then it lets them go forward. Now, you can also apply these data validation rules to text that's already in the cells. So, let's highlight the cells. And I want to see if we have any entries that don't meet the data validation rule. So, I'll go to data validation, circle invalid data, and look at that, we have some that have been entered in as decimals. So, this is a great indicator to me that I need to go in and change these to whole numbers. Now we can remove those circles by going back and clearing validation circles. Now, what if we want to make them choose from one of the parking lots that we have listed over here? Well, that's when we would create a list. So, let me select all of the cells that we want to apply this list to. Go to data validation, go over to settings, allow a list. And our source will be right over here. I'll collapse that, come over and select these entries, expand that, and click okay. Now, I would want to put an input message in, please select from the list. And if I want an error message to pop up with something specific I could type it in here. If not it will just say that they've made an invalid entry. We'll leave it as that and click okay. So, here we go. There's our list. See how easy that was to create? So, people will just simply choose from that drop down list. If they try and enter something else such as Avenue C, which is not in our list, as soon as they try and exit out of that cell, the value doesn't match the data validation restrictions defined for this cell. So, they can go ahead and retry and select something from the list. If you ever need to go in and edit a data validation rule, such as maybe adding an error message, you would select the range applied and go back to data validation and you could come in and add an input message, add an error alert, try again. And I've seen this on the exam before where they've asked you to come into an existing rule and make a change to it. Please choose from the list. There we go. So, you can come in and edit these. You can come in and change date ranges. We're going to set for the vehicle description a character length of only five characters. So, I'll select the range, go to data validation, back to settings, text length. And we're going to say that it must be less than 10, or even five. So, if after setting this you decide that you really need more than 10 character spaces allowed in this cell, you can come back in, select the range, and go in and change the validation. Now, for our date entered, let's select that range. Go to data validation. This is for January 2019. So, we want to allow the date between January 1, 2019 and the end date will be January 30, 2019. So then if someone tries to put a date in that's in February, they'll get an error message. So, those are some great ways that you can use data validation and now you know how to set them up and how to go in and edit a validation rule. Practice using this on your own so you'll be prepared when you take the exam.