Conditional Formatting Duplicate Values in Excel

In Excel for Microsoft 365 on Windows, select the cells to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a format and select OK; Excel highlights values that occur more than once in the selected range.

What the duplicate-values rule does

Conditional formatting changes how cells look when their contents meet a rule; it does not edit, delete, or rearrange the data. Excel’s built-in Duplicate Values rule checks the range you select and applies formatting to repeated values. In the rule dialog, you can choose to mark either duplicates or unique values, then pick a preset style or create a custom one.

The rule is useful for spotting repeated customer IDs, invoice numbers, email addresses, product codes, or names. It can also help identify values that appear only once, such as a one-off entry in a list that is expected to contain repeats. It is a visual check, not a complete data-cleaning process: a highlighted value might be a legitimate repeat, and an unhighlighted value might still be wrong because of a typo or inconsistent data.

How to highlight duplicates in Excel

For a basic check, apply the built-in rule to the cells you want Excel to compare. Selecting only the data cells, rather than the header row, avoids treating headings as part of the list.

Select the range, such as A2:A100. For a table, select the column’s data cells.
On the ribbon, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
In the dialog, choose Duplicate from the first list. Choose a formatting preset from the second list, or select Custom Format to set the fill, font, border, or number format.
Select OK. Excel highlights every matching occurrence in the selected range, not just the later copy.

To identify values that occur just once instead, choose Unique in the dialog. To change the style or the rule later, select a cell in the range and go to Home > Conditional Formatting > Manage Rules. Review the rule’s Applies to range as well as its format; the rule can be correct but cover the wrong cells.

What counts as a duplicate

The selection defines the comparison area. If you select A2:A100, Excel looks for repeats within that column range. It does not automatically compare the selected values with another worksheet or with cells outside the selection. If you select a two-dimensional range, the rule checks values across that selected range rather than comparing each row as a separate record.

That distinction matters when a workbook contains multiple lists. For example, applying the rule to two columns together can mark a value that appears once in each column, even if neither column contains a repeat on its own. Apply the rule separately to each column when the lists need independent checks.

Formatting can also affect what appears to be a duplicate. A date may be displayed as 3-Nov while its underlying value includes a year, and a number may be displayed with fewer decimal places than it actually contains. Excel evaluates cell values, not merely the visible characters. Text values can also differ because of leading or trailing spaces, even when they look nearly identical. If the result seems unexpected, inspect the underlying entries and their data types before changing the rule.

Use a formula for more control

The built-in rule is quick, but a formula-based conditional formatting rule lets you control which occurrences are marked and exclude blanks. To add one, select the target range, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula, choose Format, and confirm the rule.

With IDs in A2:A100 and the selected Applies to range set to A2:A100, use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1) to highlight every nonblank ID that occurs more than once. The dollar signs keep the comparison range fixed as Excel evaluates each cell; A2 remains relative so the test shifts down the column. For example, if ID X17 appears in A4 and A9, both cells are highlighted.

With IDs in A2:A100 and the same selected range, use =AND(A2<>"",COUNTIF($A$2:A2,A2)>1) to mark only the second and later occurrences as the list is read from top to bottom. If X17 is in A4 and A9, A4 stays unformatted and A9 is highlighted. This is handy when reviewing a list in which the first occurrence is the one to keep.

With IDs in A2:A100 and the conditional formatting Applies to range set to A2:D100, use =AND($A2<>"",COUNTIF($A$2:$A$100,$A2)>1) to highlight the whole row whenever its ID in column A is repeated. The dollar sign before A locks the test to the ID column; the row number changes for each row. If you want to identify duplicate combinations across two fields, such as a first name in A and a last name in B, a COUNTIFS rule can test both values together.

These formulas use COUNTIF, which is not case-sensitive. If values that differ only by capitalization must be treated as different, a case-sensitive formula is needed. With IDs in A2:A100 and the Applies to range set to A2:A100, use =AND(A2<>"",SUMPRODUCT(--EXACT(A2,$A$2:$A$100))>1). EXACT distinguishes uppercase and lowercase text, while the first test prevents blank cells from being highlighted.

Examples of useful checks

