To highlight unique duplicates in Excel, use Conditional Formatting with a formula that counts occurrences: select your data range, go to Home > Conditional Formatting > New Rule > Use a formula, enter =COUNTIF($A$2:$A$100,A2)>1 (adjusting the range to match your data), and set a fill color. This highlights every cell that appears more than once—the duplicates—while leaving truly unique values unformatted. For a more advanced approach that highlights only the duplicate values themselves (not the first occurrence), use =COUNTIF($A$2:$A2,A2)>1 in the same rule.
Why Highlight Duplicates at All?
Duplicate data is a common problem in spreadsheets. It can come from merging lists, manual entry errors, or importing data from multiple sources. Highlighting duplicates helps you quickly identify and review them before cleaning your data. Without a visual cue, you might miss a repeated customer ID, a duplicated invoice number, or two identical entries in a mailing list. Excel’s built-in tools and conditional formatting give you a fast, non-destructive way to see exactly where duplicates exist.
Method 1: Conditional Formatting with COUNTIF
The most flexible way to highlight duplicates is using a formula-based rule. This works on any version of Excel for Microsoft 365, Excel 2021, 2019, and earlier. Here’s the step-by-step:
- Select the range you want to check (e.g., A2:A100). If your data has headers, start from the first data cell.
- Go to Home > Conditional Formatting > New Rule.
- Choose Use a formula to determine which cells to format.
- In the formula box, enter
=COUNTIF($A$2:$A$100,A2)>1(adjust the range to match your selection). - Click Format, pick a fill color (e.g., yellow), and click OK twice.
This formula counts how many times the value in A2 appears in the entire range. If the count is greater than 1, the cell is formatted. The dollar signs ($) lock the range so the formula works correctly for each cell in the selection. On a Mac, the steps are identical—just use the ribbon menu.
Method 2: Highlight Only the Second and Later Occurrences
Sometimes you want to keep the first occurrence of a duplicate and only highlight subsequent ones. This is useful when you want to remove the extra copies but keep one. Use this formula instead:
=COUNTIF($A$2:$A2,A2)>1
Notice the range starts at $A$2 (absolute) but ends at $A2 (relative). As the formula is applied down the column, the range expands. For the first occurrence of a value, the count is 1, so it’s not highlighted. For the second, third, etc., the count is >1, so they are highlighted. This is a classic technique for identifying rows to delete.
Using the Built-In Duplicate Rule
Excel also has a one-click rule: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights all duplicates, including the first occurrence. It’s simpler but less flexible—you can’t customize the logic beyond choosing duplicate or unique. This rule works on both Windows and Mac, and in Excel for the web. However, it doesn’t allow you to highlight only the second occurrence or to apply more complex conditions (like duplicates in one column based on another column’s value).
How to Choose the Right Method
- Use the built-in rule if you just need a quick visual of all duplicates and don’t need to distinguish first occurrences.
- Use the COUNTIF formula if you need to highlight only the duplicate values (not the first), or if you need to base the duplicate check on a combination of columns (e.g., highlight rows where both name and date match).
- Use a formula with multiple conditions if your duplicate definition is more complex, such as duplicates in column A only when column B equals a certain value.
For example, to highlight duplicates in column A only when column B contains “Yes”, use =AND(COUNTIF($A$2:$A$100,A2)>1,$B2="Yes").
Common Problems and Fixes
One frequent issue: the formula doesn’t work because the range in the formula doesn’t match the selected range. Always ensure the range in COUNTIF matches the entire data area, and that the first cell reference (A2) matches the active cell in your selection. If you select A2:A100, the active cell is usually A2, and the formula should reference A2. If you select starting from a different cell, adjust accordingly.
Another problem: the highlight doesn’t appear. Check that the rule is applied to the correct range in the Applies to box in the Conditional Formatting Rules Manager. Also, ensure the formula returns TRUE (you can test by entering it in a cell).
A third issue: you want to highlight entire rows, not just cells. Use a formula like =COUNTIF($A$2:$A$100,$A2)>1 and apply it to the whole row range (e.g., A2:F100). The dollar sign before the column letter locks the column, but the row reference is relative, so each row’s formatting is based on column A.
Comparing with Other Tools
Excel’s Remove Duplicates command (Data > Remove Duplicates) deletes duplicate rows permanently, which is different from highlighting. Highlighting is non-destructive—you can review and decide. If you need to count duplicates, use =COUNTIF in a helper column. For more advanced analysis, the UNIQUE function (in Excel 365) can list unique values, but it doesn’t highlight. Conditional formatting is the only built-in way to visually mark duplicates in place.
Costs and Safety
Conditional formatting is free and built into Excel—no add-ins required. It’s safe because it only changes the visual appearance, not the data itself. However, be cautious when using Remove Duplicates—it permanently deletes rows, so save a copy of your workbook first, and note that it cannot be undone with Ctrl+Z if you close the file. Always verify your range before deleting.
Frequently Asked Questions
How do I highlight duplicates in two columns?
To highlight duplicates across two columns (e.g., find pairs of values that appear together in both columns), use a formula like =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1. This counts rows where both column A and column B match the current row’s values. Apply it to the combined range (e.g., A2:B100).
Can I highlight unique values instead of duplicates?
Yes. In the built-in rule, choose Unique instead of Duplicate. With a formula, use =COUNTIF($A$2:$A$100,A2)=1 to highlight values that appear only once.
Why is my conditional formatting not working on a large dataset?
Conditional formatting works fine on large datasets, but if you use whole-column references like =COUNTIF(A:A,A2)>1, it can slow down Excel because it processes millions of cells. Use a limited range (e.g., A2:A10000) for better performance.
Does this work in Google Sheets?
Yes, the same COUNTIF formula works in Google Sheets. Use Format > Conditional formatting > Custom formula is, and enter the same formula. The interface is slightly different, but the logic is identical.
How do I highlight duplicates based on case sensitivity?
By default, COUNTIF is not case-sensitive, so “Apple” and “apple” are considered duplicates. To make it case-sensitive, use a helper column with =EXACT(A2,A3) or use an array formula like =SUMPRODUCT(--EXACT($A$2:$A$100,A2))>1 in conditional formatting. This is more advanced but works.
Related
- Excel Built-In Repair Tool: What It Does and How to Use It
- Excel Budget Audit Template: How to Build One
- Excel Browser Power Query: What It Is & How to Use
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Nested IF Statements in Excel: Examples, Limits, and TipsNested IF statements in Excel put one IF function inside another, letting a formula choose among several outcomes.…
- Excel Sum Multiple Columns: Formulas, Examples, and TipsTo sum several columns in Excel, use =SUM(B2:D10) for one continuous block of cells. For separate columns or…
- Excel Filter Multiple Criteria in One Column: How It WorksTo filter one Excel column for several allowed values, select the column’s filter arrow and check each value…
- Excel Transpose Bottom to Top: Formulas and Fixes GuideTo transpose a vertical list from bottom to top into a horizontal row in Excel for Microsoft 365,…
- How to Add Parentheses in Excel Formulas CorrectlyTo add parentheses in an Excel formula, edit the cell, type an opening parenthesis where you want a…
- Excel Conditional Formatting If Not a Formula?Excel conditional formatting does not require a formula; you can apply formatting based on cell values, text, dates,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.