How to Find Duplicates in Excel: The Ultimate Step-by-Step Guide

Step-by-Step Instructions

Why use this method?

It is non-destructive. You can see the duplicates, investigate why they are there, and decide manually whether to keep or delete them.

How to use it

1. Click anywhere inside your data table.

   2. Go to the Data tab on the Ribbon.

3. In the Data Tools group, click Remove Duplicates.

4. Select Columns: A window will pop up asking which columns to check.

  * For example, if you wanted to delete repeated emails, you would select only "Email," Excel will delete any row where the email is repeated.

* If you select all columns, Excel will only delete a row if _every single cell_ in that row is an exact match for another row.

5. Click OK. Excel will tell you how many duplicate values were found and removed. It will also tell you how many unique values remain.

Pro Tip: Always copy your data to a new sheet before using this tool. Once the duplicates are removed and the file is saved, you cannot "un-delete" them easily if you realize later that some were legitimate entries.

The Logic

The COUNTIF function counts how many times a specific value appears in a range. If that count is greater than 1, you have a duplicate.

How to Implement

=COUNTIF(B:B, B2)

=COUNTIF(B:B, B2)

This appears to be an Excel formula rather than C# code. If you have C# code that interacts with Excel or a specific C# snippet you'd like converted, please provide that code for conversion to VB.NET.

Advanced Formula: Highlighting Only the Second Occurrence

If you want to keep the first instance of a name but highlight any subsequent times it appears, use a "expanding range" formula:

=COUNTIF($A$2:A2, A2) > 1

=COUNTIF($A$2:A2, A2) > 1

By locking the first part of the range ($A$2) but leaving the second part relative (A2), the formula checks "from the top down to my current row." This is the best way to flag duplicates for deletion while leaving the original data untouched.

The Power Query Workflow

1. Select your data and go to the Data tab.

2. Click From Table/Range (this will turn your data into an official Excel Table).

3. Once the Power Query Editor opens, right-click the column header you want to check.

4. Select Keep Duplicates (to see only the errors) or Remove Duplicates (to clean the list).

5. Click Close & Load to bring the cleaned data back into a new Excel sheet.

The Macro Code

To use this, press Alt + F11, go to Insert > Module, and paste:

Sub HighlightAllDuplicates()
    Dim myRange As Range
    Set myRange = Selection
    myRange.FormatConditions.AddUniqueValues
    myRange.FormatConditions(myRange.FormatConditions.Count).SetFirstPriority
    myRange.FormatConditions(1).DupeUnique = xlDuplicate
    With myRange.FormatConditions(1).Interior
        .Color = RGB(255, 199, 206) ' Light Red
    End With
End Sub

Sub HighlightAllDuplicates()
    Dim myRange As Range
    Set myRange = Selection
    myRange.FormatConditions.AddUniqueValues
    myRange.FormatConditions(myRange.FormatConditions.Count).SetFirstPriority
    myRange.FormatConditions(1).DupeUnique = xlDuplicate
    With myRange.FormatConditions(1).Interior
        .Color = RGB(255, 199, 206) ' Light Red
    End With
End Sub

Option Strict On



Sub HighlightAllDuplicates()
    Dim myRange As Range
    myRange = Selection
    myRange.FormatConditions.AddUniqueValues()
    myRange.FormatConditions(myRange.FormatConditions.Count).SetFirstPriority()
    myRange.FormatConditions(1).DupeUnique = xlDuplicate
    With myRange.FormatConditions(1).Interior
        .Color = RGB(255, 199, 206) ' Light Red
    End With
End Sub

Now, you can simply select a range and run this macro to apply the red highlight instantly.

1. Invisible Spaces

Excel sees "Apple" and "Apple " (with a space at the end) as completely different things.

• Fix: Use the =TRIM(A2) function in a helper column to remove all leading and trailing spaces before running your duplicate check.

2. Numbers Stored as Text

If one cell is the number 123 and another is the text '123, Excel will not flag them as duplicates.

