How to Check for Duplicates in Excel

How to Check for Duplicates in Excel: 5 Proven Methods (2026 Guide)

Duplicate data is one of the most common and costly problems in Excel. A single repeated row can skew your analysis, inflate totals, and lead to wrong decisions.

The good news? Excel gives you several powerful tools to find, highlight, count, and remove duplicates in minutes, whether you’re on a desktop, tablet, or phone.

In this guide, you’ll learn 5 practical methods to handle duplicates in Excel, from one-click visual highlighting to advanced formula techniques.

⚡ TL;DR — Quick Method Summary

Conditional Formatting → Visually highlight duplicates instantly
Remove Duplicates Tool → Delete duplicate rows with one click
COUNTIF Formula → Count or flag duplicates with a formula
UNIQUE Function → Extract a clean, duplicate-free list (Excel 365/2021)
Power Query → Best for large datasets and recurring cleanup tasks

Method 1: Highlight Duplicates with Conditional Formatting

Best for: Quick visual audit without changing your data

This is the fastest way to see duplicates. Excel colors them automatically with no formulas needed.

Step-by-Step

Select your data range (e.g., tap or click cell A2, then drag to A100)
Go to Home tab in the ribbon
Tap Conditional Formatting
Choose Highlight Cells Rules
Select Duplicate Values…
Pick your highlight color from the dropdown
Tap OK

Duplicate values in your selected range will now appear highlighted.

When to Use This Method

✅ You need a fast visual overview
✅ You don’t want to delete anything yet
✅ You’re working with a single column
❌ It won’t count or remove duplicates
❌ It highlights both instances (original + duplicate)

Formula Alternative: Dynamic Conditional Formatting

Want more control? Use a custom formula rule to highlight only the second and later occurrences, leaving the first instance unhighlighted.

Step 1: Select your range (e.g., A2:A100).

Step 2: Go to Conditional Formatting > New Rule > Use a formula.

Step 3: Enter this formula.

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

Step 4: Choose your fill color and click OK

How it works: The expanding range $A$2:A2 grows as Excel checks each row. If the value has appeared before, COUNTIF returns a number greater than 1, triggering the highlight.

Method 2: Remove Duplicates Tool (One-Click Cleanup)

Best for: Quickly deleting duplicate rows from your dataset

Excel’s built-in Remove Duplicates tool is the fastest way to clean your data. It permanently deletes duplicate rows, so always work on a copy of your data first.

Step-by-Step

Click anywhere inside your data range
Go to the Data tab in the ribbon
Click Remove Duplicates
A dialog box appears. Check the columns you want Excel to compare
Make sure “My data has headers” is checked if your first row has column names
Click OK

Excel will show you how many duplicates were removed and how many unique rows remain.

Important Notes

✅ Works across multiple columns (finds rows where all selected columns match)
✅ Keeps the first occurrence of each duplicate
❌ Permanently deletes rows. Undo with Ctrl+Z immediately if something goes wrong
❌ Not suitable if you want to review duplicates before deleting

Method 3: Find Duplicates with COUNTIF Formula

Best for: Flagging duplicates in a helper column without deleting data

The COUNTIF approach is non-destructive and gives you full control. You add a helper column that labels each row as “Duplicate” or “Unique.”

Basic COUNTIF Formula

In an empty column next to your data, enter:

=IF(COUNTIF($A$2:$A$100, A2) > 1, "Duplicate", "Unique")

Drag the formula down to cover all your rows.

What this does: For each cell, it counts how many times that value appears in the full range. If more than once it shows “Duplicate”. If exactly once it shows “Unique”.

Flag Only the Second (and Later) Occurrences

To keep the first instance and only flag repeats:

=IF(COUNTIF($A$2:A2, A2) > 1, "Duplicate", "Unique")

Note the expanding range: $A$2:A2. The first $A$2 is locked, but the second A2 moves down as you copy the formula.

When to Use COUNTIF

✅ You want to review duplicates before deciding what to do
✅ You need an audit trail
✅ You’re working with a shared file where others need context
❌ More steps than Remove Duplicates
❌ Requires a helper column

Method 4: Extract Unique Values with the UNIQUE Function

Best for: Creating a clean, duplicate-free list — Requires Excel 365 or 2021

The UNIQUE function is a game-changer. It spills a deduplicated list into your sheet automatically and updates live when your source data changes.

Basic Syntax

=UNIQUE(array, [by_col], [exactly_once])

Example: Extract Unique Names from Column A

Click an empty cell (e.g., C2) and enter:

=UNIQUE(A2:A100)

Excel will automatically fill the cells below C2 with your unique values.

Extract Values That Appear Exactly Once

To get only values that have no duplicates at all:

=UNIQUE(A2:A100, FALSE, TRUE)

The third argument TRUE means “return only values that appear exactly once.”

When to Use UNIQUE

✅ You want a live-updating deduplicated list
✅ You’re on Excel 365 or 2021
✅ You need to feed unique values into another formula
❌ Not available in Excel 2019 or earlier
❌ Does not modify the original data

Method 5: Use Power Query for Large Datasets

