Excel Conditional Formatting for Duplicates: How It Works

How-to basics · Updated November 22, 2026

Yes. 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 without deleting or changing them.

What Excel means by a duplicate

A duplicate is a value that appears more than once in the range covered by the rule. If you select one column, Excel looks for repeated values in that column. If you select several columns, it checks the values across the selected cells; it does not automatically treat matching records across a row as duplicate records. This difference matters when a table contains names, dates, or other fields that may repeat separately but form a unique record when combined.

The built-in rule is a visual check, not a data-cleaning command. It marks matching values with formatting, while leaving the underlying cells intact. You can still edit, sort, copy, or delete those cells normally. The rule is useful for spotting repeated customer IDs, invoice numbers, email addresses, or entries in a list before deciding what to do about them.

By default, Excel’s Duplicate Values dialog offers a duplicate-versus-unique choice and a preset format. The standard duplicate option highlights every occurrence in a repeated group, including the first occurrence—not just the second and later copies. For example, if the value 104 appears in three cells, all three can be highlighted.

Apply the built-in duplicate rule

Use this method when you want a quick highlight and do not need special rules about blanks, capitalization, or complete rows:

  1. Select the cells to check. To check a column while leaving its heading alone, select the data cells rather than the heading.
  2. On the ribbon, choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  3. In the dialog, leave Duplicate selected, or choose Unique if you want to mark values that occur only once.
  4. Choose a preset format, or use the custom-format option to set a fill, font, or border, then select OK.

The rule applies to the selected area. If you select A2:A100, Excel checks that range; it will not search other columns or other worksheets for matches. To review more than one nonadjacent range, apply a rule to each range or set the rule’s range in the Conditional Formatting Rules Manager.

In Excel for Mac, the equivalent ribbon command is Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Excel for the web also provides conditional-formatting commands from the Home tab, although the ribbon and dialog layout can vary from the Windows desktop version. If a particular dialog option is not shown in the web app, open the workbook in desktop Excel to manage the rule in full.

Choose a range that answers the right question

Before applying the rule, decide what counts as a duplicate in your task. A repeated product name may be normal in a sales table, while a repeated product ID in a list intended to contain one row per product may need investigation. Selecting the whole worksheet data block checks repeated values regardless of which column contains them; that can highlight ordinary repeats such as a shared city or status. It does not mean that the rows themselves are identical.

  • One field: Select the ID or email column when that field alone should be unique.
  • Several fields as independent values: Select multiple columns only when you want to find repeated cell values anywhere within that selected block.
  • Whole records: Use a formula based on the combination of fields, as described below, when a duplicate means that every key field in two rows matches.

For instance, two orders can share the same date but still be different orders. A date-only duplicate rule would highlight both dates. If order identity depends on both date and order number, check the combined fields instead of interpreting a repeated date as a repeated order.

Use a formula for more control

Conditional formatting can use a formula when the built-in dialog does not fit the task. Select the target cells, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula, choose Format to set the appearance, and confirm. In the rule, references should be written for the first cell in the selected range; Excel adjusts relative references for the other cells.

With values in A2:A100 and the rule applied to A2:A100, this formula highlights every nonblank value that appears more than once:

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

The COUNTIF test counts occurrences of the current cell’s value in the full range. The comparison with 1 is true when there is more than one occurrence. The AND condition prevents empty cells from being highlighted as duplicates. Without that condition, multiple blanks may be treated as repeated values by a count-based rule.

With values in A2:A100 and the rule applied to A2:A100, this version highlights only the second and later appearances, leaving the first occurrence unformatted:

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

Here, the counting range begins at A2 and expands as the rule evaluates lower rows. If Ana appears in A2 and again in A6, the first Ana has a count of 1 at its position and is not highlighted; the second has a count of 2 and is highlighted. Keep the starting row in the formula aligned with the first row of the Applies to range. A mismatch can cause Excel to evaluate the wrong cells.

Highlight duplicate records across rows

To find rows that repeat based on several fields, use COUNTIFS. It counts rows meeting all the listed conditions, unlike a duplicate rule on a block of cells, which looks for repeated individual cell values. Choose which columns define a record before applying the rule; including a timestamp or other field that differs between copies will make those rows no longer match.

With the record fields in columns A, B, and C, data in rows 2:100, and the rule applied to A2:C100, this formula highlights all cells in any row whose A, B, and C values match another row:

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

