Conditional Formatting: Highlight Duplicates in Excel

Yes. In Excel for Microsoft 365 on Windows, select the cells to check, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, pick a format, and select OK. Excel marks values that appear more than once in the selected range; it highlights them without deleting or changing the data.

What Excel Means by a Duplicate

A duplicate is a value that occurs at least twice within the range being checked. For example, if A2:A7 contains North, South, East, North, West, and South, Excel highlights both North cells and both South cells. It does not matter whether the repeated values are next to each other.

The built-in rule checks the selected range as a group. If you select A2:C100, a value in column A can be treated as a duplicate of the same value in column B or C. That may be exactly what you want when checking a list spread across several columns, but it can be surprising when each column represents a different category. To compare each column only with itself, create a separate rule for each column.

Highlighting is a visual warning, not a data-cleaning operation. The original values remain in their cells, and you can still edit, sort, filter, or copy them. Conditional formatting is useful when you want to review repeated entries while preserving the source data.

Apply the Built-In Duplicate Values Rule

For a straightforward list, the built-in rule is usually the quickest choice. In Excel for Microsoft 365 on Windows:

Select the cells to check, such as A2:A500. Include the data cells, not the column heading, unless the heading should be part of the comparison.
Choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
In the dialog, leave Duplicate selected. Choose a preset format, or use Custom Format to set a fill, font, or border.
Select OK. Excel applies the formatting to every matching occurrence in the selected range.

In the Duplicate Values dialog, the second dropdown offers formatting choices, including custom formatting. Choosing Unique instead highlights values that occur only once in the selected range. You can edit or remove the rule later through Home > Conditional Formatting > Manage Rules.

Excel for Mac has the same general Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values route, although the appearance and placement of controls can vary by release. In Excel for the web, select the range and use the Home tab’s Conditional Formatting options; available controls can differ from the desktop app. The rule changes the formatting, not the cell contents, across these versions.

Choose the Right Range and Scope

The selected range determines what Excel counts as a repeat. If a worksheet has a heading in A1 and names in A2:A200, selecting A2:A200 avoids treating a name that matches the heading as a duplicate. For a table with multiple fields, decide whether you mean repeated values in any field or repeated records with all fields matching.

One list: Select the cells in that list, such as A2:A200, to find repeated entries there.
Separate categories: Apply one rule per column if an identical label in different columns should not count as a duplicate.
A multi-column pool: Select the whole block only when repeated values anywhere in that block should be flagged.
Repeated records: Use a formula rule that compares the fields in each row, rather than the built-in rule that checks individual cell values.

For example, a repeated invoice number in a single invoice-number column is a direct duplicate check. A customer name appearing in both a billing-name column and a shipping-name column may not be a duplicate record at all. The rule does not understand the meaning of your columns; it compares cell values within the range you give it.

Use a Formula for More Control

A custom conditional-formatting formula can add conditions, exclude blanks, or check complete rows. To flag repeated nonblank entries in A2:A100, select that range, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the selected range beginning at A2, enter:

=AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1)

This checks each cell against the full list and avoids highlighting empty cells. The dollar signs keep the comparison range fixed as Excel evaluates the rule for each row; A2 remains relative so the rule checks the current cell. Choose Format, set the appearance, then select OK. The formula returns TRUE for every nonblank value that appears more than once.

To flag every occurrence of a complete repeated record, assume the records occupy A2:C100 and all three columns must match. Select A2:C100 and create a formula rule using:

=AND(COUNTA($A2:$C2)>0,COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2)>1)

The formula tests each row’s values in columns A, B, and C together. It highlights the cells in a row when that same combination appears more than once, while the COUNTA condition prevents a wholly empty row from being flagged as a repeated record. Adjust the columns and row limits to match the actual table, and make sure the formula’s first row matches the top row of the selected range.

Understand What Counts as the Same

