Locating data validation rules and violations

- [Voiceover] Someone has just sent you an employee list, like the one we see here, and as you start to make certain entries and adjustments, you realize there are some problems that you can't quite figure out at first. And data validation rules which are in this worksheet in different locations at least initially might be hard to track down unless you know some of the techniques for getting there. Imagine I'm making a change to one of the employee IDs. I'm looking at a sheet of paper here, and this particular employee ID here begins with zero. So I'm double-clicking in this cell, D4. Put in a zero, I press Enter.

I get a message. The value doesn't match the data validation restrictions. Not sure about that one. Come back to that later. The residency code for this first person here should be a six. Type a six, press Enter, and there's some restriction there that I don't know about preventing me from putting that in. Or I'm changing the compensation amount for this person, 133,000. That's not acceptable either. So how do we track down, how do we know where these rules are and how do we deal with them, how do we adjust them and so on? First of all, in any worksheet at any time, you can go to the Home tab, press Find & Select, far right button, and choose Go To Special.

That's one way. Or possibly, Data Validation, can go this way. That highlights all the cells that have data validation rules in them. We don't know what they are necessarily. We might scroll back a little bit too, see if there's anything off to the right. All those cells there have data validation rules. The other approach, by going to the Home tab, choosing Find & Select, and then Go To Special, could lead us to this choice here, Data Validation. And we see a choice called All, click OK. That does the same thing. But there's another choice out there, too.

Suppose we've discovered there's a data validation rule here. We wanna just make sure where else that same rule exists. Now you can have data validation rules by column, by row, or just across different cells. But let's see the data validation rule that exists here. Where else does it exist? So, Find & Select. This time we use Go To Special and Data Validation, but this time we'll click the box for Same, and click OK. And all the cells in columns G and H have the same data validation rule.

Now when a cell has a data validation rule, you can click in it, or in this case we've, we can then go to the Data tab in the ribbon and choose Validation and see what the rule is. The data validation rules for both these sets of data here, and it looks like it's for the entire column, must be between one and five inclusively. We see that spelled out here. It's gotta be a whole number between one and five. That's it. We could delete that right now by choosing Clear All, but let's say we don't wanna go there just yet. At least we found out what the rule was.

Now when you click on a single cell, you can certainly do it this way, too, go to Data Validation, we see the rule there as well. There's a rule over here in column L, let's just click there. Many, many times, but certainly not 100% of the time, the rule is in effect for an entire column. I wouldn't say that's definitive, but most of the time, probably is. So we could, if we wanted to, go back to Home tab and choose Go To Special. We could also press the F5 key, Go To Special. Data Validation, Same, click OK.

All those cells have the same validation rule, then jump right in to the Data Validation command. We see here that all those entries there in column L must be less than or equal to 130,000. So earlier when I put in 133,000, that's what happened. That's why we see the problem or had seen the problem. So we've got them in different locations, and how about column D? It didn't accept that leading zero there. We don't necessarily have to click that cell or click the column letter, we'll just click somewhere in here. Data Validation, what's the rule over there? The text length must be equal to seven.

Now by typing a zero, you wouldn't exactly think that would be a problem, but it is in this case, and we saw that happened. Doesn't like a leading zero. Because if it's numerical, it somehow, at least the others are numbers, it thinks that it's length six. So problems that we sometimes don't anticipate. Now if you're in charge of this, of course, you could remove these rules very easily. We could just remove all of them at once. We can just highlight this and go to Data Validation. And it says it contains more than one type of validation. Erase current settings and continue? We could click OK and then clear all and we have no rules left.

But at least we got the possibility of tracking them down and knowing what they are. I think you can sense they're really valuable, too. But here's another way to do it. Suppose this list involves some entries. Maybe you hadn't seen these numbers. Maybe they were farther down the list and you didn't know that. And maybe you didn't even know about some of these people having higher compensation amounts. What if you set up a rule here? What if you say, first of all, I'm gonna get rid of the existing rule or put in a new one. I'm gonna say the following. Data Validation, just by clicking the box itself. What if we say from now on? You could say it that way, too, instead of just thinking about new rules.

But how about these all have to be less than 120,000? What data validation does not do is deal with existing data. There's nothing it's going to do with cell L9, where the 125,000 is there. But if we try and change this to 122,000, 123,000. Anything over 120, this will not happen. You've got a rule in place that prevents that. Now there could be others in the list, we could sort the list and maybe find it out that way. But another option here would be to click column L, go to Data Validation, and Circle Invalid Data, which simply means, highlight data that violates the rules.

Now you might saying again, well, wait a minute, don't we have a rule in here? Well, if a rule is changed or if you apply a rule on top of an old rule and it's less restrictive, some of your older data is going to be invalid. Circle Invalid Data. So what we will be seeing here, there might be a pause, we see what's happening now. It picks up data here. It also works throughout the entire worksheet, not just column L. We'll see what's happening. So there could be some others down here. Looks like none of them are over 120,000, but if there were, they'd be highlighted.

Looks like there's only one. And there it is. And we could come back and remove by clicking the drop arrow. And we could come back and click Data Validation, click the drop arrow, and clear the validation circles. Again, it highlighted these because the rule for column I is something to do with dates, we didn't see that rule, so let's check this one out, too. Click column I, go to Data Validation, the date has to be greater than January 1st, 1990. Now, text entries like this violate the rule. When the rule was set up, for ease in setting it up, rather than highlighting all of the cells below it, column I was clicked, as it's often done, so sometimes what you'll want to do is come back and take titles, we could do all of them at once here.

All these titles are violating rules. Highlight them, choose Data Validation, it contains more than one type of data validation, erase current settings and continue, OK. And we'll simply clear all. We're only getting rid of the validation rules for the title rows. It wasn't truly necessary, but now I'm not gonna have a problem with this. So that minor point there about them being highlighted. So different techniques for using data validation, finding where cells have data validation controlling the content of them and changing them where necessary and even allowing us to find out where we have violations of rules that were applied later after the data was put in place.