Conditional Formatting for Duplicates in Excel

In Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, choose a format, and click OK. Excel highlights values that occur more than once in the selected range; use a formula rule when you need to ignore blanks, distinguish the first occurrence from later ones, or compare several columns together.

What Duplicate Formatting Does

Conditional formatting changes how a cell looks when its contents meet a rule. It does not delete, merge, or correct repeated data. A highlighted value is simply a signal to review: two customers may share a name legitimately, while two rows with the same invoice number may indicate a data-entry problem.

The built-in Duplicate Values rule compares cells within the range you select. For example, if you select A2:A100, Excel checks each entry against the other entries in that range. It does not automatically compare the values in column A with values in column B. The formatting updates when values change, so it can keep flagging repeats as you edit the list.

It is useful to decide what “duplicate” means before applying a rule. You might mean an identical value in one column, an identical combination of fields such as first and last name, or a repeated record across several columns. Those are different comparisons and may need different rules.

Use Excel’s Built-In Duplicate Values Rule

For a straightforward check of one selected range, the built-in rule is the quickest option:

Select the cells to check, such as A2:A100. Include the data cells, not the header, unless you also want the header included in the comparison.
Choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
In the dialog, leave Duplicate selected, or choose Unique if you want to mark values that appear only once.
Choose a preset format from the list, or select Custom Format to set a different fill, font, or border.
Click OK. Repeated values in the selected range now use the chosen appearance.

Excel’s preset styles are convenient, but a strong fill color can make a busy worksheet difficult to read. A pale fill or a font color with adequate contrast is often easier to scan, especially when the cells already use formatting for other purposes.

In Excel for Mac, the corresponding Conditional Formatting command is available on the Home tab, and the Duplicate Values option is reached through the same Highlight Cells Rules menu. Dialog appearance can differ slightly by platform or update. In Excel for the web, look for Conditional formatting on the Home tab; available rule options can vary, so a formula-based rule may be preferable when the built-in choices do not cover the comparison you need.

How Excel Decides What Counts as a Duplicate

The rule evaluates the cells in its selected range, not the whole worksheet. If you select only A2:A100, a matching entry in A101 will not be considered. If you select A2:B100, the rule evaluates values across that combined selection; it does not treat each row as a complete record or compare column A only with column A.

For ordinary text comparisons, Excel’s duplicate checking is generally not case-sensitive: “North” and “NORTH” are treated as matching. A built-in duplicate rule also does not clean or standardize data before comparing it. Extra spaces, inconsistent punctuation, and different spellings can make entries that look similar on screen count as different values. Conversely, numbers stored in different ways can produce surprising results depending on how they are represented and compared.

Blank cells deserve special care. If your list contains empty cells and you want to be certain they are not marked, use a formula rule that explicitly excludes blanks. This also makes your intention clear to someone who later maintains the workbook. A cell containing a space is not truly empty; remove or normalize such entries if they should be treated as blank.

Use a Formula to Control the Rule

A formula rule gives you more control over which cells qualify. To highlight every repeated, nonblank entry in A2:A100, first select A2:A100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the selected range starting at A2, enter:

With values in A2:A100 and the rule applied to A2:A100, this formula returns TRUE for a nonblank value found at least twice in the range:

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

Choose Format, set the appearance, and confirm the rule. The dollar signs keep the range being counted fixed, while A2 changes as the rule evaluates each cell. If the rule’s Applies to range starts somewhere other than A2, adjust the relative cell reference to the first cell in that range.

To highlight only the second and subsequent appearances—not the first—use a running count. With values in A2:A100 and the rule applied to A2:A100, this formula counts from the first row of the list through the current row:

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

The first occurrence returns a count of 1 and is left unformatted; the second returns 2 and is formatted. This can be helpful when reviewing an import because the original entry remains visually distinct from later repeats. If you sort the list, which occurrence is first can change, because the formula follows the row order.

Compare Complete Records Across Columns

A repeated value in one column does not necessarily mean that the whole record is duplicated. Two people can share a surname, and two orders can have the same date. To flag rows only when a combination of fields repeats, use COUNTIFS so each field must match its counterpart.

