How to Check Duplicates in Excel: Every Method Explained (2026)

Method 1: Highlight Duplicates with Conditional Formatting (Fastest)

Conditional formatting is the fastest way to visually identify duplicate cells in an excel worksheet without altering the original data. It applies a formatting style to every cell in your selected cells range that contains a value appearing more than once.

1. Select the range of cells you want to check. For example, select B2:B151 to check the Employee ID column in the employee data file.

2. Go to the Home tab in the Excel ribbon.

3. Click Conditional Formatting in the Styles group.

4. Hover over Highlight Cells Rules from the drop-down menu.

5. Select Duplicate Values from the submenu.

6. In the dialog box, choose your preferred formatting style from the drop-down on the right, such as Light Red Fill or Yellow Fill.
7. Click OK.

Excel immediately highlights every cell in the selected range that contains a duplicate value. Unique values remain unformatted. In the screenshot below, duplicate names in column B are highlighted in red, making them immediately visible across all 150 rows without scrolling through the data manually.

Add image alt text

When this method works best: This approach is ideal for a quick visual check on a single column, such as scanning for duplicate names, duplicate emails in column D, or duplicate employee IDs. Using conditional formatting allows you to visually identify duplicate values without altering the original data, making it a quick method for spotting duplicates before deciding what to do next. For generating Excel reports with duplicate detection built in, IronXL lets you apply these checks programmatically in C#.

Important: This method highlights cells with duplicate values based on exact matches only. It is case-insensitive, so "JAMES" and "james" would be treated as duplicates. Empty cells are not flagged as duplicates.

Method 2: Count Duplicates with COUNTIF

The COUNTIF function gives you a precise count of how many times each value appears in a range. This is useful when you need to know not just whether duplicates exist, but exactly how many duplicates there are for each entry and which specific rows are affected.

1. Click on an empty cell next to your data, for example cell Z2, to create a helper column.
2. Enter the following formula: =COUNTIF($B$2:$B$151,B2)
3. Press Enter.
4. Copy the formula down through the rest of your data rows, from Z2 to Z151.

The COUNTIF formula returns the number of times the value in A2 appears in the specified range from A2 to A151. A result of 1 means the value is unique. Any result greater than 1 means that value appears multiple times and is a duplicate. The countif function returns boolean values in a logical context: when the formula returns true it signals a duplicate value, and false when the entry is unique, making it straightforward to select data for filtering. When the range contains an empty string or blank, those empty cells each count as identical values and will return a row count greater than 1 if multiple blanks exist. A cell reference like A2 in the criteria argument always stays relative so it shifts correctly as the formula copies down.

In the screenshot below, the helper column in column Z shows a count of 1 for unique Employee IDs and counts of 2 or higher for rows where the same ID appears more than once, making it easy to identify exactly which rows are duplicates and how many duplicates exist.

Add image alt text

Counting duplicates across two columns: To check for duplicates based on multiple criteria, use the COUNTIFS function instead. For example, =COUNTIFS($B$2:$B$151,B2,$C$2:$C$151,C2) returns the count of rows where both First Name and Last Name match, helping you find duplicate names even when the data spans two columns.

Pro tip: To count duplicates only from the first occurrence onward, use =COUNTIF($A$2:A2,A2). This formula expands as it copies down, so the first occurrence of any value always returns 1, and only repeated occurrences return 2 or higher. This lets you identify and delete only the duplicate rows while keeping the first occurrence of each value.

Method 3: Filter to Find Duplicate Rows

Once you have used COUNTIF to tag duplicate rows in a helper column, you can use Excel's filter feature to isolate and find duplicate rows quickly across the entire spreadsheet.

1. Complete Method 2 first to add a COUNTIF helper column in column Z.
2. Click any cell in your data range, for example B2.

3. Go to the Data tab and click Filter in the Sort & Filter group.

4. Click the drop-down arrow on the helper column header in column Z.
5. Uncheck 1 and leave all other values checked, then click OK.

Excel now displays only the rows where the count is greater than 1, which are your duplicate rows. You can review, edit, or delete them as needed. To find duplicate rows across the entire table, check all columns in your filter criteria before applying.

Add image alt text

Using Advanced Filter for unique records: An alternative approach is to use the Advanced Filter option under the Data tab. Go to Data > Advanced, check "Unique records only," and choose whether to filter in place or copy the results to a new location. This extracts unique records from your dataset directly, which is faster than filtering out duplicates manually when you only need to see distinct entries.

Method 4: Remove Duplicates with the Built-In Feature

The Remove Duplicates feature in Excel is the most direct way to eliminate duplicate rows from your data. It permanently deletes duplicate rows, keeping only the first occurrence of each duplicate set.

Before using the Remove Duplicates feature, create a backup of your data. This action permanently deletes duplicate rows and cannot be undone after saving the file.

1. Select any cell inside your data range, for example B2.

2. Go to the Data tab in the Excel ribbon.