Duplicate checks are based on cell values, not just what appears on screen. Two numeric cells that display as 10.0 may contain the same underlying number even if their number formats differ. Conversely, values that look alike can be different if one is stored as text and another as a number, or if one contains an extra space. If a result seems wrong, inspect the underlying values and formats rather than relying only on their displayed appearance.

Excel’s COUNTIF-based duplicate logic is not case-sensitive: text such as North and NORTH is generally treated as the same for this purpose. Leading or trailing spaces can make entries differ, so “North” and “North ” may not match. Cleaning imported data or standardizing text can improve the usefulness of a duplicate check. Be careful with cleanup steps: removing spaces or changing text can affect legitimate distinctions in your dataset.

Dates are stored as numbers in Excel, so two cells with the same date value can be duplicates even if they use different date display formats. If the dates represent different times, their underlying serial values may differ even when the cells are formatted to show only the date. This is another reason to check the data’s precision and formatting when a visual comparison is inconclusive.

Fix Common Problems

If nothing highlights, first check the range and confirm that the supposed repeats are actually identical values. A heading accidentally included in the selection, trailing spaces, mixed text and numbers, or a rule applied to the wrong cells can all affect the result. Open Home > Conditional Formatting > Manage Rules to review the rule and its Applies to range.

If unrelated cells highlight, look for a range that includes multiple columns or too many rows. A rule applied to A2:C100 checks matches across that whole block, not just within each column. Also check for existing conditional-formatting rules: overlapping rules or their order can change which formatting is visible. Manage Rules lets you inspect, edit, delete, and reorder rules.

When a formula rule behaves inconsistently, verify that the formula refers to the first cell in the selected range. For a range beginning at B5, a formula written as if the first cell were A2 may evaluate against the wrong locations. Confirm that the comparison range is fixed with dollar signs, while the reference to the current row or cell is relative where needed.

A conditional-formatting rule can cover a fixed address such as A2:A100. If you add records below row 100, those new cells may not be included. Extend the Applies to range in Manage Rules, or use an Excel Table when you want a structured dataset that can expand as rows are added. Always verify that newly entered rows receive the intended rule.

Highlighting Versus Removing Duplicates

Highlighting is for finding and reviewing repeats; Remove Duplicates is for deleting repeated entries from a dataset. To remove records, Excel provides Data > Remove Duplicates. That command changes the data and keeps one occurrence according to the columns you select for comparison. It is not a substitute for highlighting when you need to inspect each case or preserve every original row.

Before using Remove Duplicates, make a copy of the data or save a separate version of the workbook. Check which columns define a duplicate: selecting only an ID column can remove rows that differ in other fields, while selecting every column removes only rows with matching values across all selected fields. Conditional formatting is usually the safer first step because you can investigate the marked entries before making a destructive change.

Another useful option is filtering by color after applying the rule, if you want to review highlighted cells together. Filtering does not resolve the duplicates; it simply narrows what is visible. Whether you highlight, filter, or remove, define “duplicate” in terms of the fields that matter for the task.

Frequently Asked Questions

Does conditional formatting highlight both copies of a duplicate?
Yes. The Duplicate Values rule marks all occurrences in the selected range, not only the second and later copies. If a value appears three times, all three cells are highlighted.

Can I highlight only the second occurrence?
Not with the built-in Duplicate Values rule, which highlights every occurrence. A formula-based rule can distinguish later occurrences by counting from the start of the list to the current row, but the formula must be tailored to the range and whether blank cells should count.

Can Excel highlight duplicates across two columns?
Yes. Selecting both columns makes the built-in rule check for repeated values across the selected cells. If you want comparisons within each column only, apply separate rules to the columns instead.

Does highlighting duplicates delete anything?
No. Conditional formatting changes only how qualifying cells look. To remove repeated records, use Data > Remove Duplicates, and preserve a copy first if you may need the original data.

Why are two entries that look identical not highlighted?
They may contain different underlying values, such as text versus a number, a hidden space, or different date-time precision. Check the cell contents and formatting, and confirm that both cells are inside the rule’s Applies to range.

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.