For example, suppose column A contains a product code and column B contains a location. Select A2:B100 and create a formula rule. With product codes in A2:A100 and locations in B2:B100, this formula highlights both cells in a row when that product-and-location combination appears more than once:

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

The dollar signs lock the comparison columns and ranges, while the row number adjusts as Excel evaluates each row. If rows with missing product codes or locations should be ignored, use this version instead:

With the same layout and Applies to range of A2:B100, this formula excludes rows where either comparison field is blank:

=AND($A2<>"",$B2<>"",COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1)

Extend the pattern with additional COUNTIFS range-and-criteria pairs when a record is defined by more fields. Keep every comparison range the same size. Before relying on the result, decide whether a partially completed row should count as a record; the blank-excluding version intentionally leaves such rows unhighlighted.

Common Problems and How to Fix Them

The expected match is not highlighted: Check that both cells are inside the rule’s Applies to range. Also inspect spaces, punctuation, and text-versus-number differences. A useful first step is to expand the selected range to cover the complete list.
Too many cells are highlighted: Confirm that the rule is not applied to entire columns when you intended to check only a smaller list. If you need to compare a combination of fields, replace a one-column rule with a COUNTIFS formula.
Blank cells appear in the results: Use the AND condition that tests for a nonblank value, or restrict the Applies to range to the actual data rows.
The formula rule gives unexpected results: The formula’s cell reference must correspond to the first cell in the Applies to range. For A2:A100, use A2 as the relative reference; for a range beginning at D5, the reference should begin with D5.
Entries containing asterisks or question marks match unexpectedly: COUNTIF treats an asterisk and question mark in its criteria as wildcard characters. If these are literal characters in your data, escape them with a tilde in the criterion or use a different comparison approach.
The rule seems to have vanished: Open Home > Conditional Formatting > Manage Rules and check the Show formatting rules for setting. Rules can apply to a selected range, the current worksheet, or other displayed scopes, so confirm both the scope and Applies to address.

Conditional formatting can also be obscured by another rule. In Manage Rules, review the order of rules and whether Stop If True is enabled for a rule above the duplicate rule. If the list is short, removing an obsolete rule and recreating the intended one can be clearer than trying to diagnose several overlapping formats.

Built-In Rule or Formula Rule?

Use the built-in Duplicate Values rule when the task is simply to mark repeated or unique values in one selected range. It is fast to set up and easy for another spreadsheet user to understand. Use a formula rule when you need to exclude blanks, highlight only later occurrences, compare fields together, or apply a condition that the built-in dialog does not offer.

For a one-time cleanup, filtering, sorting, or Excel’s Remove Duplicates command may be more appropriate than highlighting. Removing duplicates changes the data, so make a copy first and check which columns define a duplicate before proceeding. Conditional formatting is safer for investigation because it preserves every row, but it does not decide which record is correct or whether a repeated entry should be removed.

Frequently Asked Questions

Does conditional formatting remove duplicate values?
No. It changes the appearance of cells that meet a rule, but leaves their contents in place. Review the highlighted entries and use a separate cleanup method only if removal is appropriate.

Can I highlight duplicates in two columns separately?
Yes. Apply a Duplicate Values rule to each column’s data range separately, or create separate formula rules. Selecting both columns as one combined range checks repeats across that combined selection, which is not the same as comparing duplicates within each column independently.

How do I highlight duplicates but leave the first match unformatted?
Create a formula rule using a running COUNTIF, such as =COUNTIF($A$2:A2,A2)>1, with values in A2:A100 and the rule applied from A2 downward. Add a nonblank test if empty cells should be ignored.

Can Excel distinguish uppercase and lowercase duplicates?
The built-in duplicate rule and COUNTIF are not case-sensitive for ordinary text. For a case-sensitive comparison, a formula using EXACT can be used; with values in A2:A100 and a rule applied to that range, use =SUMPRODUCT(--EXACT(A2,$A$2:$A$100))>1. This checks for more than one case-exact match.

Will the rule include new rows I add later?
Only if the rule’s Applies to range includes those rows. You can enlarge that range in Manage Rules, or format the data as an Excel table so its range can expand as you add table rows. Check the rule after changing the worksheet structure to confirm its scope.

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.