How to Find, Highlight, and Remove Duplicates in Excel

To remove duplicates in Excel, select your data range, go to the Data tab, and click Remove Duplicates. Choose which columns Excel should compare, then click OK. Excel will keep the first occurrence of each duplicate and delete the rest. For more control, use Conditional Formatting to highlight duplicates or COUNTIF formulas to identify them before deleting anything.

Introduction

If you’ve ever merged two customer lists, imported data from an external system, or had multiple team members enter records into the same spreadsheet, you already know the problem: duplicates creep in quietly and make a mess of your data.

A seemingly clean spreadsheet with 500 rows might actually contain 60 duplicate entries inflating your counts, skewing your averages, and making your reports unreliable. The real danger isn’t that duplicates look messy — it’s that they often look perfectly normal until something doesn’t add up.

Fortunately, Excel gives you multiple ways to find, highlight, and remove duplicates, ranging from a single-click tool to formula-based methods that give you complete control before you delete a single row.

This guide covers every approach, explains when to use each one, and walks you through the steps in plain language — whether you’re an occasional spreadsheet user or someone who lives in Excel every day.

Why Duplicate Data in Excel Is More Damaging Than It Looks

Before diving into methods, it’s worth understanding what’s actually at stake with duplicate records.

A duplicate customer entry means that person receives the same email twice — and that damages your sender reputation and their trust. A duplicated invoice row overstates your accounts receivable. A repeated product SKU throws off your inventory counts. In data analysis, even a handful of duplicate rows can shift averages, inflate totals, and produce insights that lead to poor decisions.

The most dangerous thing about Excel duplicates is that they’re invisible at a glance. Your spreadsheet shows neat, formatted rows of data that appear complete and correct. But the numbers don’t add up, and tracing the discrepancy back to duplicate entries can take hours.

Cleaning duplicates is one of the first steps in any data preparation process — before you build pivot tables, run formulas, or create dashboards.

Method 1: How to Identify Duplicates in Excel Using COUNTIF

The COUNTIF approach lets you flag duplicates without touching your original data. It counts how many times each value appears in a range, and any count greater than one signals a duplicate. This is the safest starting point when you want to review duplicates before deciding what to delete.

Basic COUNTIF Formula

Say your data is in column A from A2 to A200. In column B, enter this formula in B2:

=COUNTIF($A$2:$A$200, A2)

Drag the formula down to cover your full range. Every cell showing a number higher than 1 is a value that appears more than once. The dollar signs lock the range so it doesn’t shift as you copy the formula down.

Label Duplicates as “Duplicate” or “Unique”

If you prefer a readable label instead of a number, wrap the formula in an IF statement:

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

Now you can filter the helper column to show only “Duplicate” rows, review them, and decide which ones to remove. This non-destructive approach is especially useful when someone else needs to approve the deletions before they happen.

Detecting Duplicates Across Multiple Columns

When a “duplicate” is defined by a combination of values — for example, the same name AND email address together — switch to COUNTIFS:

=COUNTIFS($A$2:$A$200, A2, $B$2:$B$200, B2)

This formula only flags a row as a duplicate when both the name in column A and the email in column B match another row. A person with the same name but a different email address would not be flagged.

Method 2: How to Highlight Duplicates in Excel Using Conditional Formatting

Conditional Formatting is the fastest visual way to show duplicates in Excel. It automatically colors duplicate cells so they stand out immediately — no formulas required in your data columns.

Step-by-Step: Highlight Duplicate Values

Select the cell range you want to check (for example, A2:A200).
Go to the Home tab on the ribbon.
Click Conditional Formatting in the Styles group.
Hover over Highlight Cells Rules.
Click Duplicate Values.
In the dialog box, choose your preferred highlight color. The default red fill with dark red text is easy to spot.
Click OK.

Excel immediately highlights every cell that appears more than once. Unique values stay unformatted.

How to Highlight Entire Duplicate Rows

The built-in Duplicate Values rule only highlights individual cells, not entire rows. If you need to highlight a full row wherever a multi-column combination repeats, you’ll need a custom rule.

Select your entire data range (e.g., A2:D200 — include all columns).
Go to Home → Conditional Formatting → New Rule.
Select “Use a formula to determine which cells to format.”
Enter a COUNTIFS formula such as:

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

Click Format, choose your highlight color, and click OK twice.

The mixed references ($A2 rather than $A$2) allow the rule to check each row independently while locking to the correct columns. The result is entire rows highlighted wherever the combination of column A and column B repeats.

