Find Duplicates In Excel

This page describes two simple methods of finding and highlighting duplicate cells in an Excel spreadsheet. If you want to remove duplicate cells or find duplicate rows of data, see one of the following pages:

Related Pages:
Remove Duplicates in Excel
Find Duplicate Rows in Excel
Remove Duplicates Rows in Excel

Find Duplicate Cells in Excel

Excel Spreadsheet with Duplicate Values

In order to illustrate how to find duplicates in Excel, we use the above simple spreadsheet on the right, which has a list of names in column A.

We first show how to use Conditional Formatting to highlight the duplicate cells and we then show how to use the Excel Countif Function to find the duplicates.


Highlight Duplicate Cells Using Conditional Formatting

If you have a recent version of Excel (2007 or later) the easiest way to highlight duplicate cells is to use Conditional Formatting.

To do this:

Excel Conditional Formatting Duplicate Values Option

The resulting spreadsheet is shown below.

Highlight Excel Duplicate Cells Using Conditional Formatting


Find Duplicates Using Countif

Another way to find duplicates in a range of Excel cells is to use the Excel Countif Function.

Warning: This method will only work if the contents of your cells are less than 256 characters in length, as text values used in Excel functions are limited to 255 characters.

Highlight Excel Duplicate Cells Using Countif

This is illustrated in the above example spreadsheet on the right, which has a list of names populating Column A.

In order to find any duplicates in the list of names, we have used the Countif function in column B of the spreadsheet, to show the number of occurrences of each name. As shown in the formula bar, the format of the Countif function in cell B2 is:

=COUNTIF( A$2:A$10, A2 )

This function counts the number of occurrences of the value in cell A2 (the name "Ann SMITH") throughout the range A2 - A10.

As the Countif function is copied down column B of the spreadsheet, it will count the number of occurrences of the names in cells A3, A4, etc.

It is seen that the Countif function returns the value 1 for most rows, showing that there is just one occurrence of the names in cells A2, A3, etc. However, when it comes to the name "Laura CARTER", which is present in cells A4 and A7, the function returns the value 2, showing that there are two occurrences of this name.

Absolute and Relative References in the Countif Function

Note that we have used a combination of absolute and relative cell references in the Countif function above.

The range A$2:A$10 is an absolute cell reference. The dollar symbols ensure that this reference remains constant as the Countif function is copied down column B of the spreadsheet.

However, the second argument, A2, remains a relative cell reference, so will update to the references A3, A4, etc, as the function is copied down column B.

Note that you could use a combination of Absolute and Relative references to highlight only the duplicate cells in column A and not the first occurrence of a name. An example of this is provided in the Remove Duplicates in Excel page.