Locked lesson.

Upgrade

About this lesson

Learn how to use Data Validation to ensure that users enter valid data in input cells.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Data Validation.xlsx
15.9 KB Data Validation - Completed.xlsx
16.1 KB

Quick reference

Topic

Using Data Validation to ensure that users enter valid data in input cells.

When to use

When you create a model and release it to other users it is important to make sure that they are putting the correct data into the input cells in order to avoid issues and errors.

Instructions

Basic Date Validation

  • Select A4:A7, go to the Data tab > Data Validation > Data Validation
  • Select to allow a Date, Between
  • For the start date enter =TODAY()-31
  • For the end date enter =TODAY()
  • Click OK and try to enter a date within the last 30 days, and one outside that range

Using Input Messages

  • Select A4:A7, go to the Data tab > Data Validation > Data Validation
  • Select the Input Message tab
  • For the Title enter “Enter Date”
  • For the message enter “Please enter a date within the last 30 days”
  • Click Ok and notice that as soon as you select the cell an input message pops up

Using Error Messages

  • Select A4:A7, go to the Data tab > Data Validation > Data Validation
  • Select the Error Alert tab
  • Choose the Stop style and give it a Title of “Invalid Data!”
  • Provide a clear message for your user that helps them fix the problem without calling you (they already couldn’t figure out your input message!)
  • Message: “Please enter a valid data within the last 30 days in the yyyy-mm-dd format.”
  • Click OK and try to enter a date within the last 30 days, and one outside that range
  • Notice that the invalid date triggers a much more helpful error than the default message
Login to download
  • 00:03 In this video we're going to look at data validation.
  • 00:07 And what data validation is, is it's a method that allows us to prevent users
  • 00:11 from putting in the wrong kind of data into our cells.
  • 00:15 So if we look at this particular order form that we've got here,
  • 00:18 we would want somebody to always put in a date that is either today or later.
  • 00:23 So in order to force that to happen,
  • 00:24 what we're gonna do is we're gonna go to data validation.
  • 00:29 And right now, you'll see, by default that we're able to put in any value in a cell.
  • 00:34 We're gonna change that right now.
  • 00:36 We're gonna say, let's put in a date.
  • 00:37 And it says, would you like a date between, greater than,
  • 00:41 we're gonna say a date greater than or equal to.
  • 00:45 And we can even put in a formula here like, =TODAY.
  • 00:48 And that means, that it will have to be a date greater than today's date.
  • 00:54 So when I say OK, if I try and put in a date of 6/30/2015,
  • 00:59 way ages ago, I'll get an error message that comes up.
  • 01:01 And says, hey this doesn't match the data validation restrictions
  • 01:05 defined for this cell.
  • 01:06 So that's good to know.
  • 01:08 It's not gonna work for us, so we'll say cancel.
  • 01:12 Now what we'll do is we'll go back to data validation.
  • 01:15 Well actually, let's try something different, let's go with 12/25/2015,
  • 01:18 just to make sure that we actually can put in something that works.
  • 01:21 So that's good, that will definitely work.
  • 01:23 So it allows valid data that's greater than today's date, today's date being,
  • 01:27 12/23/2015.
  • 01:29 But it's not gonna allow anything earlier.
  • 01:32 Now, I'm gonna go back, and give my users a little bit more to work with here.
  • 01:35 So when they select this cell, I'm gonna put in a message that says,
  • 01:40 Please enter a date.
  • 01:46 Please enter a date greater than or equal to today.
  • 01:49 And what you'll see now, is when I click OK, every
  • 01:55 time I select that cell, it brings up the message telling the users what to put in.
  • 01:59 So that's a little bit handy.
  • 02:01 Now, we can even, control the error message that comes out at the end.
  • 02:05 If we leave it undefined, you'll see the error message that you saw before.
  • 02:09 But in this case, we could actually go in, and put in a different message,
  • 02:13 like invalid date.
  • 02:15 And we could give our users, a custom message that says, please enter a date
  • 02:19 greater than, or equal to today's date.
  • 02:26 And we could even potentially give them more information there, but
  • 02:29 what format to put it in if we wanted to.
  • 02:32 What'll happen now, is when somebody tries to put in 6/30/2015,
  • 02:38 it comes up with my custom message, my custom header.
  • 02:41 Comes up with a custom message that I've given it as well.
  • 02:44 If you click Retry, they can go in,
  • 02:46 and they can try and change that date, so they could use something different.
  • 02:49 I'm just going to press Escape right now,
  • 02:51 because I wanna show you that there's also alternate methods for this.
  • 02:55 We could use a warning, it gets a different icon.
  • 02:59 And what this will do is, if I put in 6/30/2015, you'll notice that it says,
  • 03:06 hey, you're suppose to be entering a date, but would you like to continue anyways?
  • 03:10 So this allows us to warn.
  • 03:11 So we could actually say to someone, let's set up a data validation rule that says,
  • 03:16 we expect our data is gonna be between these ranges.
  • 03:19 We'll still let them put in something outside of that.
  • 03:22 But we wanna let them know first, that it's probably not acceptable.
  • 03:25 So in this case we can click yes, and it will accept it in the cell.
  • 03:29 We also have another message style as well, that is informational.
  • 03:33 And that will come up when we put something in.
  • 03:36 But will let us commit the value anyway, and just say OK.
  • 03:41 Right, so those the three different message styles.
  • 03:44 For the most part, what you're gonna wanna do is,
  • 03:46 you're gonna want to go with the data validation message.
  • 03:51 And actually prevent your users.
  • 03:52 Because if you can prevent your users from putting garbage into your model,
  • 03:55 then that's gonna prevent them from getting your model all messed up.
  • 03:59 With data that doesn't belong there.
  • 04:02 The only other tip that I would give you here is, if somebody has tripped on your
  • 04:06 error message, it's probably handy at that point in time to say something like,
  • 04:11 enter your date in the, for US English,
  • 04:16 it would be mm/dd/yyyy format.
  • 04:21 And that way, if you've asked them for a date, and they've messed it up,
  • 04:26 at least now, you can tell them how to actually correct the problem.
  • 04:29 So, that'll be much, much easier, in case they put in something like Dec.25.
  • 04:36 And this will say, at least, please tell me how this date's supposed to be input.
  • 04:39 So, some different strategy there for how to manage the actual dates.
  • 04:44 And make sure that people give you the right information.

Lesson notes are only available for subscribers.

Gift this course