Repeated email addresses: Apply the rule to the email column, excluding the heading. Repeats may indicate duplicate customer records, but first confirm whether shared addresses are allowed.
Invoice numbers: Check the invoice-number column within the relevant period or dataset. A duplicate may be an entry error, but it should be verified against the source records before correcting it.
Repeated rows: If two records count as duplicates only when several fields match, checking one column alone is not enough. Use a formula that tests the identifying fields together, or build a helper key that combines those fields and check that key.

For example, two customers can share a surname without being the same person. Highlighting repeated surnames is therefore a prompt to review records, not proof that one record should be removed. Choose the fields that genuinely define a duplicate for the task at hand.

Common problems and fixes

Nothing is highlighted: Confirm that the selected range contains the values, that the rule is set to Duplicate, and that the Applies to range covers the intended cells. Then check whether values that look alike actually differ because of spaces, text-versus-number storage, or other hidden differences.
Too many cells are highlighted: Check whether you selected multiple columns or included headings. Excel compares across the whole selected range. Apply the rule to a single column or use a formula that tests the intended field.
New entries are not checked: Open Manage Rules and inspect Applies to. If the range ends at row 100, values added below row 100 may be outside the rule. Expand the range or use an Excel Table so new table rows can inherit formatting more conveniently.
Old formatting remains: A cell may have another conditional formatting rule or ordinary manual formatting. In Manage Rules, review the rules affecting the selection and their order. Use Clear Rules only when you mean to remove the relevant conditional formatting.
Blank cells behave unexpectedly: For a formula rule, include a nonblank test such as A2<>"" when empty cells should not count. Also check whether cells that look empty contain spaces or formulas returning an empty string.

When cleaning text, be cautious about changing source data. Trimming spaces or converting text numbers can help make comparisons consistent, but those changes may affect other calculations. Work on a copy or use a separate helper column if you need to normalize values before deciding which records to change.

Conditional formatting versus removing duplicates

Conditional formatting is reversible and leaves every record in place. It is best for reviewing a list, monitoring future entries, or flagging records for investigation. Excel’s Remove Duplicates command is different: it deletes repeated rows or values from the selected data, keeping one occurrence according to the fields chosen. Removing duplicates can discard information, so check the selected columns and make a backup before using it.

Neither method replaces a careful definition of what makes a record unique. A repeated order number might be an error, while repeated product names could be expected. If you are unsure, highlight first, compare the full records, and only then decide whether any entries should be removed.

Managing rules and workbook performance

Conditional formatting rules are stored in the workbook and recalculate as cell values change. A few duplicate-value rules over ordinary ranges are typically straightforward, but many overlapping rules or formulas applied to very large ranges can make a workbook harder to maintain. Keep the Applies to ranges focused on actual data rather than entire columns when practical, and remove obsolete rules after a worksheet’s structure changes.

In Excel for Microsoft 365 on Windows, use Home > Conditional Formatting > Manage Rules to review or edit rules. The Show formatting rules for list lets you inspect rules for the current selection or worksheet. Excel for Mac has the same core duplicate-values feature, although ribbon placement and dialog appearance can differ slightly. In Excel for the web, conditional formatting is available from the Home tab, but some advanced rule-editing options may differ from the desktop app.

Frequently Asked Questions

Does Excel highlight both copies of a duplicate?
The built-in Duplicate Values rule highlights all occurrences in the selected range, including the first one. Use a formula-based rule with a growing COUNTIF range if you want to mark only later occurrences.

Can I highlight duplicates in one column and color the whole row?
Yes. Apply a formula rule to the full row range and lock the column being checked. For example, with IDs in A2:A100 and rows spanning A2:D100, the formula shown above checks each row’s column A value and formats the row when that ID repeats.

Does conditional formatting delete duplicate values?
No. It changes cell appearance only. Use Remove Duplicates if you intend to delete repeated data, and review the selected fields carefully before confirming.

Can I find duplicates without regard to capitalization?
The built-in duplicate rule and COUNTIF formula are not case-sensitive. Values such as ABC and abc are treated as matching by those checks. Use an EXACT-based formula when capitalization must distinguish entries.

Why are two entries that look the same not highlighted?
They may contain hidden spaces, different underlying numbers or dates, or different data types. Compare the actual cell contents and check the range the rule covers; visible formatting alone does not guarantee that two values are identical.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.