How to identify duplicates in Microsoft Excel

Quite often, when using Microsoft Excel, you need to be able to find and isolate duplicate values very quickly. Doing it (as most things Excel) is fairly easy; the hard bit is remembering how. Today we present a very simple way of identifying duplicates in Excel, with just a few clicks of the mouse. Please note that this tip works only with Excel 2007 and later versions.

In the example below, we are trying to understand how many invoices, last month, included more than one order – i.e. generated more than one sale.

1.    First of all, select the range of cells that are of interest. You can do that by clicking on a single cell and pressing Ctrl-A.

2.    From the Home tab select Conditional Formatting, Highlight Cells Rules, and finally Duplicate Values.

3.    Click OK within the Duplicate Values dialog box to identify the duplicate values.

 

4.     Duplicate values in the list will now be identified.

Great, we have identified the duplicates. Time to isolate them:

5.    Right-click on a duplicate cell, select Filter, and Filter by Selected Cell’s Color.

6.    The duplicate values will be presented to you, in one list only. Copy and paste it onto another worksheet, or just use it as you reckon best.

To remove the conditional formatting, you can click the Conditional Formatting button, choose Clear Rules, and then Clear Rules from Entire Worksheet.

If you’re using Excel 2003, you won’t be able to filter by color. However you can isolate duplicate values using the COUNTIF worksheet function. COUNTIF has two arguments:
•    Range: This is a range of two or more cells that you wish to test.
•    Criteria: The value that you’re seeking within the range.
In a future post we’ll look at this functionality of Excel more closely.

Excel online training

You can access similar Excel tips through the Filtered Microsoft Excel training. By asking carefully selected questions upfront, our online course only teaches the features of Excel that are useful to you, saving you precious time and providing a truly tailored learning experience.

Follow these links to read more about Filtered training or the Excel course syllabus. You can also see our top tips on how to use Pivot tables here.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.

Related articles