Method 3: How to Remove Duplicates in Excel — The Built-In Tool

When you’re ready to delete duplicates, Excel’s Remove Duplicates tool on the Data tab is the fastest and most straightforward option. It’s designed specifically for this task and works reliably for most use cases.

Step-by-Step: Using Remove Duplicates

Click anywhere inside your dataset.
Go to the Data tab on the ribbon.
Click Remove Duplicates in the Data Tools group.
A dialog box appears listing all your column headers.
Select the columns Excel should use when comparing rows. To remove rows where every column matches, check all of them. To remove rows where just the email or ID column repeats, check only that column.
Click OK.

Excel processes your data and shows a summary: how many duplicates were removed and how many unique rows remain.

What Excel Keeps and What It Deletes

Excel always keeps the first occurrence of a duplicate and removes all subsequent ones. If the order of your data matters (for example, you want to keep the most recent entry rather than the oldest), sort your data by date in descending order before running Remove Duplicates.

Before You Click OK — Back Up Your Data

Remove Duplicates can be undone with Ctrl+Z immediately after running it, but once you save and close the file, the change is permanent. Get into the habit of duplicating the worksheet before bulk deletions:

Right-click the sheet tab → Move or Copy → check Create a copy → click OK.

This takes ten seconds and can save hours of recovery work.

Method 4: Extract Unique Values Without Deleting (Formula-Based)

Sometimes you don’t want to delete anything from your source data. Instead, you want to extract a clean, duplicate-free list somewhere else. Excel gives you a clean way to do this — depending on which version you’re using.

UNIQUE Function (Microsoft 365 and Excel 2021)

If you’re on a current version of Excel, the UNIQUE function handles this in a single formula:

=UNIQUE(A2:A200)

Type this anywhere you want the unique list to appear. Excel automatically returns all unique values from the range and excludes duplicates. It’s a dynamic array formula, so if your source data changes, the output updates automatically.

To return unique combinations across multiple columns:

=UNIQUE(A2:C200, FALSE, FALSE)

This returns rows where the entire row combination is unique — a particularly useful feature for multi-column datasets.

For Excel 2016 and 2019 (Without UNIQUE)

If you’re on an older Excel version without the UNIQUE function, extracting unique values via formula requires complex array formulas that can be difficult to maintain. In practice, most users in this situation find it easier to run Remove Duplicates on a copy of the data, or use Power Query — which we’ll cover next.

Method 5: Using Power Query to Remove Duplicates (Best for Large or Recurring Datasets)

Power Query is Excel’s built-in data transformation engine, introduced in Excel 2016 and significantly expanded in Microsoft 365. For large datasets, data that imports regularly, or workflows where deduplication needs to happen automatically, Power Query is the most powerful option.

Step-by-Step: Remove Duplicates with Power Query

Click anywhere in your data table.
Go to Data → Get & Transform Data → From Table/Range. If your data isn’t already in a Table, Excel will prompt you to convert it — click OK.
The Power Query Editor opens.
To remove rows where one specific column has duplicates: right-click that column header and select Remove Duplicates.
To remove rows where all selected columns together create a duplicate: hold Ctrl and click multiple column headers, then go to Home → Remove Rows → Remove Duplicates.
Click Close & Load to return the deduplicated data to Excel.

The real power here is repeatability. Once you set up the query, every time your source data updates (from a new CSV export or database import, for example), you just click Refresh and Power Query applies the deduplication automatically. No manual steps required.

How to Show and Filter Duplicates Without Removing Them

Not every situation calls for immediate deletion. If you need to review duplicates first — or share them with a colleague for approval — filtering is the cleanest way to isolate them temporarily.

Filter to Display Only Duplicate Rows

Add a helper column using the formula: =COUNTIF($A$2:$A$200, A2)>1 This returns TRUE for duplicates, FALSE for unique values.
Enable AutoFilter: go to Data → Filter (or press Ctrl+Shift+L).
Click the dropdown arrow on your helper column and filter to show only TRUE values.
The spreadsheet now shows only duplicate rows. Review them, add comments, or delete specific ones manually.
Remove the filter when done to restore the full view.

Choosing the Right Method: A Quick Reference Table

Your Situation	Best Method
Quick one-time cleanup	Remove Duplicates tool (Data tab)
Visual review before deleting	Conditional Formatting
Flag duplicates without changing data	COUNTIF helper column
Extract a clean unique list dynamically	UNIQUE function (Microsoft 365)
Large dataset or recurring data import	Power Query
Duplicate defined by multiple columns	COUNTIFS or Power Query
Need someone to approve deletions	COUNTIF filter + manual review

