Data Validation

Quick reference

• 00:04 In this module, we are going to look at data validation and
• 00:07 you will notice that I have an order form that we have created in excel.
• 00:11 Now the challenge here is that I have a pick up date and
• 00:13 I need somebody to enter a date of either today or later.
• 00:17 But unfortunately, with Excel, when you create a new workbook,
• 00:20 you can put anything to a cell.
• 00:22 So somebody could easily come and
• 00:23 just bang some random characters in and away it goes.
• 00:26 Or they could actually go in and
• 00:27 put in a date that's six months ago, which obviously isn't gonna help.
• 00:31 Now for the purposes of this video,
• 00:33 I want to make sure that somebody must enter a date from today or later.
• 00:38 And I can force them to do exactly that, using the setting called Data Validation.
• 00:43 So we're going to go to the Data tab.
• 00:45 Now, your button is probably going to be bigger than mine for this because my
• 00:48 ribbon's collapsed, but you'll notice that there's a button here for Data Validation.
• 00:52 When I click on that it'll actually bring me into this little wizard here.
• 00:56 And you can see that by default, I allow any value to go into a cell.
• 01:01 But the cool thing is, I can change this.
• 01:04 I can drop down and I can say Force it to only be a whole number or
• 01:07 a decimal number.
• 01:09 Force it to be a list or a date or a time or whatever, or even custom.
• 01:13 Now in custom, I can actually go back and
• 01:15 play around with all kinds of different formulas.
• 01:17 But I wanna force this to be a date.
• 01:20 And specifically I'd like to say that the date needs to be greater than or equal to.
• 01:26 How do I make it actually work so that it's always today's date?
• 01:29 I'm gonna use the equals today formula.
• 01:33 And now we'll say OK.
• 01:35 Now, today's date is November 17, 2018 as I'm recording this.
• 01:40 So if I go and throw in a date of 11/16 2018,
• 01:44 you'll notice that it comes back and it tells me that I'm not allowed to do that.
• 01:50 I can retry and try to enter a different date.
• 01:52 Maybe I'll put in this time the 17th and hit Enter.
• 01:57 And you'll notice that this time, it actually accepts the data.
• 02:00 So that's pretty cool, I can now force it to make sure that it's either gonna
• 02:03 be today or tomorrow or even maybe a week from now.
• 02:08 And everything will work fine.
• 02:10 But if somebody tries to go back and
• 02:11 put in something for June 30th, it's gonna give them an error.
• 02:17 Now this error isn't the most helpful thing in the world.
• 02:21 If you click the Help button you're gonna go to a generic help page.
• 02:24 You can cancel.
• 02:25 Or you can retry.
• 02:26 And it gives a nice little message here that says it's Microsoft Excel and
• 02:29 this message kinda freaks users up.
• 02:31 So let's cancel this and see if we can do better.
• 02:34 I'm gonna go back to data validation, And go into my settings.
• 02:39 Now you'll notice that we have a couple of different tabs here.
• 02:42 The first one is input message and
• 02:44 I could actually put something here that says Enter a date.
• 02:48 Please enter a date greater than or
• 02:54 equal to today's date.
• 02:59 This will actually pop up message that shows up on the cell
• 03:02 every time it's selected.
• 03:03 If I select somewhere else, the message goes away.
• 03:05 But as soon as I select it, it tells the user exactly what to go and
• 03:08 put in this cell.
• 03:10 But I can do even better if I go back to my data validation settings.
• 03:15 I also have the ability to customize the error that came up.
• 03:19 I can say, this is invalid data please enter a valid
• 03:25 date that is greater than or equal to today's date.
• 03:33 Now I have the style here for stop.
• 03:36 And when I say OK, and I try and put in something like June 30th,
• 03:39 you'll notice that it now gives me my own custom message.
• 03:43 With this custom title, and tells me the message that I want to work with.
• 03:47 Which is great.
• 03:48 I wish I could get rid of this Help button,
• 03:50 because this still takes me to a generic help page.
• 03:52 Which really isn't gonna help the users at all now.
• 03:54 But it'll still let me retry or cancel.
• 03:57 But we also have some other options that we can work with here as well.
• 04:01 We have the option to use what we call a warning.
• 04:04 It gives a different icon and when I say OK and try this now,
• 04:09 you'll notice that it comes back and says Would you like to continue anyway.
• 04:13 And I can say, Yes, and it will actually put it in the cell.
• 04:16 And the reason we would set our message up like this is to say something is
• 04:20 outside of our normal parameters, but are you sure you want to do it anyway.
• 04:23 So that's a nice option to have.
• 04:25 We also have one more option, which is the information section.
• 04:31 And when I say OK on this, and somebody puts in a new value,
• 04:36 it always pops up the message to which I can just say OK or Cancel.
• 04:40 So this is what I like to refer to as a nag screen.
• 04:42 And this will drive your users crazy.
• 04:44 Personally the thing I like to do is I usually end up using the stop or
• 04:48 the warning in order to make sure that my data is bringing in correctly, and
• 04:53 making sure that it don't get garbage into my models

Lesson notes are only available for subscribers.