How to Highlight Duplicate Values in Excel
Highlight duplicates in Excel with Conditional Formatting, catch repeats across two columns or whole rows with a COUNTIF-based rule, and clear the color when done.
To highlight duplicate values in Excel, select the cells you want to check, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values and click OK. Excel shades every value that appears more than once and updates the color on its own as you edit. For repeats across two columns or whole rows, a short formula rule using COUNTIF or COUNTIFS does the same job.
Highlight every duplicate in a range
- Select the range you want to scan — one column, several columns, or the whole table.
- On the Home tab, click Conditional Formatting.
- Point to Highlight Cells Rules, then choose Duplicate Values.
- Leave the first box set to Duplicate, pick a fill from the format list, and click OK.
Switch that first box from Duplicate to Unique to shade the values that appear only once instead. The default is Light Red Fill with Dark Red Text; pick Custom Format for another color. The rule isn’t case-sensitive, so APPLE and apple count as one value — but it doesn’t skip blanks: two or more empty cells in the range count as duplicates of each other and get shaded too. Exclude blank cells from your selection, or use a formula rule like =AND(COUNTIF(range,A2)>1,A2<>"") if that’s a problem. The same path works in Excel for Microsoft 365, 2021/2019, Mac, and the web.
Catch duplicates across two columns or whole rows
To compare two columns, select both and apply the same Duplicate Values rule — it shades any value repeated anywhere in the selection. For whole rows or finer control, build a formula rule: Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, then enter one of these and set a format.
| Goal | Cells to select | Formula to enter |
|---|---|---|
| Repeats within one column | A2:A100 | =COUNTIF($A$2:$A$100,A2)>1 |
| Values in A that also sit in B | A2:A100 | =COUNTIF($B:$B,A2)>0 |
| Entire duplicate rows (A and B match) | A2:C100 | =COUNTIFS($A:$A,$A2,$B:$B,$B2)>1 |
The second formula shines when reconciling two lists; for a fuller walkthrough, see our guide to comparing two Excel columns.
Find duplicates without deleting anything
Conditional formatting only colors cells; it never edits or removes your data, which makes it safe for reviewing. Once you can see the repeats, filter by color to isolate them. If the extras should go, the built-in Data > Remove Duplicates tool handles it — our steps on removing duplicates in Excel cover the cautions first. On very large sheets live rules can lag; if the app locks up, fixing Excel when it stops responding gets you back to work.
Clear or recolor the highlighting
To remove the shading, select the range (or click any cell for the whole sheet) and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells (or Clear Rules from Entire Sheet). To recolor without starting over, open Conditional Formatting > Manage Rules, select the rule, and click Edit Rule.
Highlighting is a review step, not a cleanup — it shows what repeats so you can decide what to keep. Use the Duplicate Values rule for a quick pass, a COUNTIF or COUNTIFS rule for columns or whole rows, and clear the formatting when you’re done.