Join Dennis Taylor for an in-depth discussion in this video Use custom formulas in data validation to keep out bad data, part of Excel Tips Weekly.
- [Instructor] Excel's Data Validation feature found on the Data tab, is different than a lot of other data management tools because instead of dealing with data that's already there, it's designed to control data before it gets into a worksheet. Let's say that in this worksheet, we want these shipping dates to be entered here, and the shipping date has to be more than three days after the order date. When using data validation, it's not uncommon to select an entire column. Often you are working with lists. The lists might grow, so let's apply the rule to all of the cells in column C, so on the Data tab, Data Validation, first thought in the example here is, under the category allow, what would we allow in column C.
Let's go to date. Now there are some great choices here for restricting the dates between two different dates, later than a certain date, before a certain date. But if we're dealing with data in column B, none of these options actually quite fits. And so the other option you'll often be using, although it certainly isn't obvious, is the word custom, meaning a custom formula. And a formula will be a little bit strange, at first. I'm gonna be typing here, = C 1 > B 1 + 3.
Now even though there's no date in C1, nor will there be one, or in B1 either, By using these entries, C1 currently is the active cell. By inference every cell in column C will have a rule that says compare this with the cell to the left, it's gotta be more than three days later. So I'm gonna highlight this and copy it and show it on the worksheet to make it larger. But let's apply the rule. And this throws you a little bit, but it is correct, it simply means, literally, that C1 and B1 don't fit, but we want to continue? Yes.
So here's the rule in place, I'll leave it there for a bit. So, let's make an entry here. Let's try the 4th. That's not more than three days, it is three days, but it's not more than three days, so that should fail, and it does. How about a date before this, back in January. That should fail, sure does. And how about the 5th, that's more than three days. Good, that's working. But then the thought occurs to you, you don't want to have any Sundays in here.
You don't do any shipping on Sunday, so how do we know what day of the week that is? Let's test it with this function. Weekday. Weekday function will return a value from one through seven. If it's a one, it's a Sunday. And that's what this happens to be here. So, although we don't typically prepare data validation formulas outside of data validation, let's use this information. Let's construct a formula here. Don't have to capitalize these, by the way. So, we want the following to be true.
Every time there's an entry, and by the way, in the formula, the entry will refer to C1. We want to make sure that the weekday is not equal to one, meaning Sunday, can't be a Sunday, and furthermore, and I'll just retype what we're seeing above here, the entry in C1 must be greater than the entry in B1, plus three. And again, I'm just gonna leave this out here for documentation for the moment. That's the rule that we want to use. The new rule in column C. So I'll simply edit this and copy it.
Control C, Escape, click in column C, go back to data validation, and paste in the new rule. Click OK. We get that same message as before here, do we want to continue? Yes. The data validation doesn't deal with existing data. And this is still Sunday, but let's test it. Let's make it be the following Sunday. See if that works. And this time, it should not, the 12th. Doesn't work. Now can we take it back to the five, that's what it had been. It won't let us do that anymore, either.
So it appears to be working, we've augmented this. And I think you can imagine certain situations, too, if you don't do shipping on Saturday, you'd have another entry here, like this one, but it would be the weekday of C1 not equal to seven. So we needed two different functions there, the AND function and the weekday function to make this work. But the rule's in place for column C, it's gonna keep out all Sunday entries, and it will make sure that the shipping date entry is more than three days later. And you should always check out a few more of these.
I'll do another one out here. I'll put in the 12th out here, which is a Sunday. That doesn't work, no surprise there. And we'll try this with a five here, that's a Sunday and it's three days later, but doesn't work because it's a Sunday, and it's not more than three days. The 6th, that should work. So reasonable test. Now, if you're working with time of sale, in an example here, you want to restrict the times between there. We could certainly do that.
And here we can actually refer to these cells. So in column G, let's make sure that for all these orders that we're tracking here, and let's say we've got a sheet of paper with these numbers, we want to avoid typo mistakes here. All time entries must fell within these two times. All of our sales are between 8:00 a.m., and 6:00 p.m., 800, as we sometimes say, 1800. So column G, Data Validation, Allow, Custom Formula. As we write the formula here, we can refer to those cells. We need two things to be true.
Equal, AND, left parenthesis, the entry is G1, once again, we're using the active cell as the point of reference. This automatically refers to all the entries in column G. G1 has got to be greater than or equal to the entry in H2, and that needs to be an absolute address, we'll press the function key, F4, comma, and the G1 entry has to be less than or equal to the entry in H3.
Press the function key, F4. Both of those need to be true, and I'll highlight that, too. Press Control C, click OK, paste it out here momentarily, Control V, there it is. And Cancel. Paste it right here, there we are. Okay, so, make an entry here, how about 7:45, doesn't work, it's too early. And how about 8:45, that should work.
Yep, not a problem. And, by the way, you can type other types of data entries. Five, space, P, means 5:00 p.m., that should work okay. Six, space, P, but not 6:01, that way doesn't match. 1801 is not gonna work, and you'll quickly be able to check that out. None of those are working because they're outside of this range here, and you can see easily how that's working, too. Now, at different times, we might want to control this too, in a different way. What if we said that the entries here have to be multiples of 15.
Now these happen to be, but right now, we can put in a a 5:10, we can put in a 5:13, if we want, right here, and that's okay. But maybe, what we really want, for ease of use, is to make these be a 15-minute interval. So let's say that our entries have to change. So, we can work off this existing formula, and I'll be using a different function here called MOD. The MOD function allows us to take data, for example, the minute that we get out of these entries, that's coming out of G1 here, the MOD function says, if we divide this entry by 15, we want to make sure that it has no remainder.
And that's sort of a convoluted way of saying, the entry's gonna be either 15, 30, 45, or zero minutes after the hour. So we want to make sure that that is equal to zero. Along with our other criteria that are already there, keeping that within that 8:00 a.m. to 6:00 p.m. entry. So this is the formula we will be using now. And it's a little bit tricky on the parentheses here. I think I've got everything in place, but for the moment, I'll press Enter, come back and copy all this.
Control C, Escape, let's go back to column G, Data Validation, put in the new rule. I'll just paste it in here. When you make a mistake in the formula, sometimes you'll get a message when you press OK, sometimes not. This message simply means that the data for G1 doesn't work. We ignore that. And let's see if this is gonna work now. I'll make a change here, and put in 5:23, that's no longer acceptable. How about 5:30, that should work, and it does.
And some other entries down here. 10:03 shouldn't work, nope, when you press Retry, by the way, you're in edit mode. If you click Cancel, it gets rid of the data completely. So, Retry, make this be 15. And one more here, 9:40, there we go, that doesn't work, we'll change it to 9:45. And there's one other kind of entry that sometimes you'll need to work with multiple formulas. How about a social security number? Here are the basic rules.
It's gotta be numeric, it's gotta be exactly nine characters, and the leftmost digit cannot be zero. So, let's set up a rule, say for this column right here. We want social security numbers here. Gotta be numeric. Must be nine characters, can't be zero in left number. These functions might be helpful. LEN has to do with length, ISNUMBER allows us to check if an entry is a number. Left allows us to pull data out of the left-hand side of a cell. So if this were a social security entry, here in column N, first order of business, go to Data Validation.
Is there anything out here that would jump into our minds right away? How about length, now that's text length, what if we try this, equal to nine. Well that might work. Let's try that. First of all, put in nine numbers. That's fine. Now put in eight numbers, that obviously doesn't work, because it's too short. Well, looking good, let's say, for a bit. Now, I've got Caps Lock on, maybe I'm not thinking, maybe this ends in zero, but I put in a capital O.
That didn't get stopped at all. What if one of those entries was a capital I. That doesn't get stopped either. So these are not numeric, and so our rule here is not working. That's why we might need to use additional functions. So here, too, preparing this outside of the entry, and if we're going to be using column N, let's use the cell N1 as the reference, we need a few things to be true here. We need to make sure that cell N1 is a number. So, ISNUMBER, and the length of that, that's LEN, of N1 must be equal to nine, and to keep out those entries that begin with zero, we haven't checked for that yet, but we want to make sure that the leftmost entry, the left character of N1 is not equal to zero.
So a lot going on here. Three different criteria. It must be a number, the length of it must be nine characters, and the leftmost character cannot be a zero. Once again, preparing the data validation formula outside of data validation is not exactly typical but I'm doing it here for display purposes. So I'll highlight all this, press Control C to copy it, temporarily put in a leading space to display it there. Go to column N, Data Validation, and Allow Custom Formula.
Control V, click OK. So I've got some rules. Now, once again, existing data is not controlled here. And by the way, I should have put in the title first, because now I couldn't put in the title. We'll come back and deal with this later. But let's make a new entry here. And I'll make it be nine characters. So no problem. This one, I'll start with zero, doesn't match the criteria. I'll do a Retry, maybe it should have started with one. Okay, I put in one, Enter, but I typed an I, doesn't work, does it, and it shouldn't work because that's one of the restrictions.
So, one should work. There we are. So, if it's not a number, we saw the restriction there. It caught it. Didn't like the I, the length too short. Obviously, that's too short. It catches that, as well. So we deal with all three of these. Again, we're not doing it directly, but we're doing it by way of a data validation formula. And so we see, in this example here, and in the prior two examples regarding dates and times, how powerful this tool is, although at times you do have to work with additional functions to make it achieve your objectives.
Skill Level Appropriate for all
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.