Formula Errors

How to Highlight Duplicate Excel Rows Based on Two Columns

Partner Editor Sep 28, 2026 870 views

Question details

The user wants to highlight duplicate rows by comparing values across two specific columns using a conditional formatting formula, but encounters a #NAME? error.

How to Highlight Duplicate Excel Rows Based on Two Columns

Product: Excel
Device & OS: not provided
Scenario: Identifying and highlighting data entries where combinations of two columns (e.g., supplier and material) repeat in a dataset.
Observed behavior: The conditional formatting formula fails to apply properly and returns a #NAME? error, indicating unsupported syntax, a misspelled function, or incorrect argument separators.

Before you start

Verify the exact starting row of your dataset (excluding headers) and ensure your columns contain clean text without leading or trailing spaces that could prevent exact matching.

Solution 1Recommended

Use the COUNTIFS Function in Conditional Formatting

Apply a custom formula rule using COUNTIFS to check for matching pairs across two columns simultaneously.

The built-in 'Duplicate Values' feature only checks single cells or entire rows. To check duplicates based on a combination of exactly two columns, you must use a formula-based conditional formatting rule.

Select your data range

Click and drag to select the entire dataset where you want to highlight rows. For example, select A2:C100, making sure your active cell (where you start the selection) is A2.

Open Conditional Formatting

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

Enter the formula

Choose 'Use a formula to determine which cells to format'. In the formula box, enter =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)>1. Adjust the column letters and row numbers to match your specific dataset.

Apply formatting

Click the 'Format' button, choose your desired highlight color from the 'Fill' tab, click 'OK', and then click 'OK' again to apply the rule.

Use the COUNTIFS Function in Conditional Formatting

Formula Mechanics: The formula counts occurrences where both Column A and Column B match the current row. The '>1' condition triggers the highlight when duplicates are found.

Solution 2

Troubleshoot the #NAME? Error in Formulas

Identify and correct syntax errors, invalid separators, or unsupported function names that cause Excel to reject your formula.

Work Smarter with WPS

Easily Highlight Duplicates with WPS Spreadsheet

WPS Spreadsheet offers powerful data management tools and fully supports advanced conditional formatting formulas like COUNTIFS. You can quickly identify complex duplicates across multiple columns with a familiar, highly compatible interface.

1. Select Data in WPS: Open your workbook in WPS Spreadsheet and highlight the data range (e.g., A2:D100).
2. Access Conditional Formatting: Go to the 'Home' tab and click on the 'Conditional Formatting' icon.
3. Create a New Formula Rule: Select 'New Rule', pick 'Use a formula to determine which cells to format', and input your COUNTIFS formula.
4. Set Highlight Style: Click 'Format', pick a fill color to identify duplicates, and apply the changes.

100% compatibility with Microsoft Excel formulas and conditional formatting rules.Clean, intuitive interface that makes applying custom rules straightforward.Lightweight performance that processes heavy datasets without freezing.Free to use for both basic and advanced spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my conditional formatting formula highlight the wrong rows?

This usually happens if the active cell selected when creating the rule doesn't match the relative row reference in your formula. Ensure the row number without the dollar sign (like $A2) exactly matches the very first row of your selected data range.

Can I highlight duplicates based on three or more columns?

Yes, you can simply expand the COUNTIFS formula to include additional ranges and criteria. For example: =COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2)>1 will check columns A, B, and C simultaneously.

Is there a built-in button to highlight duplicates without formulas?

There is a built-in 'Highlight Cells Rules' > 'Duplicate Values' feature, but it only checks for duplicates in single cells or single columns independently. To find row-level duplicates based on the combination of two columns, you must use a formula.

Does this formula delete the duplicate rows?

No, conditional formatting only changes the visual appearance (like background color) of the cells. If you want to delete them, you need to use the 'Remove Duplicates' feature found under the Data tab.

Excel & Data Problems