From the Not Just Numbers blog:
One of the biggest challenges when dealing with lists of data is spotting and/or removing duplicate entries.
Fortunately, the more recent versions of Excel offer some excellent tools for handling this problem.
I thought that this week, I would give you a quick introduction to them.
Excel offers a quick facility to highlight duplicate entries in a list.
Simply highlight the list and, from the Home ribbon, select Conditional Formatting, Highlight Cells Rules, Duplicate Values.
You then get two drop-down boxes to choose whether you want to highlight the duplicates or the unique values and what colours you want to highlight them in. It’s as simple as that.
This will simply highlight all of the cells that have a duplicate (or don’t, if you select unique values).
Often, you don’t need to see the duplicates, just get rid of them. Excel also has a tool for that, which is also a little more sophisticated than the previous tool.
It doesn’t just look at duplicate cells, but duplicate rows.
To use the tool, highlight all of the columns containing your data (data should be formatted correctly as covered in my earlier post on this subject, or at least with a column for each field and a row for each record).
On the Data ribbon, select Remove Duplicates and you will see the following dialog box:
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.