Search Not Just Numbers


Tuesday, 1 April 2014

Excel Tip: Identifying and removing duplicates

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.

Highlighting Duplicates

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).

Removing Duplicates

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:

First of all, check that the tick-box, My data has headers, is correctly ticked or unticked.

In the main window of the dialog box, all of the columns you have selected will be shown. and you can tick which ones must be duplicated to consider the entry a duplicate row.

In this example, with all columns ticked, the rows must be exactly the same before they are deleted. However, if we unticked the value column, then any rows with a duplicate code would be considered duplicates (irrespective of the entry in the Value column).

It is always the first row that is kept, and all subsequent duplicates deleted. This is only relevant if you have not ticked all of the columns, otherwise all of the duplicate rows are the same anyway.

Excel Expert Course

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".


  1. Most people believe themselves to be overworked replica wathces and underpayed. In the fictional universes discussed on this site, it can sometimes rolex replica be difficult to remember that beyond the costumed heroes or mystic villains, there are thousands of working stiffs that just gucci replica want to make it to the next paycheck. This is our list of the fictional companies that would, if not be great chanel replica to work for, would at least make things more replica handbags interesting.

  2. One more frame of mind we've inside purchasing on the web will be that individuals need almost everything to be effective thus without headaches. fake hermes On the web purchasing will be popular for your draw series “you are usually a single simply click apart, ” yet together with countless purchasing web site engines like google will provide you with, that might be a great understatement. rolex replica uk Buckskin clutches have got turn into a smaller high-class bit now-a-days along with are getting to be virtually any popular emphasize for girls, in which conform to a solid earlier fine-tuning trend craze. prada replica With a greater dependence on buckskin clutches, manufacturers contain to enable you to develop impressive along with interesting goods fulfill the specifications with the genuine craze. fake chanel Therefore wondering just what their particular items are likely planning to become related with. cartier replica uk By way of example people accessorise buckskin clutches together with matches along with smart-wear so that you can contain a great ambiance of professionalism and reliability.