Data Validation

Quick reference

• 00:04 For this video,
• 00:05 I want to assume that we're building a nice little holiday treat pre-order form.
• 00:09 I've got a field for name and phone number, a pickup date, and
• 00:13 a bunch of places here to fill in my quantity and products.
• 00:17 My concern is is that I want to make sure that my pickup date is after today's date
• 00:21 and right now, if you go and just bash in a random text string, it will accept it.
• 00:25 And if you go in and say, hey, look, I'm going to go and pick this up in 1982
• 00:30 October, 31st, it's going to accept that too, that's not really cool.
• 00:34 I want to try and figure out a way to prevent that from happening rather than
• 00:38 set up a bunch of rules to try and
• 00:39 highlight it if it does happen, this is what we call proactive data validation.
• 00:43 And we actually have a feature for
• 00:45 working with this under the data tab called Data Validation.
• 00:49 And what you'll notice is, by default,
• 00:51 every cell, will allow you to enter any value.
• 00:54 And this makes sense, because when your spreadsheet's first opened,
• 00:57 we don't know what you want to put in any of those cells.
• 01:00 But we also have the ability to actually restrict that to certain things.
• 01:04 So if you wanted to say, hey, look, I want a whole number that is equal to
• 01:07 a certain value, you better tell your users what that's going to be or
• 01:11 it's going to be a real challenge, you could set up that rule.
• 01:14 Now, for me, what I'm interested in right now of all these options is I'd
• 01:19 like to go to date, and what I want is I want a date that is greater than.
• 01:24 And we're going to use a nice little formula here to pick this up,
• 01:26 we're going to say equals today.
• 01:28 Notice you can pick up values from cells, you can hard code values,
• 01:31 or you can put in a formula here.
• 01:33 And what will happen at this point in time if I say, okay, is if I try and
• 01:37 enter that date of 1982, 10, 31, it's going to come back with
• 01:41 a message that says, I'm sorry, but you can't do that, it's not going to work.
• 01:46 On the other hand, if I go through and put in a message or value to say, I'm going to
• 01:50 pick this up on October 31st, 2050, at this point, it's going to allow it.
• 01:55 So this is okay, this is doing exactly what I want.
• 01:58 The thing is that nobody knows that when they hit that cell, so
• 02:01 I'm going to give them a little bit of help here.
• 02:03 We're going to go back to data validation, and
• 02:05 I'm going to move over to the input message tab.
• 02:07 Notice that it's got a checkbox here to say show this message when the cell's
• 02:11 selected, but there's nothing in here, so at this point it's suppressed.
• 02:14 We're going to change this, it's going to say, enter a date.
• 02:19 And we're going to say, please enter a date later
• 02:23 than today's date, and we'll say okay.
• 02:27 And at this point, when you select the cell,
• 02:30 our little yellow message will come up.
• 02:32 If we go and select a different cell, it goes away.
• 02:34 Go back to it, comes up.
• 02:35 Awesome for reminding people what needs to go into these cells.
• 02:39 I will warn you though, people get a little tired of these if this is in
• 02:41 a spreadsheet that they use every single day.
• 02:43 So use this with caution, okay, you don't have to define one of these but you can.
• 02:48 Let's go back and see if we can do something else here,
• 02:51 let's go customize our error message.
• 02:53 We're going to say that this is an invalid date, okay?
• 02:56 And this is a stop error message.
• 02:58 And what I generally advise people to do is if you've given somebody a little
• 03:01 tooltip text here to tell them what to put in there and they get here,
• 03:04 they probably need some help.
• 03:06 So we're going to say, please enter a valid date in the format yyyy/mm/dd,
• 03:12 give them a little leg up here to understand what they need to do.
• 03:18 And now when we come in here and bash in some text,
• 03:21 we get a custom error message here.
• 03:24 And this is kind of cool, you can hit retry, that'll take you back to try and
• 03:27 do it again.
• 03:28 Of course, if you put the same thing in, you're going to get the same message.
• 03:31 You can cancel or you can click Help to go to the Excel Help, which to be honest,
• 03:34 is going to be no help whatsoever.
• 03:36 So I'm just going to cancel this for right now.
• 03:38 Now, the thing to know about these data validation messages, though,
• 03:42 is that we have different options.
• 03:43 We've got a warning.
• 03:44 Now that's going to cough up a nice little yellow message here.
• 03:47 And what you can see is that the difference is that this will actually say,
• 03:51 would you like to continue even though you've entered invalid data?
• 03:55 And if I say, yes, it'll let me put it in the cell.
• 03:58 In this case, this type of error message handling is not what we want for
• 04:02 this particular form.
• 04:03 But if you are using this to actually say, hey, I'm going to prompt to say,
• 04:07 is my value within a certain range?
• 04:09 Because that's normal, we could say this is outside of the normal range.
• 04:12 Are you sure you want to continue?
• 04:14 So that actually could be useful.
• 04:16 Finally, you get an information message, the information message here when you go
• 04:20 and bash something in will just pop up a nice little thing here to tell you
• 04:23 that it's not very good, and you can say okay, and commit it.
• 04:26 And that just makes no sense to use whatsoever, that's a nag, and
• 04:30 it's just irritating, so I wouldn't use that at all.
• 04:33 But if I go back, I'm going to set this back to the stop rule that I want and
• 04:37 say, okay.
• 04:38 Now, the last thing that I want you to be aware of around data
• 04:41 validation is that you can clear your rules by coming back and seeing clear all.
• 04:45 You can also run into challenges because when you copy data and
• 04:49 paste it into a cell, the data validation rule is not triggered,
• 04:54 it's only triggered by manual entry.
• 04:57 You can see the rule is still here, it just never got fired.
• 05:00 So this is unfortunate, something we'd like to see change, but
• 05:03 it's just the way it is.
• 05:04 But if your form is set up to encourage people to manually enter data, well,
• 05:08 at that point in time, everything should be good to go.

Lesson notes are only available for subscribers.