Remove Duplicates in Excel and Highlight Duplicates

Part of the free Module 4: Sort and Filter · Lesson 8 of 11 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted. The UNIQUE function needs Excel 365 or 2021.

To remove duplicates in Excel, select your data and click Data > Data Tools > Remove Duplicates, tick the columns that define a duplicate, and press OK. Excel deletes every repeated row and keeps the first occurrence. If you only want to see the repeats rather than delete them, use Conditional Formatting to highlight duplicates or a COUNTIF helper column to flag and count them.

Table of Contents

What counts as a duplicate in Excel

A duplicate is a row (or a cell) whose value matches another one exactly. The details decide what Excel treats as a match, so check them before you delete anything.

Whole row or one column. If you tick every column in Remove Duplicates, only rows that match in all columns are removed. If you tick one column, such as Email, any two rows with the same email are duplicates even when the names differ.
Case is ignored. Remove Duplicates, COUNTIF and Conditional Formatting treat Ravi and RAVI as the same value. Only the EXACT function is case-sensitive.
Trailing spaces break matches. "Delhi" and "Delhi " are different values. Clean the column with =TRIM(A2) first.
Numbers stored as text are different from numbers. The text "1001" and the number 1001 are not duplicates. Convert with Data > Text to Columns or =VALUE(A2).
Formatting does not matter. 01/03/2026 and 1 March 2026 are the same date serial number, so they are duplicates.

Remove duplicates with the Data Tools command

This is the fastest method and it works in every version of Excel. It deletes the repeated rows permanently, so copy the sheet first if the data is important.

Click any cell inside your data. Excel selects the whole range automatically when you open the dialog.
Go to Data > Data Tools > Remove Duplicates. The shortcut is Alt, A, M.
Keep My data has headers ticked so the column names appear in the list instead of Column A, Column B.
Tick the columns that should define a duplicate. Use Select All to compare whole rows or Unselect All and tick just one column.
Click OK. Excel shows a message such as “3 duplicate values found and removed; 9 unique values remain.”

Excel keeps the first occurrence from the top and deletes the later ones. If you want to keep the most recent record instead, sort the data by date, newest first, before you run the command. Ctrl+Z restores the deleted rows if the result is wrong.

Highlight duplicates with Conditional Formatting

Highlighting is non-destructive. The rows stay in place, so you can review them, sort by colour or filter by colour before you decide what to delete.

Highlight duplicates in one column

Select the column, for example C2:C13.
Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Leave Duplicate selected, pick a fill colour and click OK.

Every value that appears more than once is coloured, including the first occurrence. Choose Unique in the same dialog to colour the values that appear only once.

Highlight duplicate rows across several columns

The built-in rule looks at one column at a time. To highlight rows that match on two or more columns, use a formula rule.

Select the whole data range, for example A2:D13.
Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Enter =COUNTIFS($A$2:$A$13,$A2,$C$2:$C$13,$C2)>1 to match on the Name and Email columns.
Click Format, choose a fill and click OK twice.

The dollar signs matter: the columns are locked and the row is relative, so the rule tests each row against the full list.

Count duplicates and flag the second occurrence

A helper column tells you how many times each value appears and which rows are repeats. Type these formulas in column E and fill down.

Goal	Formula in E2	Result
Count how many times the email appears	`=COUNTIF($C$2:$C$13,C2)`	1 for unique, 2 or more for duplicates
Flag the 2nd and later occurrences only	`=COUNTIF($C$2:C2,C2)>1`	FALSE on the first, TRUE on repeats
Flag duplicate rows on Name and Email	`=COUNTIFS($A$2:A2,A2,$C$2:C2,C2)>1`	TRUE only on the repeated row
Total number of duplicate entries	`=SUMPRODUCT((COUNTIF(C2:C13,C2:C13)>1)*1)`	Count of cells that have a match

The expanding range $C$2:C2 is the trick. In row 2 it covers one cell, in row 5 it covers C2:C5, so the formula only counts occurrences above the current row. Filter the column for TRUE and delete the visible rows to remove duplicates while keeping full control.

Extract a unique list

Sometimes you want the original data untouched and a clean list somewhere else.

Advanced Filter, Unique records only

Select the column or range and go to Data > Sort & Filter > Advanced.
Choose Copy to another location and set Copy to to an empty cell such as G1.
Tick Unique records only and click OK.

The copy is static. Run the filter again after the data changes. The full procedure is in Advanced Filter in Excel.

The UNIQUE function (Excel 365 and 2021)

=UNIQUE(C2:C13)                 unique emails, spills down
=UNIQUE(A2:D13)                 unique whole rows
=SORT(UNIQUE(C2:C13))           unique and sorted
=UNIQUE(C2:C13,FALSE,TRUE)      values that appear exactly once

UNIQUE updates itself whenever the source changes, which makes it the best choice for reports and drop-down source lists. Excel 2019 and earlier return #NAME?.

Remove duplicates in a Table and in Power Query

Excel Table: click inside the Table and use Table Design > Tools > Remove Duplicates. The dialog and the result are the same as the Data tab command, and the Table keeps its structure and formulas.

Power Query: select the data, go to Data > From Table/Range, select the columns that define a duplicate, then Home > Remove Rows > Remove Duplicates and Close & Load. The source stays intact and the step is repeatable: click Refresh after the data changes. Note that Power Query is case-sensitive, so lower-case the key columns first with Transform > Format > lowercase if you need case-insensitive matching.

Which method to use

