Learning how to find duplicates in Excel can help you identify repeated names, numbers, email addresses, and other entries in your spreadsheet. Duplicate data can lead to inaccurate reports, counting errors, and confusion when managing records. Fortunately, Microsoft Excel provides several tools for finding duplicate values, including Conditional Formatting, the COUNTIF function, and the Remove Duplicates feature.
-
Find Duplicates Using Conditional Formatting
Conditional Formatting is one of the easiest ways to identify duplicate values in Excel because it highlights repeated entries automatically.
- Open your Excel spreadsheet.
- Select the column or range of cells you want to check.
- Click the Home tab.
- Select Conditional Formatting.
- Choose Highlight Cells Rules, then Duplicate Values.
- Select a formatting colour and click OK.
Excel will highlight duplicate values within the selected range, making them easier to identify.
-
Find Duplicates Using the COUNTIF Function
The COUNTIF function helps you identify how many times a value appears in a range. This method is useful when working with large spreadsheets.
For example, if your names are in column A, starting at cell A2, enter the following formula in cell B2:
=COUNTIF(A:A,A2)
Press Enter and copy the formula down the column.
The results show how many times each name appears in column A. A result greater than 1 means the value occurs more than once.
You can also use this formula to label duplicate entries:
=IF(COUNTIF(A:A,A2)>1,”Duplicate”,”Unique”)
This formula displays Duplicate for repeated values and Unique for values that appear only once.
-
Find Duplicates Across Multiple Columns
Sometimes, you need to identify duplicate records based on more than one column, such as a person’s name and phone number.
Suppose names are in column A and phone numbers are in column B. Enter this formula in cell C2:
=COUNTIFS(A:A,A2,B:B,B2)
Copy the formula down the column.
A result greater than 1 indicates that the combination of the name and phone number appears multiple times. This method is helpful when different people share the same name.
-
Find and Remove Duplicates Using Excel Tools
Excel also allows you to remove duplicate records when you no longer need repeated entries.
- Select the data range or table.
- Click the Data tab.
- Select Remove Duplicates.
- Choose the columns Excel should use to identify duplicate records.
- Click OK.
Excel will report how many duplicate values or records were removed and how many unique records remain. Make a copy of your spreadsheet first because this action permanently removes entries from the selected data.
-
Identify Duplicates Without Deleting Them
If you only want to review duplicate entries, use Conditional Formatting or a formula instead of Remove Duplicates. This allows you to investigate repeated records before deciding what to do with them.
For example, duplicate customer names may represent separate people, while repeated invoice numbers may indicate a data-entry problem. Always confirm that an entry is genuinely duplicated before deleting it.
Also Read: How to Attract Your Crush
Email your news TIPS to Editor@Kahawatungu.com — this is our only official communication channel
