On this page · 10 sections
- Which duplicate method should you use?
- Worked example: a customer list with repeats
- Method 1: Highlight duplicate values with conditional formatting
- Method 2: Find duplicates with a COUNTIF formula
- Method 3: Remove duplicates (or filter them out safely)
- Method 4: List unique or duplicate values with UNIQUE
- Why Excel misses (or invents) duplicates
- For IT teams and businesses
- How eCorpIT can help
- References
Summary. To find duplicate entries in Excel, select the column, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, pick a format and select OK: every repeated value is highlighted and nothing changes. When you are ready to delete, Data > Remove Duplicates keeps the first occurrence and permanently deletes the rest, so copy the data first. To count copies, or flag only the second and later copy, use a helper column with =COUNTIF($A$2:$A$9,A2) or =COUNTIF($A$2:A2,A2)>1. To leave the source untouched, =UNIQUE(A2:A9) spills a clean list into new cells. Highlighting, COUNTIF and Remove Duplicates work in Excel 2016 through Microsoft 365 on Windows, in Excel for Mac and on the web. UNIQUE needs Microsoft 365, Excel 2024 or 2021, Excel for the web or the mobile apps. Excel 2019 and 2016 lack it and left support on 14 October 2025; Excel 2021 reached its retirement date on 13 October 2026. Most missed duplicates come from stray spaces, or from Remove Duplicates comparing what a cell displays rather than the value it stores.
About this article. For anyone who cleans lists in Excel. We checked every menu path, formula and limit on 15 November 2026 against Microsoft Support, and support dates against Microsoft's lifecycle pages. The steps match Microsoft 365 Current Channel Version 2610 (Build 20527.20196, 10 November 2026) and Office for Mac Version 16.115 (10 November 2026); labels can differ slightly in older versions and other channels. No vendor paid for inclusion. Researched with AI assistance and reviewed by our editorial team.
Which duplicate method should you use?
The table summarises Microsoft's guidance for each method.
| Method | What it does | Changes your data? | Where it works |
|---|---|---|---|
| Duplicate Values highlighting | Colours every value that appears more than once | No | Windows, Mac and web |
| COUNTIF helper column or rule | Counts copies, or flags only the second and later copy | No | All versions on Windows, Mac and web |
| Remove Duplicates | Deletes repeat rows and keeps the first | Yes, though Undo reverses it straight away | Windows, Mac and web |
| Advanced Filter with Unique records only | Hides duplicates or copies a clean list elsewhere | No | Windows and Mac desktop apps |
| UNIQUE function | Spills a deduplicated list into new cells | No | Microsoft 365, 2024, 2021, web and mobile |
Microsoft's advice is to filter for unique values or highlight them first, confirm the result, and only then remove duplicates.
Worked example: a customer list with repeats
Type the header Customer in A1 and the eight names below in A2:A9. In B2 enter =COUNTIF($A$2:$A$9,A2), in C2 enter =COUNTIF($A$2:A2,A2)>1, then fill both down to row 9.
| Row | Customer (A) | Count (B) | Repeat copy (C) |
|---|---|---|---|
| 2 | Ana | 2 | FALSE |
| 3 | Ben | 1 | FALSE |
| 4 | Cara | 3 | FALSE |
| 5 | Ana | 2 | TRUE |
| 6 | Dev | 1 | FALSE |
| 7 | Cara | 3 | TRUE |
| 8 | Eli | 1 | FALSE |
| 9 | Cara | 3 | TRUE |
On this data:
- Duplicate Values highlighting colours 5 cells (A2, A4, A5, A7 and A9): every copy, including the first.
- Column B above 1 means a duplicate. Column C is TRUE only for the 3 extra copies (rows 5, 7 and 9), the rows Remove Duplicates would delete.
- Remove Duplicates on A2:A9 removes 3 rows and leaves 5 names.
=UNIQUE(A2:A9)returns those 5 names without touching column A;=UNIQUE(A2:A9,,TRUE)returns the 3 that appear once (Ben, Dev and Eli).
Method 1: Highlight duplicate values with conditional formatting
This is the quickest way to see duplicates, and it changes nothing.
Excel for Windows (Microsoft 365, 2024, 2021, 2019, 2016):
- Select the cells you want to check, for example A2:A9.
- Select Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- In the box next to values with, pick a format, then select OK.
Excel for Mac (Microsoft 365, 2024, 2021): select the cells, then on the Home tab select Conditional Formatting, point to Highlight Cells Rules and select Duplicate Values. Choose the options in the New Formatting Rule dialog and select OK.
Excel for the web: select the cells, then select Home > Styles > Conditional Formatting > Highlight Cell Rules > Duplicate Values (Microsoft's web steps use Cell, singular) and choose a fill, text or border colour.
To highlight values that appear only once, create a Format only unique or duplicate values rule and choose unique. On Windows, go to Conditional Formatting > Manage Rules > New Rule; on a Mac, select New Rule and set Style to Classic.
Microsoft lists two limits: the rule can't format the Values area of a PivotTable, and it reads * and ? as wildcards, so codes containing them can be matched wrongly. Check a PivotTable's source data instead, and use Method 2's exact-match formula for such codes.
Method 2: Find duplicates with a COUNTIF formula
COUNTIF counts the cells in a range that meet one criterion. Use it in a helper column you can sort or filter, or inside a conditional formatting rule.
Helper column formulas (enter in B2 and fill down):
=COUNTIF($A$2:$A$9,A2)returns how many times the value in A2 appears.
=COUNTIF($A$2:$A$9,A2)>1returns TRUE for every duplicate, including the first copy.
=COUNTIF($A$2:A2,A2)>1returns TRUE only for the second and later copies, because the range grows one row at a time.
Highlight whole rows that contain a duplicate. Select the whole table, for example A2:C9, then create a formula rule with =COUNTIF($A$2:$A$9,$A2)>1. The $ before A keeps the check on column A while the colour spreads across the row.
- Windows: Home > Conditional Formatting > New Rule, select Use a formula to determine which cells to format, type the formula, select Format, pick a fill, then select OK.
- Mac: Home > Conditional Formatting > New Rule, set Style to Classic, change the rule type to Use a formula to determine which cells to format, type the formula and choose a format in the Format with box.
- Web: Home > Styles > Conditional Formatting > New Rule, set Rule Type to Formula, type the formula and select Done.
Duplicates across two or more columns. When a duplicate means the same first name and the same last name, use COUNTIFS, which accepts up to 127 range and criteria pairs: =COUNTIFS($A$2:$A$9,$A2,$B$2:$B$9,$B2)>1.
Microsoft's COUNTIF page lists the traps. COUNTIF ignores case, so ANA and Ana match. It returns wrong results for strings longer than 255 characters. Stray spaces and nonprinting characters make look-alike values differ, so clean the column with TRIM or CLEAN first. And * and ? in the data act as wildcards, as in the Duplicate Values rule. TRIM also leaves the nonbreaking space (character 160) common in text copied from web pages.
An exact match for codes that contain wildcards. Compare cells directly with =SUMPRODUCT(($A$2:$A$9=$A2)*1)>1, the compare-and-multiply pattern Microsoft's SUMPRODUCT page uses. Alternatively, type a tilde (~) before each * or ? to match it literally.
Method 3: Remove duplicates (or filter them out safely)
Remove Duplicates deletes data permanently, so Microsoft advises copying the range or table to another sheet or workbook first.
Excel for Windows:
- Select the range, or click any cell inside a table.
- Select Data > Remove Duplicates (in the Data Tools group).
- Under Columns, tick the columns that define a duplicate. Select All and Unselect All speed this up.
- Select OK. A message says how many duplicate values were removed or how many unique values remain; select OK to close it.
Excel for Mac: select the range, choose Data > Remove Duplicates, tick the columns, then select Remove Duplicates.
Excel for the web: select the range, choose Data > Remove Duplicates, clear any columns that shouldn't be compared, then select OK. Undo (Ctrl+Z) brings the data back.
The ticked columns form the key, but the whole row is removed, including columns you didn't tick, and the first occurrence is kept. The comparison uses what the cell displays: the same date shown as 3/8/2006 and as Mar 8, 2006, or 1 formatted as 1.00 in one cell and 1 in another, counts as two values. Remove any outline or subtotals first, because Remove Duplicates won't run on that data; the counts it reports can include empty cells and spaces.
The non-destructive alternative (Windows and Mac desktop): select the range, choose Data > Advanced (in Sort & Filter), pick Copy to another location, enter a cell in the Copy to box, tick Unique records only and select OK. The original list stays as it was. Microsoft's Excel for the web steps cover only Remove Duplicates, so use UNIQUE there instead.
Method 4: List unique or duplicate values with UNIQUE
UNIQUE returns a deduplicated list that spills into the cells below the formula and recalculates when the source changes.
=UNIQUE(A2:A9)returns each name once (5 results in the example).
=UNIQUE(A2:A9,,TRUE)returns only the values that appear exactly once (3 results).
=UNIQUE(FILTER(A2:A9,COUNTIF(A2:A9,A2:A9)>1))lists just the duplicated values (Ana and Cara).
=COUNTA(A2:A9)-COUNTA(UNIQUE(A2:A9))counts the extra copies: 8 entries minus 5 unique names gives 3.
Version check. Microsoft's UNIQUE page lists Excel for Microsoft 365, Excel 2024 and Excel 2021 on Windows and Mac, Excel for the web (Microsoft365.com) and Excel for iPad, iPhone and Android. The FILTER page lists the same desktop and mobile editions. Neither function is in Excel 2019 or 2016; use Methods 1 to 3 there.
If a cell in the output range already holds data, you get a #SPILL! error until you clear it. Spilled formulas can't sit inside an Excel table, so put them beside it; if the source is a table, structured references make the list resize as rows are added. A short, clear table name keeps those formulas readable: see how to name a table in Excel.
Why Excel misses (or invents) duplicates
| Symptom | Likely cause | Fix |
|---|---|---|
| Two values look the same but aren't flagged | Leading or trailing spaces, or nonprinting characters | Check a helper column with =TRIM(CLEAN(A2)) instead |
| Remove Duplicates keeps the same number or date twice | It compares displayed values, and the formats differ | Apply one number format to the whole column first |
| Unrelated codes flagged as duplicates | An asterisk or question mark in the data acts as a wildcard | Use the SUMPRODUCT exact-match formula from Method 2 |
| Remove Duplicates won't run | Data is outlined or has subtotals | Remove the outline and subtotals, then retry |
| No highlight in a PivotTable | Duplicate rules don't apply to the Values area | Check the source data instead |
| UNIQUE not recognised | Excel 2019 or 2016 | Use COUNTIF or Advanced Filter, or upgrade |
Sources: Microsoft's duplicates, conditional formatting, COUNTIF and UNIQUE pages.
For IT teams and businesses
Duplicate rows cost money downstream: invoices sent twice, inflated CRM counts. Make deduplication a defined step before any import, record which columns define a duplicate, and keep the raw export for audit.
Match shared templates to your Excel estate. Excel 2016 and 2019 reached end of support on 14 October 2025, and Excel 2021 (Windows and Mac) reached its retirement date on 13 October 2026, so Microsoft supports none of them now. Staff on 2016 or 2019 can't use a template built on UNIQUE and FILTER; COUNTIF rules work everywhere. If the same cleanup runs weekly, a repeatable query or script beats a manual Remove Duplicates. Upstream, drop-down lists with data validation stop bad entries at the source, and numbering rows automatically gives every record a stable ID before you deduplicate.
How eCorpIT can help
eCorpIT is a Gurugram-based technology organisation, founded in 2021, assessed at CMMI Level 5 and MSME certified, with senior-led engineering teams working across AWS, Microsoft and Google platforms. Our data and analytics services team replaces recurring spreadsheet clean-ups with repeatable data pipelines and reporting. Talk to us at /contact-us/.
Last updated: 15 November 2026.
