ExcelFunctions.net

Search Site:

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 |

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.

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:

- Select the range of cells to be formatted (cells A2 - A10 in the example spreadsheet above);
Select the Excel Conditional Formatting drop-down menu from the Home tab at the top of your Excel workbook.

Within this menu:

- Select the
option and from the secondary menu that appears, select the__H__ighlight Cells Rulesoption;__D__uplicate Values... A 'Duplicate Values' dialog box will pop up. The drop down menu in the left hand side of this window should show the value 'Duplicate' (although this can be changed to show unique values only, instead of duplicates).

- Click
**OK**.

- Select the

The resulting spreadsheet is shown below.

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.

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.

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.