3. Click Remove Duplicates in the Data Tools group.

4. In the Remove Duplicates dialog, select which columns to check. To remove duplicate rows based on the entire row, check all columns. To remove duplicates based on a specific column like Employee ID, check only that column.
5. Click OK.

Excel removes all duplicate rows based on your selected columns, keeping only the first occurrence of each value. A dialog box confirms the final result: how many duplicate values were found and removed, and how many unique values remain.

In the screenshot below, the Remove Duplicates dialog shows all 25 columns from the employee data file listed, with the option to check or uncheck each one. After clicking OK, Excel reports the number of duplicate rows removed and the row count of unique records remaining.

Add image alt text

Choosing which columns to check: If you select all columns, Excel only removes rows that are completely identical across every field, which is rarely what you want. In most cases, you will check just the key identifier column, such as Employee ID in column A, the upper left cell of your data range, to remove duplicate rows based on that single column while keeping the rest of the data intact. To remove duplicate values from a specific column only, check that column and uncheck all others in the Remove Duplicates dialog. This lets you remove duplicates excel-style for any targeted field without disrupting the rest of the row.

Note: The Remove Duplicates feature does not preview the rows before deleting them. If you want to review duplicate rows before removing them, use Method 2 and Method 3 first to identify them, then delete manually.

Method 5: Find Duplicates Across Multiple Columns Using COUNTIFS

When you need to identify duplicates based on a combination of fields rather than a single column, the COUNTIFS function handles multiple criteria at once. This is useful when a single column like First Name has many repeats that are not true duplicates, but the combination of First Name and Last Name is what defines a unique record.

1. Add a helper column, for example in column Z at cell Z2.
2. Enter the formula: =COUNTIFS($B$2:$B$151,B2,$C$2:$C$151,C2)
3. Copy the formula down to Z151.

This formula returns the count of rows where both the First Name in column B and the Last Name in column C match the values in the current row. A result greater than 1 identifies that combination as a duplicate entry.

For even more specific duplicate detection across the entire table, you can extend the COUNTIFS formula to include additional columns such as Department in column E and Hire Date in column K, which is useful when you want to find duplicate entries that share the same date. Excel formulas like COUNTIFS are among the most flexible tools for this kind of work because the function returns a count for any combination of criteria you define.

Add image alt text

Method 6: Use Power Query to Remove Duplicates in Large Datasets

Power Query is recommended for removing duplicates in larger datasets with over 10,000 rows. It is an advanced tool in Excel that handles complex duplicate scenarios more efficiently than the built-in Remove Duplicates feature, and it preserves your original data in a separate query rather than modifying the source directly.

1. Click any cell in your data range, for example B2.

2. Go to the Data tab and click From Table/Range in the Get & Transform Data group. If prompted, confirm that your data has headers.

3. The Power Query Editor opens. Select the column you want to check for duplicates, such as the Employee ID column.
4. Right-click the column header and select Remove Duplicates.
5. Click Close & Load in the Home tab of the Power Query editor to load the deduplicated results to a new sheet.

The Power Query editor loads your data into a separate query and removes duplicate rows based on the selected column, keeping only the first occurrence. The results are loaded to a new location on a new sheet, so your original data stays untouched.

Add image alt text

Excel 365 and the UNIQUE function: Excel 365 users can also use the UNIQUE function to extract only unique values from a dataset directly on the worksheet. The formula =UNIQUE(A2:A151) returns a list of unique values from the Employee ID column in a single step, without opening the Power Query editor. This is a sophisticated method for managing duplicates and unique records side by side.

Common Issues and Troubleshooting

Conditional formatting flags cells that do not look like duplicates

Conditional formatting is case-insensitive and trims leading or trailing spaces before comparing. If two cells look different but are being flagged, check for hidden characters using the TRIM function or Find and Replace.

Remove Duplicates says "no duplicates found" but I can see identical values

The comparison is exact across all selected columns. If you checked all columns, rows must be completely identical in every field to be flagged. Try checking only the key column to narrow the criteria.

COUNTIF returns 1 for everything even though I can see duplicates

The range argument may not be locked correctly. Make sure it uses absolute references like $A$2:$A$151 while the criteria uses a relative reference like A2. Without locked references, the formula shrinks as it copies down and misses rows.

Power Query is not available in my version of Excel

Power Query is available in Excel 2016 and later on Windows, and Excel 2019 and later on Mac. In Excel 2010 and 2013 on Windows, it is available as a free Microsoft add-in.

Conditional formatting highlights empty cells as duplicates

If your range includes multiple empty cells, Excel treats them as identical values by default. Use a custom rule with a COUNTIF formula instead: =COUNTIF($A$2:$A$151,A2)>1 applied only to non-empty cells in your selected range.

Choosing the Right Method: Quick Reference

