Highlight Duplicate Rows in Excel When Both Columns Must Match

Define the duplicate record before selecting a rule

Use Excel 2016 or later desktop editions for this example. Put the sample in A1:B9 with order and sku headers. The record has exactly two fields for this exercise. O-01 with P-A is repeated, but O-01 with P-B is a different record. O-02 with P-A is also different even though its product has appeared before.

This definition matters when adapting the guide to wider data. If a real record is identified by order, product, and line number, comparing only the first two fields would flag legitimate lines. Write down the complete identity rule before implementing formatting. Here incomplete rows are excluded from duplicate highlighting and should be reviewed separately as missing-data issues.

The main rule ignores letter case and assumes both identifier columns contain Text values or the intentionally empty cells shown here. Import numeric-looking identifiers as Text when their spelling matters. Review source error values before applying the rule; a formula error is not evidence that no duplicates exist.

Apply one formula to the complete two-column range

Select A2:B9, starting from A2 so that it is the active cell. In desktop Excel, choose Home > Conditional Formatting > New Rule and the option to use a formula to determine which cells to format. Enter the formula below, select a simple fill color, and confirm the rule. Open Manage Rules to verify Applies to is =$A$2:$B$9.

The formula tests each row against all eight source rows. Each source row contributes to the count only when both fields equal the current row’s fields. A count above one means that the current record has another occurrence. The same true-or-false decision should color both cells of a record, which is why the active row and the locked column references are important.

=AND($A2<>"",$B2<>"",SUMPRODUCT(--($A$2:$A$9=$A2),--($B$2:$B$9=$B2))>1)

Check every highlighted row against the fixture

Rows 2 and 4 should be highlighted together, as should rows 6 and 7. Row 3 must remain unhighlighted despite sharing O-01 with other rows. Row 5 must remain unhighlighted despite sharing P-A with other rows. These two negative cases are essential: they expose a rule that accidentally counts duplicates in either column instead of matching the pair.

Rows 8 and 9 each lack one required field, so the AND checks keep them outside this duplicate rule. They are not certified clean records merely because they have no fill. Use a separate missing-data check if the workflow requires complete fields. The expected-results download includes a boolean for every worksheet row so the visual result can be audited precisely.

Choose whether to highlight every occurrence or only later ones

When reviewing duplicates, highlighting every member of a repeated group can help a person compare the records. When marking extra copies for review, a later-occurrence rule may be more convenient. For that version, replace the fixed range endings with the current row, as shown below, and keep the same Applies to range.

The expanding comparison range begins at row 2 and stops at the row currently being formatted. The first O-01/P-A record has only one occurrence so far; row 4 has two. The same reasoning makes row 7 the later O-03/P-C copy. This is based on current worksheet order, not a promise that the earliest date or newest version is retained.

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

Keep literal symbols and case rules explicit

The primary formula uses direct comparisons rather than criterion strings, so a literal asterisk in a code is not intentionally used as a wildcard pattern. This is useful when product codes can contain punctuation. A COUNTIFS-based alternative is concise for restricted clean codes, but its criteria syntax needs care when asterisk, question mark, or tilde are valid identifier characters.

The sample uses uppercase identifiers and does not require a case distinction. If your identifier system treats letter case as meaningful, use the EXACT-based comparison below and test that policy with an explicit mixed-case fixture before rolling it out. Keep spaces unchanged until you have decided whether they are accidental; silently trimming codes can merge records that the source considers different.

=AND($A2<>"",$B2<>"",SUMPRODUCT(--EXACT($A$2:$A$9,$A2),--EXACT($B$2:$B$9,$B2))>1)

Maintain the range and avoid turning review color into deletion

When appending records after row 9, update both comparison ranges and the Applies to range. Check Manage Rules after copying cells from another workbook, because extra rules or shifted references can change which fill appears. Keep the primary rule bounded to the real data rather than running whole-column pair comparisons across every cell in a large workbook.

Highlighting supports review; it is not a deletion decision. Preserve the source and establish which record to retain before removing anything. The fixture was checked with independent pair counting and native Excel conditional-formatting rules in version 16.0, build 5569.0, on September 13, 2026. Both columns highlighted rows 2, 4, 6 and 7 for all occurrences, and rows 4 and 7 for later occurrences. Rule creation used Excel’s object model; ribbon steps were not exercised. Test your own identifier and missing-data policies before using the rule operationally.