How to Find Duplicates in Excel with Multiple Columns?

Written By Tej Pratap Shukla

Approved By Anuraag Singh

Modified On May 2nd, 2026

Reading Time 10 Minutes Reading

Quick Summary: How to Find Duplicates in Excel with Multiple Columns? If you want to quickly find duplicates and want to eliminate you simply need to use these methods:

Use COUNTIFS Formula: This is one of the easy ways to check duplicate conditions across more than 2 columns in one formula.
Helper Column + COUNTIF: Concatenate columns to create a string and then count them.
Conditional Formatting with using a custom formula: This will visually highlight your full duplicate rows.
Use Power Query: This is much ideal for large datasets (10,000 rows) this is non destructive and refreshable.
Professional Tool: Best and reliable solution as there is built multi feature for instantly deleting multi column duplicates.

If you work with CSV files and need a reliable and secure solution without affecting your data SysTools solutions is a trusted option that comes with multiple advanced feature allow you to select specific columns and clean all duplicate items in just a few clicks, no complex excel formulas needed.

Table of Contents Hide

Why Multi Column Duplicate Detection Matters

Duplicate data is one of the most common problems that silently damages the quality of your spreadsheets as a single duplicate row in a sales report can inflate your revenue numbers. Similarly a duplicate contact in a CRM export can trigger two emails to the same person and a duplicate transaction ID can throw off your entire reconciliation.

So the problem gets trickier when the duplicates are not in a single column. Just imagine what would happen if a customer appeared twice because their first name, last name, and email address all match rather than just one of these fields. This is exactly the challenge of multi column duplicates.

This guide is a perfect solution if you are looking for How to Find Duplicates in Excel with Multiple Columns. We cover every methods from beginner friendly to advanced Power Query workflows.

Understand Multi Column Duplicates in Excel

Before we jump into any solution it is equally important for you to understand what exactly multi column duplicates means in excel.

Type	Description	Example
Single column duplicate	Same value appears more than once in one column	Email column: [email protected] appears twice
Multi column duplicate (row match)	Your entire row of data is repeated across two or more columns	First Name, Last Name and Email all match
Partial duplicate	Some columns match but not all	Same Name but different Email
Case sensitive duplicate	You will see there are same text but different capitalisation	“JOHN” vs “john” in the same column

Manual Methods to Find Duplicates in Excel with Multiple Columns

Lets explore all the proven manual methods which you can use and find duplicacy in spreadsheets. We will start with basic beginner friendly methods first and then move to advanced power query.

Method 1: Use COUNTIFS to Find Duplicates Across Multiple Columns

The COUNTIFS function is the most direct formula based way to detect multi column duplicates as it counts rows where all specified column values match simultaneously and giving you a count greater than 1 for any duplicate.

When to Use COUNTIFS

You need to check 2 to 5 columns for duplicate combinations.
If You want to flag duplicates with a label (“Duplicate”/”Unique“) without modifying your data.
And if your dataset has up to 50,000 rows.

Step to use COUNTIFS Duplicate Formula

Use COUNTIFS to Find Duplicates Across Multiple Columns

Suppose you have a customer list with First Name (Column A), Last Name (Column B), and Email (Column C) and you want to flag rows where all three columns together form a duplicate.

Click on cell D2 (the first blank column next to your data).

You simply need to Enter this formula:

=IF(COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2,$C$2:$C$1000,C2)>1,"Duplicate","Unique")

Now drag the formula down through all your data rows.

Filter Column D by “Duplicate” to isolate all matching rows.

Pro Tip: You should use an expanding range ($A$2:A2) Therefore the first entry is marked as “Unique” and only the subsequent repeated entries are flagged as duplicates. In this way this formula checks the data in a sequence rather than the entire list.

Method 2: Helper Column + CONCATENATE/TEXTJOIN

This approach works by simply combining the values of multiple columns into a single string in a helper column then you can use COUNTIF on that combined string. It is more flexible than COUNTIFS and works well in older Excel versions (2010 and above).

Step to Follow for Helper Column Method

You need to Insert a new column (e.g., Column E) and label it “Combined Key“.
In cell E2 you need to enter one of these formulas:

For Excel 2019 / Microsoft 365:

=TEXTJOIN("|",TRUE,A2,B2,C2)

For Excel 2016 or older:

=A2&"|"&B2&"|"&C2

In Column F (“Duplicate Count“) and enter:

=COUNTIF($E$2:$E$1000,E2)

Any row with a count > 1 is a duplicate across those columns.

Why use a separator like “|”?
If you are using a separator it will prevent accidental false matches. Without it “John” + “son” could match “Johns” + “on”. Let me tell you the pipe character | is rarely used in data making it a safe separator.

Method 3: Conditional Formatting to Highlight Duplicate Rows

If you want a visual solution the colour coding duplicate rows directly in your spreadsheet Conditional Formatting is your answer. This method does not require any helper column and highlights the rows in place.

Step to Highlight Duplicate Rows with Conditional Formatting

