Format based on unique or duplicate values

- [Instructor] On this worksheet called "Duplicate or Unique Entries" in our chapter one file, we've got two different lists, one in columns A through H, and then off the the right a narrower list, although quite deep, columns J through L. We'd like to know if there any duplicates, any unintended duplicates for example with the Social Security numbers in column B. So selecting column B, we can use Conditional Formatting, Highlight Cell Rules, Duplicate Values, and immediately we see something's going on, and something that needs to be corrected. Two different people have the same Social Security number. Once again, we can go with the automatic formatting that's here. Notice how it says Duplicate, click OK, and we'll probably either sort the data or scroll up and down, find if there are any more. Keep in mind there might be more than just two of them alike. There could be three or four, hopefully not but this brings it out beautifully. We've got to make the corrections ourselves though. There's also an option called Unique, but sometimes I'm going to almost challenge you to find out where it is. Here's another list off to the right. This is quite a bit bigger in terms of its depth, it's over a thousand rows. We're tracking a bunch of transaction numbers. We're simply interested in which state they're coming from, but here the issue is are we getting more than one entry from each state. Probably are because that's a lot of entries there, but just to be sure let's check and see if there are any unique entries here. So selecting column L, we go to Conditional Formatting, Highlight Cell Rules. Now there's Duplicate, and of course most of them are duplicates. But buried over here, it's easily overlooked, is a drop arrow next to Duplicate. Unique and immediately we see something off to the right there. It looks like Wyoming is going to be there once, and perhaps another one down there that we can't quite see. So I click OK here, and there's Alaska and perhaps some others. It's a big list, we'll find them eventually. By the way, you can sort the list on the basis of color, so if we were to select this data here and go to the Data tab and choose Sort, we can sort based on the data in column L, that's the state. And we can sort this on the basis of in this case either cell color or font color, and the cell color right here, this pink background. Click OK, and there we see them all clustered together. And keep in mind the way this works is if it's unique it's highlighted. If it's not, maybe this entry here says Maine should have been Vermont, I'll type in Vermont. And of course Vermont will no longer be unique because it's there more than once. So I think you can see how both features have their uses. It doesn't correct data, but it certainly brings out the fact that we have either duplicates or unique entries within a list.