Need to highlight duplicate values with conditional formatting in Excel? The feature takes only a few clicks, and it colors every repeated entry in a selected range so nothing slips past. This works for customer lists, invoice numbers, email addresses, or any column where repeats cause trouble.
Key Takeaways
- Select the range first, then use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Pick a preset color style or build a custom format from the same dialog box.
- A simple COUNTIF formula can highlight entire rows or ignore the first occurrence.
- Rules can be edited or removed anytime through Manage Rules or Clear Rules.
- Duplicate highlighting is not case-sensitive and works on text, numbers, and dates.
Step-by-Step Guide to Highlight Duplicate Values with Conditional Formatting
The steps below work in Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel 2016. Menu names are the same across these versions.
- Step 1: Open the workbook and select the cells to check. Click the first cell and drag to the last one, or click a column letter to select the whole column.
- Step 2: Go to the Home tab on the ribbon.
- Step 3: In the Styles group, click Conditional Formatting.
- Step 4: Point to Highlight Cells Rules, then click Duplicate Values.
- Step 5: In the dialog box, make sure the first drop-down is set to Duplicate. The second drop-down lets you choose a style such as Light Red Fill with Dark Red Text, Yellow Fill with Dark Yellow Text, Green Fill with Dark Green Text, or Custom Format.
- Step 6: Click OK. Every value that appears more than once in the selected range is now highlighted.
Think of it like a highlighter pen that only marks sentences repeated in a document. Unique entries stay untouched, so the repeats stand out immediately.
Tip: Select only the column or range that matters. If the selection includes unrelated columns, Excel treats values across all selected cells as one pool, so a number in column A can be flagged as a duplicate of the same number in column D.
Highlight Unique Values Instead
In the same Duplicate Values dialog box, change the first drop-down from Duplicate to Unique. Excel then colors the values that appear only once, which is handy when the goal is to spot one-off entries instead.
Use a Custom Color for the Highlight
If the preset styles do not fit, choose Custom Format from the second drop-down in the dialog box. The Format Cells window opens, where the Fill tab sets a background color and the Font tab changes the text color. Click OK twice to apply it.
Highlight Entire Rows That Contain Duplicates
The built-in rule only colors the individual cell. When a whole record should light up, such as an entire order row, a formula-based rule is the way to go.
- Step 1: Select the full data range, for example A2:E100, starting from the first data row.
- Step 2: Click Home, then Conditional Formatting, then New Rule.
- Step 3: Choose Use a formula to determine which cells to format.
- Step 4: Enter the formula =COUNTIF($A$2:$A$100,$A2)>1 in the formula box. This checks column A for repeats and applies the format across the whole row.
- Step 5: Click Format, choose a fill color on the Fill tab, and click OK.
- Step 6: Click OK again to apply the rule.
Tip: The dollar signs matter. The locked column and row references in $A$2:$A$100 keep the checked range fixed, while $A2 lets the row number change as the rule moves down the data.
Highlight Only the Second and Later Occurrences
Sometimes the first entry is the keeper and only the repeats need to be flagged. Use the same New Rule steps with this formula instead: =COUNTIF($A$2:$A2,$A2)>1. Because the range expands as the rule moves down, the first appearance of each value stays uncolored.
Remove or Edit the Duplicate Highlighting
- Step 1: Select the cells with the highlighting, or click any cell on the sheet.
- Step 2: Click Home, then Conditional Formatting, then Manage Rules.
- Step 3: In the Show formatting rules for box, choose This Worksheet to see every rule.
- Step 4: Select the rule and click Edit Rule to change it, or click Delete Rule to remove it.
- Step 5: Click OK to confirm.
To wipe the highlighting quickly, select the range, click Conditional Formatting, point to Clear Rules, and choose Clear Rules from Selected Cells or Clear Rules from Entire Sheet.
Common Problems and Fixes
Values That Look Identical Are Not Highlighted
Hidden spaces are the usual cause. A trailing space makes ‘Smith’ and ‘Smith ‘ count as different entries. Use the TRIM function in a helper column to clean the text, then apply the rule to the cleaned column.
Numbers Stored as Text Are Missed or Mismatched
Numbers formatted as text can behave differently from true numbers. Select the cells, click the warning icon that appears, and choose Convert to Number. The rule then matches them correctly.
Too Many Cells Are Highlighted
This happens when the selection covers more than one column. Excel compares every selected cell against all the others. Clear the rule and reapply it to a single column or to one range at a time.
The Highlighting Does Not Appear After Pasting Data
Pasting can overwrite or split the conditional formatting range. Open Manage Rules, check the Applies to range, and extend it to cover the new rows. Using Paste Special > Values avoids replacing the existing formatting.
Long Numbers Are Flagged as Duplicates by Mistake
Excel only stores 15 significant digits of precision. Long numbers such as 16-digit card or ID numbers can be treated as equal when they differ only after the fifteenth digit. Store these as text, and use a formula such as =COUNTIF($A$2:$A$100,$A2&’*’)>1 to compare them as text.
Frequently Asked Questions
Is the duplicate highlighting in Excel case-sensitive?
No. Excel treats ‘apple’ and ‘APPLE’ as the same value, so both cells are highlighted as duplicates.
Can duplicates be highlighted across two columns?
Yes. Select both columns together and apply the Duplicate Values rule. Excel compares all selected cells as one group and highlights any value that repeats, whether in the same column or the other.
Does conditional formatting remove the duplicates?
No. It only changes how the cells look. To delete repeats, select the data and use Data > Remove Duplicates, ideally after reviewing the highlighted entries.
Will new entries be highlighted automatically?
Yes, as long as they fall inside the range the rule applies to. Typing a repeated value in that range triggers the highlight instantly. Entries outside the range need the Applies to range extended in Manage Rules.
How can duplicates be highlighted in a whole column, including future rows?
Click the column letter to select the entire column before applying the Duplicate Values rule. Every cell in that column, including ones added later, is covered. This can slow very large workbooks slightly.
Can duplicate dates be highlighted?
Yes. Dates are stored as numbers, so the Duplicate Values rule works on them the same way it does on text and numbers.
How can duplicate rows be found based on several columns?
Use a formula rule with COUNTIFS, for example =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1. This flags a row only when both the A and B values repeat together.
Why does the Duplicate Values option appear grayed out?
This usually happens when the worksheet is protected or the workbook is in Protected View. Click Enable Editing, or unprotect the sheet from the Review tab, and the option becomes available again.
Does the highlight print or stay when the file is saved?
Yes. The conditional formatting is saved with the workbook and appears in print and print preview, just like any other cell color.
