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 Duplicate Cells in Excel |
| Find Duplicate Rows in Excel |
| Remove Duplicate Rows in Excel |
Find Duplicate Cells 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.
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
- Select the range of cells to be formatted.
-
Select the Conditional Formatting drop-down menu from the Home tab at the top of your Excel workbook.
Within this menu:
- Select the Highlight Cells Rules option and from the secondary menu that appears, select the Duplicate Values... option;
-
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.
Formatting cells A2-A10 of the example spreadsheet in this way, produces the following result:
Find Duplicates Using Countif
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:
=COUNTIF( A:A, A2 )
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.
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.