How to Find Duplicates in Excel

Excel offers several techniques for discovering duplicate entries in your spreadsheet. These duplicates can result in inaccurate data, counting errors, and confusion. Microsoft Excel provides Conditional Formatting, the COUNTIF function, and the Remove Duplicates feature to help spot repeated values.

To use Conditional Formatting, open your Excel spreadsheet and choose the column or range of cells you wish to examine. Navigate to the Home tab, select Conditional Formatting, and then Highlight Cells Rules, followed by Duplicate Values. Choose a formatting color and click OK. Excel will automatically highlight the duplicate entries within the selected range, making them easy to spot.

The COUNTIF function is another useful method, particularly when dealing with larger datasets. For instance, if your names are in column A starting from cell A2, input the formula "=COUNTIF(A:A,A2)" in cell B2. Press Enter and drag the formula down the column. The outcome displays how many times each name appears in column A. Any result greater than 1 indicates that the value appears more than once. Additionally, you can modify the formula to label duplicate entries as "Duplicate" or "Unique," depending on the count.

In some cases, you may need to find duplicates that span across multiple columns, such as a person's name and phone number. Suppose you have names in column A and phone numbers in column B. Enter the formula "=COUNTIFS(A:A,A2,B:B,B2)" in cell C2 and copy it down the column. A result greater than 1 signifies that the combination of the name and phone number repeats multiple times. This approach is particularly helpful when different individuals share the same name.

To permanently remove duplicate records, select the data range or table, click the Data tab, and choose Remove Duplicates. Select the columns Excel should use to identify duplicate records, then click OK. Excel will inform you of how many duplicate values or records have been removed and how many unique records remain. Remember to create a copy of your spreadsheet before removing duplicates, as this action cannot be undone.

If you simply want to review duplicate entries without eliminating them, employ Conditional Formatting or a formula rather than the Remove Duplicates feature. This allows you to examine the repeated records before deciding on their fate. For example, duplicate customer names might represent separate individuals, while repeated invoice numbers could indicate a data-entry mistake. Always verify that an entry is genuinely duplicated before deleting it.