A common problem in processing data in Excel is handling duplicates or double entries. Excel has a few convenient tools for quickly making these changes.
Simply eliminating duplicate entries in Excel
After highlighting your chart of data, click on the Data tab and choose Remove Duplicates in the Data Tools group.
The Remove Duplicates pop up window will be presented. First, check to see that the My data has headers box is checked. If you do have headers in your chart, then the header names should appear in the column section below. If not, then uncheck the box, and they should be labeled Column A, Column B, Column C, etc.
Excel will identify duplicates that match all of the data entered. If you are only checking for duplicates in one column, and the contents of the other columns won’t matter, then make sure only that column header is checked. If you need to eliminate entries that are duplicated in 2 columns and the other columns won’t matter, just select those two. If everything needs to match, then make sure everything is checked.
Once you’ve chosen your columns, click ok. The duplicate entries will be deleted.
Manually sorting through duplicate entries in Excel
In some cases, the data in the non-duplicated columns is important, and you might want to manually select which entry should be deleted. Excel makes this easy too.
The first step will be to highlight all of the cells that are duplicated.
Highlighting duplicate entries in Excel
First, Select the column or columns that contain duplicate entries be clicking on the letter at the top of the columns. Then, under the Home tab choose Conditional Formatting and Highlight Cells Rules and Duplicate Values. Choose one of the rules that includes a light red fill.
Sort by cell color in Excel
The next step will be to get all of the duplicate values to the top. If you’ve turned on filter then you can click on the drop down arrow by the header, choose Sort by color, and select light red or any other color you chose in the previous step.
If filtering isn’t enabled, you’ll need to use custom sort. First highlight your data, then choose Sort from the Data tab. For Column, choose your column header, then change Sort on to Cell Color and for Order make Red go to the top.
Either of these methods will bring the duplicate entries to the top. You might also want to add a sorting level that alphabetizes them first, since you’ll want each duplicate to be right next to each other.
