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
10.5 KB Data Validation - Completed.xlsx
10.7 KB Data Validation - Extra Practice.xlsx
15.8 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 Data Validation in Excel is all about forcing the user to put the right kind of input into a specific
  • 00:09 cell and not letting them put the wrong kind of inputs in.
  • 00:12 There's actually a tool that's built right into Excel, it's on the Data tab and it's called Data Validation.
  • 00:19 To make use of this what we're going to do is we're going to select cells A4:A7 here that have
  • 00:23 dates in them and we're going to force users to only put in a valid date.
  • 00:28 So we go to Data Validation we click Data Validation
  • 00:32 and we at this point have the ability to choose what kind of data we want to allow.
  • 00:37 By default every single Excel cell will allow any value until you change it
  • 00:42 and look at all the options that we have here. The one that we're after this time is Date.
  • 00:48 We have the option to choose between, not between, all kinds of different options in here as
  • 00:53 well but for right now we're going to stick with the between rule.
  • 00:58 We get to define a Start date and End date so I'm going to put in a Start date = 1/1/2013
  • 01:05 and an End date of =12/31/2013. So this means that it's got
  • 01:11 to be between January 1st and December 31st 2013. When I say OK
  • 01:17 it doesn't look like anything's happened at all. As a matter of fact the rule is kind of hidden but what happens if
  • 01:23 we try and go in here and say let's go and put in 2010-04-13.
  • 01:28 And we get an error that says that the value we entered is not valid.
  • 01:32 OK... This little message here about the the user restricting values that's what tells us the data validation is actually active.
  • 01:38 Now that's nice but
  • 01:41 wouldn't it be nice if we could provide some better information to our users so they actually really know what's going on?
  • 01:47 So if we select our range again
  • 01:48 and we go back into Data Validation and select our Data Validation rule.
  • 01:53 It comes back and says alright what do you want to work with here? Well
  • 01:56 the first thing that I'm going to do is I'm going to go and change this a little bit. Let's make this a
  • 02:01 formula instead so we're going to say that the start date is equal to
  • 02:05 today minus 31. So it's always got to be within the last 31 days and the end date will be today.
  • 02:14 This is the data validation setup that we can force to say
  • 02:17 you only can enter data for the last 31 days period. You can't enter data for tomorrow, you can't
  • 02:21 enter data for next week. I only want to see historical data but not way past a ridiculous range.
  • 02:28 The other thing we can do now is we can go and select an Input Message
  • 02:32 and in this Input Message we have the ability to give it a title such as Enter date.
  • 02:37 And the input message itself can say
  • 02:40 "Please enter a date within the last 30 days".
  • 02:48 We said 31 that gives us one day of grace.
  • 02:52 When we do this, and we say OK, you'll see that now as soon as somebody selects the cell
  • 02:57 they get a little message that pops up that tells them what to do.
  • 03:01 And that's pretty cool.
  • 03:03 So now they know that if they enter a date that's too far back - say 2013-01-13 - it tells me that can't happen.
  • 03:13 But if I were to go in and say let's put in a date within the last 30 days (today is June 28 when I was recording this). So we say 2013-06-25
  • 03:24 it allows the entry which is great.
  • 03:27 Now, let's deal with that nasty error message though.
  • 03:30 We can't do it yet because we've only selected one cell. So we want to make sure we've got them all
  • 03:34 and we apply the whole rule to all the cells that we've set up. So we select A4:A7 again
  • 03:40 We'll go back to Data Validation, Data Validation
  • 03:44 and we're going to work with the Error Alert tab. Now there's three states to the error alerts.
  • 03:49 We've got Stop, which completely prevents the user from putting in data that is invalid.
  • 03:54 We've got Warning that will warn the user that something's outside the range, but let them commit it anyway at their option.
  • 04:00 Or we've got just a general informational tab.
  • 04:03 Now, we want to stop the user from putting in invalid data all together.
  • 04:07 So we're going to go and give it a nice title like "Invalid Data!"
  • 04:14 And at that this point, I always advise people when they're building spreadsheets
  • 04:17 to make sure that you recognize that we've given the people a label that tells them "this is what they need to put in"
  • 04:23 and they still haven't done it right.
  • 04:25 So rather than just tell them the same thing again, we actually need to provide a message that's helpful,
  • 04:30 that gives them a leg up and tells them how to actually solve the problem, as plainly they didn't get it the first time.
  • 04:35 So, we can start again with
  • 04:37 "Please enter a valid date within the last 30 days in the yyyy-mm-dd format"
  • 04:52 and say OK.
  • 04:55 And now what you'll find is, when they go in trying to enter a date that is far too old,
  • 05:01 it will actually come back with our custom message that tells them what to do.

Lesson notes are only available for subscribers.

Gift this course