• Fix: Select the column, go to Data > Text to Columns, and click Finish. This converts everything in that column to a consistent format.

3. Non-Breaking Spaces

Data copied from websites often contains a "non-breaking space" (ASCII 160) which the TRIM function cannot remove.

• Fix: Use Find and Replace (Ctrl + H). In the "Find" box, hold Alt and type 0160 on your numpad. Leave the "Replace" box empty and click Replace All.

Why Use IronXL?

• No Interop: Avoid the headaches of Microsoft.Office.Interop.
• Manipulate Data with Ease: Easily open and edit Excel Worksheets within your projects, whether you just need to locate duplicate cells and highlight them, edit entire rows, apply formatting styles, extract unique values, or just enter new data.
• Speed: Process massive spreadsheets in milliseconds.
• C# Integration: Use familiar LINQ syntax to find and group duplicates, even if you need to highlight duplicate rows based off multiple criteria.

Code Snippet: Finding Duplicate Entries in C#

using IronXL;
using System.Linq;
// Load the workbook
WorkBook workbook = WorkBook.Load("financial_report.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;
// Select the column to audit (e.g., Column B - Revenue)
var range = sheet["B2:B20"];
// Use LINQ to find the duplicate values
var duplicateList = range.GroupBy(c => c.Value.ToString())
                         .Where(g => g.Count() > 1)
                         .Select(g => g.Key);
// Apply styling to those duplicates
foreach (var cell in range)
{
    if (duplicateList.Contains(cell.Value.ToString()))
    {
        cell.Style.BackgroundColor = "#FFC7CE"; // Light Red fill
        cell.Style.Font.Color = "#9C0006";       // Dark Red text
    }
}
// Save the audited file
workbook.SaveAs("Audited_financial_report.xlsx");

using IronXL;
using System.Linq;
// Load the workbook
WorkBook workbook = WorkBook.Load("financial_report.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;
// Select the column to audit (e.g., Column B - Revenue)
var range = sheet["B2:B20"];
// Use LINQ to find the duplicate values
var duplicateList = range.GroupBy(c => c.Value.ToString())
                         .Where(g => g.Count() > 1)
                         .Select(g => g.Key);
// Apply styling to those duplicates
foreach (var cell in range)
{
    if (duplicateList.Contains(cell.Value.ToString()))
    {
        cell.Style.BackgroundColor = "#FFC7CE"; // Light Red fill
        cell.Style.Font.Color = "#9C0006";       // Dark Red text
    }
}
// Save the audited file
workbook.SaveAs("Audited_financial_report.xlsx");

Imports IronXL
Imports System.Linq

' Load the workbook
Dim workbook As WorkBook = WorkBook.Load("financial_report.xlsx")
Dim sheet As WorkSheet = workbook.DefaultWorkSheet

' Select the column to audit (e.g., Column B - Revenue)
Dim range = sheet("B2:B20")

' Use LINQ to find the duplicate values
Dim duplicateList = range.GroupBy(Function(c) c.Value.ToString()) _
                         .Where(Function(g) g.Count() > 1) _
                         .Select(Function(g) g.Key)

' Apply styling to those duplicates
For Each cell In range
    If duplicateList.Contains(cell.Value.ToString()) Then
        cell.Style.BackgroundColor = "#FFC7CE" ' Light Red fill
        cell.Style.Font.Color = "#9C0006"       ' Dark Red text
    End If
Next

' Save the audited file
workbook.SaveAs("Audited_financial_report.xlsx")

Comparison of Methods

| Method | Best For | Difficulty | | --- | --- | --- | | Conditional Formatting | Quick visual audits of small/medium lists. | Beginner | | Remove Duplicates | Permanent cleaning of data. | Beginner | | COUNTIF Formula | Adding logic to your sheet (e.g., "if count > 3"). | Intermediate | | Power Query | Very large datasets or recurring data imports. | Advanced | | IronXL (.NET) | Building apps or automated server-side reports. | Developer |