How to Find Duplicates in Excel (PivotTable + COUNTIFS Tutorial)
Duplicate records can cause serious problems when uploading spreadsheet data into other systems. If you need to find duplicates in Excel, there are several simple tools that can help you identify repeated rows or duplicate record combinations quickly. In this tutorial, you’ll learn two easy methods to find duplicates in Excel using PivotTables and the COUNTIFS formula, so you can clean and validate your spreadsheet data before importing it into another system..
In this tutorial, you’ll learn two easy ways to find duplicates in Excel:
Using a PivotTable to summarize duplicate combinations
Using a COUNTIFS formula to identify the exact duplicate rows
Example Scenario
In this example, we have a spreadsheet containing:
Employee ID
Skill
Each employee should only have one record for each skill.
If a duplicate record appears (for example an employee with the same skill listed twice), we want to identify and remove it before uploading the data into a system.
Method 1: Find Duplicates in Excel Using a PivotTable
Using a PivotTable is one of the fastest ways to identify duplicate combinations of values.
Step 1: Insert a PivotTable
Select your dataset.
Go to the Insert tab.
Click PivotTable.
Choose New Worksheet and click OK.
Step 2: Build the PivotTable
Now we’ll configure the PivotTable to count records.
Drag Employee ID into the Rows area.
Drag Skill below Employee ID in the Rows area.
Drag Employee ID into the Values area.
Excel may default to Sum. If so:
Click the dropdown next to the field in Values.
Select Value Field Settings.
Choose Count.
The PivotTable now shows how many times each Employee ID + Skill combination appears.
Step 3: Identify Duplicate Records
If the count equals 1, the record is unique.
If the count is greater than 1, that means a duplicate record exists.
To make duplicates easier to see:
Select the Count column.
Go to Home → Conditional Formatting.
Choose Highlight Cells Rules → Greater Than.
Enter 1.
Excel will now highlight any duplicate records.
Method 2: Identify Duplicate Rows in Excel with COUNTIFS
While the PivotTable method summarizes duplicates, the COUNTIFS formula can identify the exact duplicate rows in your spreadsheet.
Step 1: Insert a Helper Column
Add a new column next to your dataset called:
Duplicate Check
Step 2: Enter the COUNTIFS Formula
In the first data row, enter:
=COUNTIFS(A:A,A2,B:B,B2)This formula counts how many times the combination of:
Employee ID
Skill
appears in the dataset.
Step 3: Copy the Formula Down
Use the fill handle to copy the formula down the column.
If the result is:
1 → Unique record
2 or more → Duplicate record
Now you can filter the column to quickly locate duplicate rows.
Removing Duplicate Records
Once duplicates are identified, you can:
delete the extra rows manually
or use Excel’s Remove Duplicates tool
To remove duplicates automatically:
Select your data.
Go to the Data tab.
Click Remove Duplicates.
Select the columns that define the unique record (Employee ID and Skill).
Excel will remove duplicate combinations.
Why This Step Matters Before Uploading Data
Checking for duplicates is an important data cleanup step before importing Excel data into another system.
It helps prevent:
duplicate employee records
incorrect reporting results
system upload errors
data integrity issues
Running a quick duplicate check can save a lot of time fixing data problems later.
Related Excel Tutorials
If you're preparing Excel data for system uploads, you may also find these tutorials helpful:
How to Restart Sequential Numbering in Excel - Video Tutorial
Excel Data Cleanup Checklist Before Importing Data
Final Thoughts
Finding duplicate records in Excel is a simple but important step when preparing spreadsheet data for uploads or system imports.
Using a PivotTable gives you a quick summary of duplicate combinations, while COUNTIFS helps you identify the exact duplicate rows.
Together, these methods make it much easier to clean and validate your Excel data before importing it into another system.
