How to Highlight Duplicates in Excel
Excel's built-in Duplicate Values rule highlights repeated entries in a single column with two clicks, but it can't compare two separate columns on its own — for that you need a COUNTIF-based formula rule instead.
Quick answer: Select the range, go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, and click OK. To find values that appear in one column but not another, use a formula rule instead: =COUNTIF($B:$B, A2)=0 highlights entries in column A missing from column B. Highlighting only marks cells visually — use COUNTIF or Remove Duplicates if you need an actual count or a cleaned list.
How do I highlight duplicate values in one column?
Use the built-in Duplicate Values rule — it needs no formula and updates automatically as you add or edit data.
1. Select the range (e.g. A2:A200)
2. Home tab → Conditional Formatting → Highlight Cells Rules
3. Click "Duplicate Values"
4. Leave it set to "Duplicate" (or switch to "Unique" to flag one-off values)
5. Choose a format → OKEvery value that appears more than once gets formatted, including the first occurrence — the rule doesn't distinguish "original" from "copy."
How do I highlight only the second and later occurrences?
The built-in rule flags every copy of a duplicate, including the first one. To flag only the repeats, use a formula rule that counts occurrences up to the current row.
Select A2:A200 (active cell A2):
=COUNTIF($A$2:A2, A2)>1The range $A$2:A2 has one side anchored and one side free, so as the rule copies down to row 50, it becomes $A$2:A50 — a running count of how many times that value has appeared so far. The first occurrence returns 1 (not >1, so it stays unformatted); every later occurrence returns 2 or more.
How do I highlight duplicates across two columns?
The built-in Duplicate Values rule only looks within the selected range, so comparing column A against column B needs a formula rule that points at the other column.
Highlight values in A that also exist in B (select A2:A200):
=COUNTIF($B:$B, A2)>0
Highlight values in A that are MISSING from B:
=COUNTIF($B:$B, A2)=0
Highlight values that exist in EITHER column (apply to both ranges):
=COUNTIF($A:$A, A2)+COUNTIF($B:$B, A2)>1This is the same pattern used for a two-list reconciliation — matching an order list against a shipped list, or a budget line against actuals.
Should I highlight duplicates or count them?
Highlighting is for visual review of a modest list; counting is for anything you need to filter, sort, sum, or report on. The table below shows when each approach fits.
| Goal | Best tool |
|---|---|
| Eyeball which rows are duplicated | Conditional formatting (Duplicate Values) |
| Get a number: how many duplicates exist | =COUNTIF($A$2:$A$200,A2) or a pivot table count |
| Filter to only the duplicated rows | Helper column with COUNTIF, then filter >1 |
| Permanently remove duplicate rows | Data → Remove Duplicates |
See removing duplicates in Excel when the goal is a cleaned dataset rather than a visual flag, and custom conditional formatting formulas for the $ anchoring rules behind the COUNTIF recipes above.
What are common mistakes when highlighting duplicates?
Most duplicate-highlighting problems trace back to case sensitivity, trailing spaces, or comparing text to numbers rather than the rule itself.
- Case is ignored: "Apple" and "APPLE" count as duplicates — COUNTIF and the built-in rule are not case-sensitive
- Trailing spaces break matches the other way: "Apple" and "Apple " (with a trailing space) are treated as different values, so real duplicates go unflagged
- Numbers stored as text: 100 and "100" (imported from a CSV) may not match depending on formatting; wrap with
&""or useVALUE()to normalize - Selecting the wrong range for the formula rule: the $A$2:A2 running-count trick only works if the selection actually starts at A2 — starting elsewhere shifts every result
Pro Tip: To highlight duplicate rows based on a combination of columns (e.g. same customer AND same date), use COUNTIFS with one range/criteria pair per column: =COUNTIFS($A$2:$A$200,A2,$B$2:$B$200,B2)>1 (or, equivalently, =SUMPRODUCT(($A$2:$A$200=A2)*($B$2:$B$200=B2))>1). This flags rows that are duplicates on the pair, not on either column alone.