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:
|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.
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 Excel Conditional Formatting drop-down menu from the Home tab at the top of your Excel workbook.
Within this menu:
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:
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.
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.