From the course: Excel Tips Weekly

Use Data Validation rules to prevent duplicate entries in a range - Microsoft Excel Tutorial

From the course: Excel Tips Weekly

Start my 1-month free trial Buy for my team

Use Data Validation rules to prevent duplicate entries in a range

“

- [Instructor] Encountering duplicates and lists sometimes is a big problem. And there are two general approaches to dealing with them. One approach by way of conditional formatting allows you to identify where they exist in existing data. And also, if you were using that feature, conditional formatting while putting in new data, it would certainly highlight information, but it doesn't prevent the bad entries. So as viable as that is, and it's worth a look, even better is data validation, which allows us to not only have a rule to prevent duplicate, but also to control the the length of an entry, these IDs that we're seeing on the worksheet here in column D, let's say need to be four characters, not three, not five, but always four. So we could combine that using data validation rules. The conditional formatting rule that's in place in column G was set up very simply. If you click column G and go to conditional formatting on the home tab, drag over highlight cells, and then down here, duplicate values. And I simply use the default setting here and we can make other choices if we want to use a different format. But nevertheless, that works. And by the way, do keep in mind when we say the word duplicate and we're talking about data, that could mean triplicate, or whatever the other words might mean for even more than one duplicate. So be on the lookout for that concept as well too. But let's focus more on the idea that we don't want to have these, let's say, in a new list of data. So the next worksheet over called data validation, we're about to put in employee IDs here and off to the right are the kinds of things that we might want to include in this feature for people who are doing the data entry. So we're going to select column C, and then on the data tab in the ribbon, off to the right under data tools, there's a button there for data validation. And as you look at the various settings here, that's the tab we want to have active, allow, there's nothing there about duplicate, and there is something about text or text length. So if we were concerned only with the the length and not worried about duplicates, we could make sure that these entries are all four characters, but for duplicates, there's no obvious choice here. So what we need to use is actually custom. And let's just focus on the data validation part of this. Now, you may or may not be familiar with a function called count if. Here's what we'd like to be saying in English. Every time we make an entry in column C, we want to make sure that the number of times that's appearing in column C is once and only once. So we use a function here, and I will make this larger in a minute, and I don't necessarily need to use uppercase, but I will here for clarity, we use a function called count if, and we're looking in column C. Comma. We want to make sure that every entry that we see here appears once and only once. And now I'm going to be entering something that might seem a little strange. I'm putting in the address C1. C1 currently is the active cell. We see it at the top of the screen, even though it contains text. So the county of function, and I'll paste this out on the worksheet momentarily here, is saying every time we see this latest entry, we're going to compare it with the entire list and count how often that appears there. We want to make sure that it's equal to one. Now I'm going to highlight this and copy it, control C, but that will be the rule that's in place here. I'm going to click okay, and normally don't put it on the worksheet, but I will here for sake of clarity. That's the function being used in column C. And even though I used C1, it will refer to every cell in the column. So here's a number, nothing wrong with that, it's the only one there, but here's another number and that's okay. How about this one? That message is probably good enough, although you do have control over the message that pops up as well too. If you click retry, you're in edit mode. If you click cancel, I'll click cancel first. It just wipes out the entry, start over. If I do it again, click retry, just edit. Maybe that should have been 1236. I could get the idea here. So it actually prevents the entry. If we're using conditional formatting, it would highlight it, but it wouldn't prevent the entry. What if we also wanted to make sure that it's always for characters. See, right now I can put in not a problem. Well, that's not what I want and I didn't say earlier, so you understand why this didn't get blocked. We want to make sure that that's only for characters. So what we'll need to do, and I can do it momentarily outside of data validation, then combine it. What we want to say is two things. We want our formula to be two things to be true. And we use the function and. So we still want that to be true, comma, and we want the length. Now there's a function called LEN, first to length, C1 we use again. And again, that seems a little odd because I selected column C, but C1 was the active cell. That's why we use it. Equals four. So two things need to be true. The and function says I've got multiple criteria here. There's one separated by a comma, or it actually begins with this. That's the first one. And here's the second criterion right here, actually all the way there. So this is what we'll use now. So I'm going to copy this and press control C and jump back into column C and then date of validation and change that formula to be the longer formula. Press control V to paste it in. Now, data of validation doesn't do anything with existing data, so that's still there, but let's put in a five digit number. Again, this one, a short one. Doesn't work. So retry or cancel or get out of here. Now we also have control over the messages that can appear here. So I'm going to go back to column C. Click that drop arrow and provide an input message. And so instead of me typing this right now, I'll just copy this information right here. In other words, this here will appear or can appear as a popup. So I'm going to press control C on that, escape. Go back to date of validation. This time, provide an input message, the message that I just copied out of cell G1. You can also give it a title like caution, warning, reminder, something like that. Whatever seems appropriate. You don't have to do that. Click okay. Now what happens? Every time we click here, we see that. You could say that's good enough. Aren't going to be many errors if people see this. And similarly, the error that we see at the end here, we could also use, so this kind of text here could be the error message when an entry is incorrect. Control C there, back to column C, back to data validation. This time the error alert, the error message control V. People, by the way, sometimes play around with this and put in crazy things here, but I'm going to put in, for example, word wrong, that looks, that's overkill for sure. But anyway, click okay. Now what happens here, if we type in an entry like this, or anyone that's a duplicate, you'd get that kind of a message. So you can imagine playing around with that a little bit too. Got control over this. The main idea is to, let's say keep out the duplicates, but if you also want control of the text length, you can combine that as well too. So data validation is a powerful tool. Keeping out the bad data before it ever goes anywhere with this list.

Contents