Whenever you are working with large data sets, you will inevitably have to search for duplicates and possibly remove duplicates. So in this screencast, I'm going to go over how you can identify duplicates and how you can remove them. So I've got this file called Duplicates. I'm going to start with kind of an easy example here. I've just got a bunch of animals here and we want to check to see if there are duplicates, if there are duplicates, we would like to remove them. If you just want to check to see if there are duplicates in a set of data, you can highlight that, you can use conditional formatting. So I'm selecting this block of cells, go up here to Conditional Formatting, we go to Highlight Cell Rules and then you can just select Duplicate Values. You can not only choose duplicates here, but I could search for Unique values. So this kind of the converse of one another but I'm going to go ahead and identify the duplicates. So then you would know that these highlighted cells are duplicates and you might go through and delete them. So let's go ahead and do Ctrl+Z to get back to our original data. If you wanted Excel to automatically remove the duplicates in this list, you could go here and we can highlight that and I can go up here in the data tab, remove duplicates. There's this little tool here called Remove Duplicates so I can click on that command button. And it brings up this Remove Duplicates tool. I only have one column of data. I'll show you here in a minute where we might have multiple columns of data. So really, if I want to remove duplicates here, all I do is make sure that column B is selected and then I can click OK, and it says three duplicate values were found and removed 11 unique values remain, that's how I can permanently remove the duplicates. Before you remove duplicate, it's always a good idea to go back and just make a copy. So you could just copy this entire sheet and paste onto a new sheet, just in case you remove something and later on you realize that it was an error. The second sheet down here in this file, I've just got a bunch of numbers here. Again, if I wanted to just identify duplicates, I could go to Home, Conditional Formatting, Highlight Cell Rules, Duplicate Values and it's going to identify duplicate values alternatively, I can identify unique values. So I'm just going to cancel. Now, I cannot use data remove duplicates because remove duplicates, it searches through columns. So it deletes duplicate rows from a sheet. So unfortunately, I cannot remove duplicates this way. If you did have duplicates in a chunk of information here, the easiest way is just to identify those duplicates using conditional formatting and then go through and manually remove them. I've got another example here, maybe we have orders by various people, we have their names, their categories and catalog numbers and we wanted to check for duplicates. Now you notice we have a couple of opportunities for duplicates. We could have duplicate names of which we have several. We could have duplicate categories here and we could potentially have duplicate catalog numbers. So I'm going to go up here, you don't have to have the entire array selected. I can go up here to remove duplicates. And it's going to by default, select that, if you have data headers, Excel does a good job of detecting those, otherwise, you could remove that but I do have data headers. And you can check down here what you would like Excel to search through in order to look for duplicates. If you have all three selected, that means it's going to be all three have to be an exact match in order for it to be a duplicate. You see here in my data, I have Heather, Backpacks, I have that three times. The first two though are unique, we have this catalog number here and a different catalog number there. So if I remove duplicates with all three columns selected, it's not going to detect the first and fifth rows as being duplicates because all three of them are not the same but what it will do is the fifth row and the second to last row, those are exactly identical. So I click OK and it said we found one duplicate removed, 11 unique values remain. Let me actually go back Ctrl+Z and I'm going to show you now what happens if we do remove duplicates, but we deselect one of these, so let's deselect catalog number. So now all it's going to do is you have to have exact matches for name and category but not catalog number in this case. All of them are unique except for Heather's backpack orders. So when I click OK, it's going to remove those two repeats of Heather backpacks. And it says here 2 duplicate values were found. I'm going to go ahead and do Ctrl+Z to get us back to the original data. So now let's see what happens if I just filter by name and you see that there are lots of these that have duplicate names in the name column. So when I do that, we remove all six, Ctrl+Z. We can also look for just the category. So maybe there can only be one of those categories and I can do that. And finally, I could look through for just the catalog number and we're going to remove those duplicates and there's only one of those. This is how you can have Excel go through a chunk of data to remove duplicates. And again, it's always a good idea to copy the original data set and store that on a different sheet. You can do Ctrl+Z, but sometimes you've messed up your spreadsheet so much that it's really unfeasible to go back and it's always good to have the original data. One more thing, you can always convert this into a table so I can go up here to Format as Table and up here in the left on Table Design, there's a Remove Duplicates and that brings up the same exact tool that we used with just the information that I had before, I converted it to a table. So hopefully, this screencast gave you a better idea of how to identify duplicates and remove duplicates in Excel.
