Data Validation: Preventing Garbage Input

Quick reference

• 00:04 Data validation sets acceptable entry values for cells.
• 00:08 You can set values for
• 00:10 text or numbers or you can use validation to create a drop down list.
• 00:14 For example, this sheet shows you fields for the entry of personal details.
• 00:19 Let's start with the Client Code field.
• 00:22 Select the cell where you want to enter the data validation.
• 00:25 Data Validation sits on the Data tab, under the Data Tools grouping.
• 00:30 That's what the icon looks like.
• 00:32 Click on the drop down, Data Validation is the first option.
• 00:36 The Data Validation dialog box has three tabs or input screens.
• 00:41 The first one is where you set the validation.
• 00:43 The second is for an Input Message and the last one is for an Error Alert.
• 00:48 Only the first screen is required, the other two are optional.
• 00:52 So let's try a basic validation.
• 00:55 Let's say we want our client codes to be whole numbers, six digit's long.
• 01:00 I click on Allow, and click on Whole numbers.
• 01:05 And I want my data between a minimum and a maximum.
• 01:08 We set the minimum to the lowest six digit number we can find, and
• 01:13 that's 100,000 or a one with five zeros.
• 01:17 The maximum, the highest six digit number we can find would be 999,999.
• 01:23 Using this minimum and maximum, we should be able to get any six digit combination.
• 01:28 So let's click on Input Message.
• 01:31 The Title should be Client Code, and the Input Message
• 01:36 is going to be simple, Enter the six digit client code.
• 01:41 The Input Message will be seen when the user hovers over the client
• 01:46 code field where the validation is taking place.
• 01:50 Error Alert allows you to customize the error message that pops up if someone
• 01:54 enters an incorrect value.
• 01:56 And you have three types of errors, you have a Stop, a Warning and
• 02:00 an Information error.
• 02:02 The Stop is going to result in a total rejection of the entry.
• 02:06 The Warning option will give you a warning message and
• 02:09 the option to either accept or reject.
• 02:12 The Information option will only tell you that it's not valid, but
• 02:15 it will accept the entry.
• 02:17 So let's try the Stop message and we'll go through all three.
• 02:22 For the title, we'll use Invalid Client Code.
• 02:25 And for the message, we can say something like, Client codes have six digits.
• 02:30 That's a good reminder for the user.
• 02:33 And we can click OK.
• 02:35 So here's my Input Message, Client code, Enter the six digit client code.
• 02:40 If we only have four digits in the field, we'll get our error message,
• 02:45 Invalid Client Code.
• 02:47 Client codes have six digits.
• 02:50 So let's click to Retry.
• 02:52 This time we'll type in 123456.
• 02:56 That's six digits.
• 02:58 And when we click Enter, it accepts the entry.
• 03:02 Let's change our validation to a Warning only.
• 03:05 Go back up to Data Tools, Data Validation.
• 03:10 And we'll change the Stop to a Warning.
• 03:12 Everything can stay the same, and then we hit OK.
• 03:16 So let's type in 1234 again, I get the same error message Invalid Client Codes.
• 03:21 Client codes have six digits, but the icon is different and
• 03:24 then I can choose to Continue, Yes or No.
• 03:26 If I choose Yes, it accepts the four digits.
• 03:30 The third option we have if we change the data validation error alert
• 03:36 again this time to Information only, the icon changes, my message stays the same.
• 03:43 Let's try this again.
• 03:44 We'll add the four digits, this message pops up.
• 03:48 Client codes have six digits and I can choose OK.
• 03:51 Excel doesn't reject the entry at all, the message was merely for
• 03:55 informational purposes.
• 03:57 Let's leave the option as Warning only for the moment.
• 04:00 Then we have time for one more validation in this video,
• 04:03 the Date of Birth validation.
• 04:05 We want to enter a date of birth down at the bottom field.
• 04:09 So let's select the cells, go to Data Validation,
• 04:13 okay let's type in a new Input Message here.
• 04:17 Let's change the title to, Enter date of birth.
• 04:21 The input message is going to tell the user the format we'd like, mm/dd/yyyy.
• 04:27 Simple enough.
• 04:29 We go to Settings, we need a Date.
• 04:32 Now let's say we only accept subscriptions for people older than, 16 years old.
• 04:37 So we need to set this part of the range using the end date.
• 04:41 What is the very last birth date we will accept?
• 04:44 Well, that would be 16 years ago today.
• 04:47 So we can type that in and make this dynamic equals today minus 16.
• 04:55 But today, deal is with days not years.
• 04:59 So we need to convert 16 years into days, simple enough.
• 05:04 We type in 16 times 365, close the parenthesis.
• 05:11 And that will get us what we need.
• 05:12 If you want to consider leap years, you could make a decimal provision for
• 05:17 leap years.
• 05:18 But let's keep things simple for the sake of this example.
• 05:21 I trust that you will put in the proper formula for
• 05:24 whatever your form provides access to.
• 05:27 So now what's the earliest birth date for a person who can enroll?
• 05:31 It's good to set a limit here to prevent any errors with typing in the wrong date.
• 05:36 So let's say these users won't be older than the age of 110.
• 05:41 If you type in a birth date saying you're older than that,
• 05:44 it's most likely a mistake.
• 05:45 So let's put that in here.
• 05:48 So start date, today minus 110 times 365.
• 05:54 Again, we will ignore the leap years and that should work.
• 05:58 We hit OK, and now let's enter a birthday.
• 06:01 Let's try this example.
• 06:02 We enter the 1st January 1900.
• 06:05 That's definitely older than 110 years old, and Excel rejects the entry.
• 06:12 Let's retry, let's try too young now.
• 06:15 1st of January 2015, that's too young, and Excel rejects that.
• 06:21 Okay, now let's try something that should work.
• 06:24 1st of January, year 2000, Excel accepts that, no problem.
• 06:30 Now remember the format we want for this field is month, day, year.
• 06:34 Let's see what happens if we swap the month and the day.
• 06:37 Let's enter the 31st of January 2000 but in day, month, year format.
• 06:43 And you can see even for
• 06:44 a date within the range Excel rejects the entry because I messed up the format.
• 06:49 So, there we go.
• 06:51 These are very basic data validations and we will look at the First Name,
• 06:56 Last Name and the other fields in the next lesson.
• 06:59 And we'll also add state with a drop down list.
• 07:02 I'll show you how to make a list for that.

Lesson notes are only available for subscribers.