You need to select your entire data range (e.g., A2:C500) – excluding headers.
Go to Home > Conditional Formatting > New Rule.
Select “Use a formula to determine which cells to format”.
And enter this formula (adjust column references to match your data):

=COUNTIFS($A$2:$A$500,$A2,$B$2:$B$500,$B2,$C$2:$C$500,$C2)>1

Now Click Format and choose a fill colour (e.g., light red) thereafter click OK.

Important Note

The default Conditional Formatting > Duplicate Values option only checks ONE column at a time. If you want multi column duplicate detection then you must use the “Use a formula” option shown above.

Method 4: Excel’s Built in Tool

Excel has a built-in duplicacy removal feature that supports multiple columns. This is the fastest method when you simply want to delete duplicate rows but it is destructive so you should always make a backup first.

Step to Remove Duplicates Across Multiple Columns

Click anywhere inside your data range.
Now Go to the Data tab > Remove Duplicates.
In the dialog box you need to check the boxes next to the columns you want to use as the duplicate detection criteria.
And click OK. Excel will remove all but the first occurrence of each duplicate combination.

Method 5: Use of Power Query

This is best for large datasets as Power Query is the most powerful and scalable method to find duplicates in Excel with multiple columns. It is non destructive, reusable and easily handles datasets of more than 100,000 rows without slowing down.

When to Use Power Query

If your dataset has more than 10,000 rows.
When you need to run the same duplicate check regularly on refreshed data.
If you want to compare duplicates across two separate worksheets or files.
When having case sensitive duplicate detection.

Step to Find Duplicate Rows with Power Query

Click inside your data range, then go to Data > From Table/Range.
In the Power Query Editor you need to select the columns you want to check (hold Ctrl and click each column header).
Now Go to Home > Keep Rows > Keep Duplicates.
Click close and load to send the duplicate rows to a new worksheet.
To refresh the results after data changes you simply need to right click the output table and click Refresh.

Power Query Case Sensitivity Warning
Unlike Excel formulas the Power Query IS is case sensitive by default. “ABC” and “abc” will be treated as different values. So if you want case insensitive matching you need to add a Transform > Format > lowercase step to your columns before running the duplicate check.

Method 6: VBA Macro for Automated Multi Column Duplicate Detection

This method is useful for advanced users or those who need to run the same duplicate check repeatedly on different files. Here is a simple macro that flags duplicate rows in a helper column based on three columns.

Sub FindMultiColDuplicates()

Dim ws As Worksheet: Set ws = ActiveSheet

Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

Dim i As Long, key As String

ws.Cells(1, 5).Value = "Duplicate Check"

For i = 2 To lastRow

key = ws.Cells(i,1) & "|" & ws.Cells(i,2) & "|" & ws.Cells(i,3)

If dict.Exists(key) Then

ws.Cells(i, 5).Value = "Duplicate"

Else

dict.Add key, i

ws.Cells(i, 5).Value = "Unique"

End If

Next i

MsgBox "Duplicate check complete!"

End Sub

To run this macro you simply need to Press Alt + F11 to open the VBA editor and insert a new module, paste the code and now press F5 to run. You can adjust the column numbers (1, 2, 3) and range to match your actual data.

Advanced and Reliable Automated Solution to Find Duplicate Dataset

If you are still struggling with your CSV Files this is the time to download and install the best and safest tool SysTools CSV Duplicate Remover Software to find and remove duplicate items from your csv files easily without any hassle. The tool is highly capable of handling:

Download Now Purchase Now

Very large CSV files (more than 100,000 rows easily)
Allows to process Multiple CSV files at once
Useful for Teams that do not have Excel installed or prefer a no-formula solution
You can choose column specific matching to find and eliminate from selected column
Compatible with both Windows and Mac OS

Frequently Asked Questions

Q1. How do I find duplicates in Excel with multiple columns?

You should use the COUNTIFS formula in a helper column that will simply flag duplicates as “Duplicate” and “Unique” without deleting your data. After this you can use a filter to view all duplicate items using a helper column. You can also use Conditional Formatting to easily highlight them.

Q2. How to find duplicate rows in Excel based on multiple columns using a formula?

The best formula is: =IF(COUNTIFS($A$2:$A$500,A2,$B$2:$B$500,B2,$C$2:$C$500,C2)>1,”Duplicate“,”Unique“). You need to enter this formula in an adjacent column and then drag it down. This formula will display you all “Duplicate” whenever the same combination of values in columns A, B, and C appears more than once.

Q3. How to highlight duplicate rows in Excel based on two or more columns?

To highlight you simply need to select your data range now go to Home > Conditional Formatting > New Rule > Use a formula. Just enter: =COUNTIFS($A$2:$A$500,$A2,$B$2:$B$500,$B2)>1. Choose a fill colour and click OK. This highlights every row where both Column A and Column B match another row.

Conclusion

Knowing how to find duplicates in Excel with multiple columns is a fundamental data management skill. Whether you are cleaning your customer list, auditing your financial records or maintaining a product database so if there is a duplicate rows in your datasheet well it can cause real problems but if you use best approaches as suggested in this article you can easily find and fix all duplicate items and clean your dataset easily.