| Goal | What to do | | --- | --- | | Visually highlight duplicate values without changing data | Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values | | Count how many times each value appears | Use =COUNTIF($A$2:$A$151,A2) in a helper column | | Filter to show only duplicate rows | Add COUNTIF helper column, then Data > Filter on that column | | Remove duplicate rows permanently | Data tab > Remove Duplicates (back up your data first) | | Find duplicates across multiple columns | Use =COUNTIFS() with criteria for each relevant column | | Handle large datasets over 10,000 rows | Data tab > From Table/Range > Power Query Editor > Remove Duplicates | | Extract only unique values in Excel 365 | Use =UNIQUE(range) directly on the worksheet |

For Developers: Detect and Handle Duplicate Values with IronXL

If your application processes Excel files automatically, it will often need to check for and remove duplicate data as part of a data pipeline. IronXL gives you a clean API for reading cell values, comparing them programmatically, and removing duplicate rows in C# without Microsoft Office installed on the server.

Here's how to find and remove duplicate rows based on the Employee ID column in C#:

using IronXL;
using System.Collections.Generic;
using System.Linq;
WorkBook workBook = WorkBook.Load("employee_data.xlsx");
WorkSheet sheet = workBook.DefaultWorkSheet;
var seen = new HashSet<string>();
var rowsToDelete = new List<int>();
// Start from row 2 to skip the header row
for (int i = 2; i <= 151; i++)
{
    string cellValue = sheet[$"A{i}"].StringValue;
    if (!seen.Add(cellValue))
    {
        // Value already seen, this is a duplicate row
        rowsToDelete.Add(i);
    }
}
// Delete duplicate rows in reverse order to preserve row numbers
foreach (int rowIndex in rowsToDelete.OrderByDescending(r => r))
{
    sheet.RemoveRow(rowIndex);
}
workBook.SaveAs("employee_data_deduplicated.xlsx");

using IronXL;
using System.Collections.Generic;
using System.Linq;
WorkBook workBook = WorkBook.Load("employee_data.xlsx");
WorkSheet sheet = workBook.DefaultWorkSheet;
var seen = new HashSet<string>();
var rowsToDelete = new List<int>();
// Start from row 2 to skip the header row
for (int i = 2; i <= 151; i++)
{
    string cellValue = sheet[$"A{i}"].StringValue;
    if (!seen.Add(cellValue))
    {
        // Value already seen, this is a duplicate row
        rowsToDelete.Add(i);
    }
}
// Delete duplicate rows in reverse order to preserve row numbers
foreach (int rowIndex in rowsToDelete.OrderByDescending(r => r))
{
    sheet.RemoveRow(rowIndex);
}
workBook.SaveAs("employee_data_deduplicated.xlsx");

Imports IronXL
Imports System.Collections.Generic
Imports System.Linq

Dim workBook As WorkBook = WorkBook.Load("employee_data.xlsx")
Dim sheet As WorkSheet = workBook.DefaultWorkSheet
Dim seen As New HashSet(Of String)()
Dim rowsToDelete As New List(Of Integer)()

' Start from row 2 to skip the header row
For i As Integer = 2 To 151
    Dim cellValue As String = sheet($"A{i}").StringValue
    If Not seen.Add(cellValue) Then
        ' Value already seen, this is a duplicate row
        rowsToDelete.Add(i)
    End If
Next

' Delete duplicate rows in reverse order to preserve row numbers
For Each rowIndex As Integer In rowsToDelete.OrderByDescending(Function(r) r)
    sheet.RemoveRow(rowIndex)
Next

workBook.SaveAs("employee_data_deduplicated.xlsx")

The code reads each value in the Employee ID column (column A), tracks values it has already seen using a HashSet, and flags any row where the value has appeared before as a duplicate. Rows are deleted in reverse order so that removing one row does not shift the index of remaining rows.

To highlight duplicate values with a background color instead of deleting them, IronXL's conditional formatting API lets you apply formatting rules programmatically to any cell range, matching the visual output of Excel's built-in Highlight Cells Rules feature.

IronXL also handles reading and writing Excel data, cell formatting and styling, and processing large workbooks without requiring Microsoft Office installed on the server.

Getting started: Install IronXL via NuGet with Install-Package IronXL.Excel and start your free trial. IronXL runs on .NET 6+ and is compatible with Windows, Linux, macOS, Docker, Azure, and AWS.

Wrapping Up

Checking for duplicates in Excel is something you will need to do regularly, whether cleaning up a customer list, verifying employee records, or preparing data for analysis. For a quick visual check, conditional formatting is the fastest answer: it highlights every duplicate value across your selected range without touching the original data. For developers automating this in .NET, IronXL provides the tools to detect and remove duplicates programmatically without Microsoft Office on the server.

To find duplicate values precisely, COUNTIF and COUNTIFS let you count occurrences, identify specific rows, and build filters around them. When ready to remove duplicate rows permanently, the Remove Duplicates feature in the Data tab handles it in a few clicks, while Power Query is the better tool for large datasets where performance and non-destructive processing matter. Whatever method you choose, back up your original data before removing anything.