The ranges being counted stay fixed, while the row references such as $A2 adjust as Excel evaluates each row. If the same three-field combination appears twice, the count for each matching row is 2, so the condition is true in both rows. To avoid highlighting rows with blank key fields, add a blank check appropriate to the columns that must be populated. This is a useful way to flag duplicate records for review without assuming that any single column must be unique.

Customize, inspect, and extend the rule

To change a rule after creating it, choose Home > Conditional Formatting > Manage Rules. In the Rules Manager, select the rule and use Edit Rule or adjust its Applies to range. The manager can show rules for the current selection or the worksheet, so check the Show formatting rules for control if the rule you expect is not listed. The Applies to box is especially important when a rule seems to miss cells or appears in the wrong place.

Conditional formatting updates when cell values change, which makes it useful for ongoing lists. However, a rule only evaluates the range it covers. If you append new rows below the Applies to range, the new entries may not be checked. Review the range in Manage Rules after extending a worksheet. Excel tables can make expanding lists easier to manage, but confirm the rule’s coverage rather than assuming every newly added row is included.

Choose a format that remains legible alongside existing cell formatting. A strong fill color is easy to see, but may obscure other visual cues or be difficult for some readers to distinguish. A contrasting font or border can be more suitable in a dense worksheet. Conditional formatting changes the displayed appearance; it does not replace the cell’s original value or permanently recolor the data.

Common problems and what to check

  • Nothing is highlighted: Check that the selected or Applies to range contains the cells you expect and that the values really repeat within that range.
  • Blanks are highlighted: Use a formula with a nonblank test, or inspect whether blank-looking cells contain spaces or formulas returning an empty string.
  • Unexpected matches: Check for extra spaces, inconsistent data types, or similar-looking characters. A value with a trailing space is not necessarily the same stored text as one without it.
  • Only some rows are affected: Inspect the formula’s starting row, absolute references, and Applies to range in Manage Rules.
  • Colors do not appear to change: Another conditional-formatting rule may also apply. Review the rules and their order in Manage Rules; rule priority and settings can affect the final appearance.

Excel’s ordinary text comparisons in COUNTIF are not case-sensitive, so a formula using COUNTIF generally treats Apple and APPLE as matching text for this purpose. If capitalization is important and should distinguish values, the built-in duplicate rule and a basic COUNTIF test may not meet that requirement; a case-sensitive formula requires a different approach. Also remember that a highlighted value is a prompt to inspect, not proof of an error: repeated values may be intentional.

Conditional formatting versus removing duplicates

Use conditional formatting when you want to identify and review repeated entries while keeping all rows. It is reversible and updates as values change. Use Excel’s Remove Duplicates command only when you have decided that repeated records should be deleted. On Windows, select the data and choose Data > Remove Duplicates. Excel then offers columns to include in its comparison. The command removes duplicate rows from the selected data; it is not merely a visual marker, and deleting rows is a change to the data.

Before removing anything, make a copy of the data or ensure the workbook can be recovered. Confirm the comparison columns carefully: selecting only a name column may remove rows that share a name but differ in other details. A safer review workflow is to highlight or filter likely duplicates, verify which records are truly redundant, and only then remove confirmed copies if that is the desired outcome.

Frequently Asked Questions

Does Excel highlight both copies of a duplicate?
Yes. The built-in Duplicate Values rule normally formats every occurrence in a repeated group, including the first one. Use a formula that counts from the first row through the current row if you want to flag only later occurrences.

Can Excel highlight duplicates in one column only?
Yes. Select the data cells in that column and apply the Duplicate Values rule. The comparison is limited to the selected range, so a match elsewhere in the worksheet will not count.

Can I highlight duplicates without highlighting blank cells?
Yes. A custom conditional-formatting formula can test that a cell is nonblank before counting it. For a range beginning in A2, use a formula such as the nonblank COUNTIF example above and make sure the Applies to range also begins in row 2.

Does conditional formatting delete duplicate values?
No. It only changes how qualifying cells look. To delete repeated rows, use Data > Remove Duplicates after checking the comparison columns and confirming that removal is appropriate.

Can the rule find duplicate rows rather than repeated cell values?
Not with the basic Duplicate Values rule by itself. Use a COUNTIFS-based formula that checks the combination of fields defining a record, then apply it across the rows you want to highlight.

Related

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.