Common Mistakes When Removing Duplicates in Excel

Even people who use Excel regularly run into avoidable issues with duplicate removal. Here are the ones worth knowing about.

Checking every column when you only need one. If you select all columns in the Remove Duplicates dialog, Excel only removes rows where every value in every column is identical. If your duplicates are defined by a customer ID or email address alone, select only that column.

Missing case differences. Excel’s Remove Duplicates and Conditional Formatting treat “john@example.com” and “JOHN@EXAMPLE.COM” as identical — they’re case-insensitive. If case matters in your data, you’ll need a helper column using the EXACT function to identify case-sensitive duplicates before proceeding.

Ignoring whitespace. A space before or after a value — like ” New York” versus “New York” — makes Excel treat them as completely different entries. Rogue spaces are one of the most common causes of failed deduplication. Clean your data first with the TRIM function: =TRIM(A2), paste as values, then run deduplication.

Applying the tool to a partial selection. If you select only part of your table before clicking Remove Duplicates, Excel works only within that selection and may leave duplicates in the unselected rows — or worse, misalign your data. Always click inside your full table or select the complete range.

Not sorting before removing. Because Excel keeps the first occurrence, the order of your data determines which records survive. If recency matters (keeping the latest version of a record), sort by date descending first.

FAQ

Does Excel’s Remove Duplicates keep the first or last occurrence?

Excel always keeps the first occurrence of each duplicate group and removes the rest. If you need to keep the most recent record rather than the oldest, sort your data by date in descending order before running the tool.

Can I remove duplicates based on more than one column?

Yes. In the Remove Duplicates dialog, select the specific columns that define a “duplicate” for your use case. Excel will only remove rows where the combination of values in those selected columns repeats.

How do I find duplicates across two different Excel sheets?

Use COUNTIF with a cross-sheet reference. For example, in Sheet 1 column B, enter: =COUNTIF(Sheet2!$A$2:$A$200, A2). This returns how many times the value in your current row also appears in Sheet 2. Any count greater than zero indicates a match.

Will Remove Duplicates work on filtered data only?

No. The Remove Duplicates tool processes all rows in the selected range — including hidden or filtered-out rows. If you need to remove duplicates from a filtered subset, use Power Query or manually delete rows after applying your filter.

How do I undo Remove Duplicates in Excel?

Press Ctrl+Z immediately after running Remove Duplicates to undo the action. This works as long as you haven’t saved and closed the file. After saving, the deletion is permanent — which is why backing up your data beforehand is so important.

Is there a formula to count the total number of duplicates in a range?

Yes. Use: =SUMPRODUCT((COUNTIF(A2:A200, A2:A200)>1)*1) to count all entries that have at least one duplicate elsewhere in the range. This includes the first occurrence — so if a value appears three times, it contributes 3 to the count.

Does Conditional Formatting slow down Excel on large datasets?

On datasets with tens of thousands of rows, complex Conditional Formatting rules can noticeably slow file performance. In those cases, a COUNTIF helper column is a more efficient alternative. Once you’ve identified and resolved the duplicates, delete the Conditional Formatting rules to restore normal performance.

How do I remove duplicates from a column but keep all the other row data intact?

Use the Remove Duplicates tool and select only the column that defines the duplicate (such as an ID or email column). Excel removes the entire duplicate row but keeps all the data in the surviving row unchanged. Alternatively, use Power Query’s Remove Duplicates option on the key column to achieve the same result.

Conclusion

Duplicate data is one of the most common — and most consequential — data quality problems in Excel. Left unchecked, it distorts analysis, inflates counts, and quietly undermines the decisions your data is supposed to support.

The good news is that Excel gives you exactly the right tools for each situation. The built-in Remove Duplicates feature on the Data tab is the fastest fix for a one-time cleanup. Conditional Formatting makes duplicates visible at a glance before you touch anything. COUNTIF formulas let you flag and review duplicates without altering the original data. And Power Query handles deduplication automatically for large datasets or recurring data imports.

Whatever method you choose, build one habit before any bulk deletion: copy your sheet first. That ten-second step is the difference between a clean dataset and a panic-driven undo session.

With a solid understanding of each approach, you can handle duplicate data in Excel confidently — whether you’re working with a 50-row contact list or a 50,000-row database export

How to Find, Highlight, and Remove Duplicates in Excel (Complete Step-by-Step Guide)