How to Highlight Duplicates in Excel (Step by Step)
Highlight duplicate values, two-column matches, and entire duplicate rows in Excel using Conditional Formatting — with the exact clicks, the custom-formula rule, and a free no-setup alternative.
Founder of ARCED Foundation, ARCED International, and Solutions of Things Lab (SoTLab). Built FolderManifest to help teams protect file integrity and stay audit-ready.
Quick Answer
Select your range, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, pick a color, and click OK. To highlight whole rows, use a custom COUNTIFS formula rule. To skip the setup entirely, drop the file into the free Spreadsheet Duplicate Finder.
Highlight Duplicates with Conditional Formatting
The built-in rule is the quickest way to color duplicate values:
- Select the cells you want to check.
- On the Home tab, click Conditional Formatting.
- Choose Highlight Cells Rules → Duplicate Values.
- Pick a fill color (or a custom format) and click OK.
Highlight Duplicates in a Single Column
To check just one column, select only that column before applying the rule — for exampleA2:A500. Excel highlights values repeated within that column only, which is what you want when you are checking an email or ID column and don't want matches from other columns to interfere.
Highlight Duplicates Across Two Columns
Select both columns together (for example A2:A500 and C2:C500 with Ctrl), then apply Duplicate Values. Excel treats the combined selection as one pool and highlights any value that appears more than once across both columns — useful for catching an entry that exists in two different lists.
Highlight Entire Duplicate Rows
The standard rule colors cells, not rows. To highlight a whole row when a combination of columns repeats, use a custom formula rule. Select the data range, choose Conditional Formatting → New Rule → Use a formula, and enter:
=COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2) > 1Lock the columns with $ but leave the row relative ($A2) so the rule applies down the range. Any row whose A+B combination repeats gets the highlight.
Change or Remove the Highlight Color
To change the color, go to Conditional Formatting → Manage Rules, select the duplicate-values rule, and click Edit Rule → Format. To remove highlighting entirely, choose Clear Rules → Clear Rules from Selected Cells (or from the whole sheet).
Highlight Duplicates Without Excel
If a rule isn't catching your duplicates — usually trailing spaces or mixed case — or you simply want results in one drag, the free Spreadsheet Duplicate Finder highlights duplicate values automatically and ignores case and surrounding whitespace. It runs in your browser, so your file is never uploaded.
Common gotcha
Conditional Formatting treats "123" (text) and 123 (number) as different, and Ann and ann as different. The online tool normalizes all of these so true duplicates are not missed.
Frequently Asked Questions
How do I highlight duplicate values in Excel?
Select the range, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values, choose a color, and click OK.
How do I highlight duplicate rows, not just cells?
Use a custom formula rule like =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1 applied to the whole row range.
Why is Conditional Formatting not highlighting my duplicates?
Usually trailing spaces, mixed case, or numbers stored as text. Clean with TRIM, or use the online tool, which ignores case and whitespace.
How do I highlight duplicates in Google Sheets?
Use a custom formula such as =COUNTIF(A:A, A1) > 1. See finding duplicates in Google Sheets.
Highlight Your Duplicates Now
No rules, no formulas — drop your spreadsheet into the free, private highlighter.