Best for: Large datasets, recurring cleanup, or complex multi-column deduplication

Power Query is Excel’s built-in data transformation tool. It’s ideal when you need a repeatable, scalable process.

Step-by-Step

Click anywhere in your data
Go to the Data tab
Click From Table/Range (or “Get & Transform Data” in older versions)
Power Query Editor opens
Select the columns you want to deduplicate (hold Ctrl to select multiple)
Right-click the selected column headers
Choose Remove Duplicates
Click Close & Load to send clean data back to Excel

Why Power Query Stands Out

✅ Non-destructive. Your original data stays untouched
✅ Refreshable. Re-run the process any time your source data updates
✅ Handles thousands of rows efficiently
✅ Can deduplicate across multiple columns with precision
❌ Slightly steeper learning curve for beginners
❌ Overkill for simple, one-time cleanups

How to Check for Duplicates Across Two Columns

Sometimes you need to find rows where a combination of values repeats, for example the same first name AND last name together.

Using COUNTIFS (Multiple Criteria)

=COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2) > 1

Wrap it in an IF for a readable label:

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

This checks whether the combination of values in column A AND column B repeats anywhere in your dataset.

How to Find Duplicates in Two Separate Lists

Need to compare two lists and find what appears in both?

Using COUNTIF to Cross-Reference Lists

Assume List 1 is in column A and List 2 is in column C. In column B, enter:

=IF(COUNTIF($C$2:$C$100, A2) > 0, "Match Found", "No Match")

This checks each value in List 1 against the entirety of List 2.

Using VLOOKUP as an Alternative

=IFERROR(VLOOKUP(A2, $C$2:$C$100, 1, 0), "Not Found")

If a match is found, the value is returned. If not, “Not Found” appears.

Choosing the Right Method: A Quick Reference

Visual review only (no changes)Use Conditional Formatting

Delete duplicates immediatelyUse Remove Duplicates Tool

Flag duplicates in a helper columnUse COUNTIF Formula

Build a live, unique values listUse UNIQUE Function (Excel 365/2021)

Large datasets or recurring cleanupUse Power Query

Frequently Asked Question

How do I check for duplicates in Excel?

The quickest way is to use Conditional Formatting. Select your data range, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values and choose a color. Excel will immediately highlight all duplicates in that range.

How do I find duplicates in Excel using a formula?

Use the COUNTIF formula in a helper column:

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

This returns TRUE for any value that appears more than once. Wrap it in an IF statement to label them: =IF(COUNTIF($A$2:$A$100, A2) > 1, "Duplicate", "Unique").

How do I remove duplicates in Excel?

Click inside your data, go to Data > Remove Duplicates, select which columns to check, and click OK. Excel will remove duplicate rows and keep the first occurrence. Always work on a copy of your data first.

How do I find duplicates in Excel across two columns?

Use the COUNTIFS function to check multiple columns simultaneously:

=COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, B2) > 1

This flags rows where the combination of values in both columns repeats.

Does Excel have a built-in duplicate checker?

Yes. Excel has two built-in features: Conditional Formatting (highlights duplicates visually) and the Remove Duplicates tool under the Data tab (deletes duplicate rows). For Excel 365 and 2021, the UNIQUE function also provides a formula-based deduplication option.

How do I find duplicates in two separate Excel lists?

Use COUNTIF to check if values from one list appear in another:

=IF(COUNTIF($C$2:$C$100, A2) > 0, "Found in List 2", "Not Found")

Place this formula in a helper column next to your first list.

What is the UNIQUE function in Excel?

The UNIQUE function (available in Excel 365 and Excel 2021) extracts a deduplicated list from a range. Simply enter =UNIQUE(A2:A100) in an empty cell and Excel spills a clean list of unique values automatically.

How do I highlight duplicates but keep the first occurrence?

Use a Conditional Formatting rule with this custom formula:

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

Apply this to your range. The expanding reference means only the second and later occurrences get highlighted. The first instance stays unhighlighted.

Can I find duplicates in Excel on a phone or tablet?

Yes. The Conditional Formatting method works on the Excel mobile app. Go to the Home tab, tap Conditional Formatting, and select Duplicate Values. The Remove Duplicates tool is available on tablet versions. For complex formula work, a desktop browser or the desktop app is recommended.

What happens when I use Remove Duplicates in Excel?

Excel scans the columns you select, identifies rows where all selected column values match, and permanently deletes the duplicate rows, keeping only the first occurrence. A summary dialog tells you how many duplicates were removed and how many unique rows remain.

Final Thoughts

Duplicate data is a silent accuracy killer in any spreadsheet. Thankfully, Excel gives you multiple ways to tackle it, from the instant visual feedback of Conditional Formatting to the precision of COUNTIF formulas and the power of Power Query.

Start with Conditional Formatting if you just want to see what’s duplicated. Use Remove Duplicates if you’re ready to clean up. And if you’re on Excel 365, the UNIQUE function is worth adding to your regular toolkit.

Bookmark this guide as your go-to reference and spend less time hunting duplicates and more time making decisions from clean, reliable data.