Formatting Issues

How to Highlight Duplicate Combinations in Excel Columns A and B

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to highlight cells in column C whenever the specific combination of values in columns A and B appears more than once, without altering the original data in columns A or B.

Product: Excel
Device & OS: not provided
Scenario: Identifying and flagging duplicate data entries that share the same combination of criteria across two distinct columns.
Observed behavior: The goal is to visually display duplicate indicators in a separate column (Column C) using conditional formatting, preserving the layout of the original columns.

Before you start

Ensure your dataset does not contain hidden leading or trailing spaces, as these can cause Excel to treat otherwise identical combinations as unique.

Solution 1Recommended

Use Conditional Formatting with the COUNTIFS Formula

Apply a formula-based conditional formatting rule to dynamically check multiple criteria across columns and flag duplicates.

Using the COUNTIFS function inside a Conditional Formatting rule allows you to count how many times a specific combination occurs in your dataset. If the count is greater than 1, the rule triggers the formatting.

This method is highly effective because it evaluates combinations dynamically and places the visual alert in an independent column, ensuring your primary data remains unaffected.

Select the Target Range

Highlight the cells in column C where you want the formatting to appear (e.g., C2:C1000). Ensure C2 is the active cell in your selection.

Open Conditional Formatting

Navigate to the 'Home' tab on the Excel ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Enter the COUNTIFS Formula

Choose the option 'Use a formula to determine which cells to format'. In the formula box, enter: =COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2)>1

Apply Formatting

Click the 'Format' button, switch to the 'Fill' tab, and choose your preferred highlight color. Click 'OK' twice to apply the rule to your selected range.

Adjusting Cell Ranges: Make sure to adjust the row numbers in the formula (e.g., $A$2:$A$1000) so they precisely match the data range of your spreadsheet. Absolute references ($) are crucial for the ranges, while relative references (like $A2) are needed for the criteria.

Powerful Data Management

Highlight Duplicate Combinations Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting formulas like COUNTIFS, allowing you to highlight complex duplicates seamlessly without any steep learning curve.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you wish to format.
2. Select the destination column: Highlight the cells in column C where you want the duplicate markers to appear.
3. Apply Conditional Formatting: Go to the Home tab, click Conditional Formatting > New Rule, and select 'Use a formula to determine which cells to format'.
4. Input formula and format: Enter the COUNTIFS formula, select a distinct fill color under the Format settings, and click OK to instantly highlight duplicates.

100% compatibility with Microsoft Excel formulas and formatting rulesFree, lightweight, and fast alternative for heavy spreadsheet managementFamiliar user interface ensuring zero friction when migrating from OfficeAdvanced data visualization tools available out of the box

microsoft office alternative - wps office

Frequently Asked Questions

Can I highlight the entire row instead of just column C?

Yes. Instead of selecting just column C, select your entire data range (e.g., A2:C1000). Apply the same COUNTIFS formula, but ensure the column references for your criteria are locked with a dollar sign (like $A2 and $B2) so the formatting spans across the whole row.

Why isn't my COUNTIFS formula working properly?

Check that your range references are absolute (using $ signs like $A$2:$A$1000) so they don't shift down as the rule is applied. Also, verify that the criteria references are relative to the row (like $A2). Additionally, hidden spaces or formatting inconsistencies between cells can cause the formula to miss identical values.

How do I check for duplicate combinations across three columns?

You can easily expand the COUNTIFS formula by adding another range and criteria pair. For example, to check columns A, B, and D, use: =COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2,$D$2:$D$1000,$D2)>1.

Document & File Issues