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 Duplicate Cells in Excel|
|Find Duplicate Rows in Excel|
|Remove Duplicate Rows in Excel|
In order to illustrate how to find duplicate cells in Excel, we use the above simple spreadsheet on the right, which has a list of names in column A.
Select the Conditional Formatting drop-down menu from the Home tab at the top of your Excel workbook.
Within this menu:
Formatting cells A2-A10 of the example spreadsheet in this way, produces the following result:
Warning: This method will only work if the contents of your cells are less than 256 characters in length, as Excel functions cannot handle text strings that are longer than this.
In order to illustrate how to use the Countif Function to find duplicates in Excel, we will use 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 entered the Countif function into column B of the spreadsheet, to show the number of occurrences of each name. As shown in the formula bar, the Countif function used in cell B2 is:
This function counts the number of occurrences of the value in cell A2 (the name "Ann SMITH") within column A of the spreadsheet.
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.