Method	Deletes data?	Updates automatically?	Multi-column?	Versions
Data > Remove Duplicates	Yes	No	Yes	All
Conditional Formatting, Duplicate Values	No	Yes	Single column (formula rule for more)	All
COUNTIF / COUNTIFS helper column	No	Yes	Yes	All
Advanced Filter, Unique records only	No (copies)	No	Yes	All
UNIQUE function	No (new list)	Yes	Yes	365, 2021
Power Query	No (new output)	On refresh	Yes	2016 onward

Worked example: a customer list with repeats

A small customer export has the same person twice, once with a different case and once with a trailing space.

Row	A: Name	B: City	C: Email
2	Ravi Sharma	Delhi	ravi@example.com
3	Anita Roy	Kolkata	anita@example.com
4	ravi sharma	Delhi	RAVI@example.com
5	Meera Nair	Kochi	meera@example.com
6	Meera Nair	Kochi	meera@example.com

Add =TRIM(C2) in D2, fill down, then copy column D and paste as values over column C. Row 5 now matches row 6.
Click in the data and run Data > Remove Duplicates. Untick everything except Email.
Click OK. Excel reports “2 duplicate values found and removed; 3 unique values remain.”

Rows 2, 3 and 5 stay. Row 4 was removed because email matching ignores case, and row 6 was removed because you trimmed the space first. Without the TRIM step Excel would have reported only one duplicate.

Tips and common mistakes

Copy the sheet before deleting. Right-click the sheet tab, choose Move or Copy, tick Create a copy. Remove Duplicates cannot be reversed after you save and close.
Select the whole data, not one column. If you select a single column and choose “Continue with the current selection”, Excel deletes cells in that column only and the rows shift out of line.
Clean first, then deduplicate. TRIM, CLEAN and consistent number formats catch near-duplicates that Excel would otherwise keep.
Sort to control which row survives. Excel keeps the first occurrence, so sort by date or completeness before you run the command.
Merged cells block the command. Unmerge with Home > Merge & Centre before you start.
Highlight before you delete. A Conditional Formatting pass shows you what will go and helps you spot mistakes in the key columns.
Blank cells are duplicates of each other. Two empty emails count as a match, so a column with many blanks may lose rows you wanted to keep.

Errors and how to fix them

Problem	Cause	Fix
Remove Duplicates is greyed out	The sheet is protected, the workbook is shared, or you are editing a cell	Press Esc, unprotect the sheet, turn off sharing
Obvious repeats are not removed	Trailing spaces, text versus number, or hidden characters	Apply TRIM and CLEAN, convert text numbers with VALUE
Too many rows removed	Only one column was ticked, so different people with the same value were merged	Ctrl+Z, tick more columns to define the duplicate
Duplicate Values rule colours every cell	The selection includes a column of repeating categories such as City	Apply the rule to the ID or Email column only
#NAME? from UNIQUE	Excel 2019 or earlier	Use Advanced Filter or Remove Duplicates instead
#SPILL! from UNIQUE	Cells below the formula are not empty	Clear the cells or move the formula

Practice exercise

Open the course practice file Sort-and-Filter.xlsx, copy the data sheet, and work on the copy.

Apply Duplicate Values Conditional Formatting to the first text column and count the coloured cells.
Add a helper column with =COUNTIF($A$2:A2,A2)>1 and filter for TRUE. How many rows are second or later occurrences?
Run Remove Duplicates on the whole range with every column ticked. Note the message, then press Ctrl+Z.
Run it again with only the first column ticked and compare the number removed.
In Excel 365 or 2021, build a unique sorted list of the first column with =SORT(UNIQUE(...)). Check that it matches the result of Advanced Filter with Unique records only.

Key takeaways

Remove Duplicates lives at Data > Data Tools, keeps the first occurrence and deletes the rest.
The columns you tick define what a duplicate is. Tick all for whole-row matching, one for a key column.
Matching ignores case but not spaces or text-versus-number differences, so clean with TRIM first.
Conditional Formatting and COUNTIF show duplicates without deleting anything.
UNIQUE (365/2021) and Power Query give you a repeatable, non-destructive unique list.
Always work on a copy and remember that Ctrl+Z undoes the command.

Related lessons

Frequently asked questions

Does Remove Duplicates keep the first or the last occurrence?

It keeps the first occurrence counting from the top of the range and deletes every later match. If you need the last record, for example the latest order per customer, sort the data by date in descending order before you open the dialog so the newest row sits at the top.

How do I find duplicates in Excel without deleting them?

Select the column and use Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values. Every repeated value gets a fill colour. For a count, add a helper column with COUNTIF and filter for values greater than 1. Both methods leave the data untouched and update automatically when values change.

Why does Excel not remove duplicates that look identical?

The values differ in a way you cannot see. The usual causes are trailing or leading spaces, non-breaking spaces from web pages, and numbers stored as text in one row and as numbers in another. Apply TRIM and CLEAN, convert text to numbers, then run Remove Duplicates again.

Is Remove Duplicates case-sensitive?

No. Excel treats “ravi@example.com” and “RAVI@example.com” as the same value and removes one of them. If you need a case-sensitive comparison, use a helper column with EXACT or SUMPRODUCT, or use Power Query, which matches case exactly by default.

How do I remove duplicates based on two columns?

Open Data, Remove Duplicates, click Unselect All, then tick just the two columns, for example Name and Email. A row is removed only when both values match another row. For a preview, use a COUNTIFS formula rule in Conditional Formatting with the same two columns.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.