Data Validation

Quick reference

• 00:04 One of the quickest ways to mess up any file is to send it to somebody else who
• 00:09 doesn't know how it works and have them put the wrong kind of data into a cell.
• 00:14 So if you look at the form that we're looking at here we've got a bakery
• 00:18 treat order.
• 00:19 And the field that I have selected,
• 00:21 B6, is indicating that we'd like to have the pickup date for the specific quarter.
• 00:27 Now the challenge here is that if somebody were to go and type text into this,
• 00:30 this really wouldn't work for us.
• 00:31 So what we'd like to do is we'd like to actually go back and
• 00:34 add a little bit of validation to this to make sure that somebody, A, knows they
• 00:38 should put in a date, and B, actually puts in a valid date in that field.
• 00:42 So to do that, we're gonna use Excel's data validation feature,
• 00:45 which can be found on the Data tab under Data Validation.
• 00:50 This brings up the dialog box here which allows us to, and
• 00:55 by default any cell will allow any value to go into it.
• 00:58 But we can actually restrict that somewhat and say you know what,
• 01:01 let's put in a date.
• 01:03 And it says well what's the date that you'd actually to put in?
• 01:06 Well I can say you know what, let's go with =today- 31,
• 01:11 so somebody can fill out this order form and say it can be,
• 01:15 if it was recorded after the fact, up to 31 days ago, and
• 01:19 I'll say that it can be picked up anywhere from today to today + 10.
• 01:24 Okay, so within ten days of today's date.
• 01:26 So formulas are perfectly valid in here.
• 01:29 We'll say OK.
• 01:30 What you'll notice now is that if I try and put in, say, December 25th, 2020,
• 01:33 way outside of the realm here, you say OK,
• 01:39 and it comes back and tells us that we can't do that, that's not valid data.
• 01:43 So okay, let's retry this.
• 01:45 How about we go with 10/19/2015 which is when I'm recording this valid.
• 01:51 Oh, it's still not gonna let me do it.
• 01:53 And that must be because it doesn't like the date format that I'm using.
• 01:56 So let's try using an ISO format, and that works much better.
• 02:02 So it's actually allowing me to take this as a valid date.
• 02:06 So this is actually good because it prevented me from putting in something
• 02:09 that was in a date format that Excel didn't actually recognize in this case
• 02:12 too, which is fantastic.
• 02:15 But how can I actually help the users understand that even better?
• 02:18 Let's go back and look at our data validation rule.
• 02:20 I am just going to click on it here.
• 02:23 We have the option to provide an input message and
• 02:26 in this part here we might want to say, Enter a date.
• 02:30 Please enter the pickup date for the order.
• 02:37 What you'll see now is that every time someone selects the cell,
• 02:41 this little box will pop up to let them know.
• 02:44 When we click somewhere else, it goes away.
• 02:46 Just an interesting tip as well with this is if you move one of these
• 02:49 boxes out of the way, as I go and
• 02:51 click somewhere else, that box will actually move back on the Mac.
• 02:55 That's interesting, on the Windows side, it actually doesn't until you close and
• 02:58 save the file.
• 02:59 There is a little bit of an inconsistency between the two platforms there.
• 03:03 Now this is great and this can help tell people what to put in the cell but it
• 03:07 doesn't tell them how to fix the problem if they ran into what I did were the date
• 03:10 format was incorrect so in that case what we should do is if we kick off the error
• 03:15 alert and we've already told them what to do, we should probably provide them with
• 03:19 something that will actually allow them to figure out what they need to do.
• 03:23 So we'll give them a message that says invalid data,
• 03:29 and we'll say please enter a date in the format yyyy-mm-dd.
• 03:35 And hopefully that will be explanatory enough.
• 03:38 Now we have a few different options as well.
• 03:41 The stop message will actually prevent somebody from
• 03:44 putting in data until they get it right.
• 03:46 The warning message actually has a different icon on it, and
• 03:49 this will give them the option it will say hey you didn't enter a valid date,
• 03:52 did you want to enter it anyway?
• 03:54 And you could say yes.
• 03:55 The other one just kicks off a little bit of information to say hey you entered
• 03:58 something that was incorrect.
• 04:00 So we're gonna go with Stop.
• 04:02 We want to prevent somebody from putting something into the model that
• 04:04 they shouldn't.
• 04:06 So now, if I go back and try this again 10/19/2015, a date format that it doesn't
• 04:11 recognize here, it comes back and tells me, it's invalid data.
• 04:16 Please enter a date in the format yyy-mm-dd.
• 04:19 So I can go back and actually try that and say,
• 04:22 2015-10-19 and it'll actually accept it.
• 04:27 There's all kinds of formatting rules that are available for
• 04:30 working with our data validation.
• 04:33 We could set up rules that are based on text length, for example.
• 04:38 Make sure that it's over a certain number of characters.
• 04:41 We could set up whole numbers, so in a case of quantity, we would probably want
• 04:45 to set up a rule because we aren't gonna sell half a cookie for example.
• 04:49 So we say, to force a quantity, of a specific, greater than zero or
• 04:55 whatever we decided that we would actually want to do in this particular case and
• 04:58 that's whole number.
• 05:00 The other option we have for working with things like prices,
• 05:03 we could setup decimal numbers.
• 05:05 So, whole numbers don't allow any decimals, decimal numbers do.
• 05:08 So, there's lots of options here.
• 05:10 You can use hardcoded numbers in your formulas here.
• 05:13 You have the betweens, greater thans, all kinds of different variables here.
• 05:17 So give the practice set a shot in here and see if you can actually follow through
• 05:21 the rest of the steps to actually set up the rest of the data validation.
• 05:24 You'll find this to be a very, very useful tool.
• 05:27 One quick caveat that I will tell you, though, is if somebody copies and
• 05:30 pastes on top of your data validation, it unfortunately wipes your data
• 05:33 validation out, and that's a real problem that we hope Microsoft fixes one day.

Lesson notes are only available for subscribers.