Function Problems

How to Compare Two Excel Columns with Duplicate Values

Amos Gikunda Sep 30, 2026 869 views

Question details

The user needs to compare data between two Excel columns to find matches or differences, but the presence of duplicate values complicates the matching process.

How to Compare Two Excel Columns with Duplicate Values

Product: Excel
Device & OS: not provided
Scenario: Comparing two lists of data to verify if they contain the same information and identifying unique or missing entries.
Observed behavior: Duplicate entries make it difficult to confirm one-to-one matches between the two columns, leading to inaccurate or confusing comparison results.

Before you start

Before comparing the columns, ensure that your data is cleaned of leading or trailing spaces using the TRIM function, as invisible spaces can cause false mismatches during the comparison.

Solution 1Recommended

Use Conditional Formatting to Highlight Duplicates and Differences

Conditional formatting provides a quick visual way to identify which values appear in both columns and which are unique.

This built-in feature scans the selected range and applies a color to any value that appears more than once. It is highly effective for a quick visual audit of two columns side-by-side.

Select the Data Range

Highlight both columns containing the data you want to compare. You can do this by clicking and dragging over the cells or selecting the column headers.

Apply Conditional Formatting

Navigate to the 'Home' tab on the ribbon and click on 'Conditional Formatting'. Choose 'Highlight Cells Rules' and then select 'Duplicate Values' from the dropdown menu.

Choose Formatting Style

In the dialog box, select a formatting style (e.g., Light Red Fill with Dark Red Text) to apply to the duplicate values. Click 'OK'. The unhighlighted cells represent the unique values in your lists.

Use Conditional Formatting to Highlight Duplicates and Differences

Handling Duplicates within the Same Column: Keep in mind that this method highlights any duplicate value, even if it repeats within the same column. It is best used when you want a general overview of overlapping data.

Solution 2

Use the COUNTIF Function for Precise Matching

The COUNTIF function helps you count exactly how many times a value from one column appears in the other, making it easier to handle duplicates logically.

Solution 3

Remove Duplicates Before Comparing

If you only need to verify whether the unique items match between both lists, removing duplicates first significantly simplifies the matching process.

Powerful Spreadsheet Editor

Compare and Analyze Data Easily with WPS Spreadsheet

WPS Office provides powerful data analysis tools, including intuitive conditional formatting, built-in duplicate removal features, and full support for advanced comparison formulas like COUNTIF and VLOOKUP. You can effortlessly manage complex datasets and reconcile columns without hassle.

1. Open your Spreadsheet: Launch WPS Office and open your spreadsheet file containing the columns you wish to compare.
2. Use Highlight Duplicates: Select the two columns, navigate to the 'Data' tab, and click 'Highlight Duplicates' to instantly visually identify overlapping entries.
3. Clean Your Data: Alternatively, utilize the 'Remove Duplicates' feature under the Data tab to sanitize your lists before applying detailed comparison formulas.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats without any data loss.Built-in Highlight Duplicates tool for instant visual comparison across columns.Comprehensive support for advanced lookup and counting formulas (VLOOKUP, XLOOKUP, COUNTIF).Lightweight, fast, and completely free to use across Windows, Mac, Linux, and mobile devices.

microsoft office alternative - wps office

Frequently Asked Questions

How can I compare two columns row by row?

To compare values in the exact same row across two columns, you can use the IF function. Type `=IF(A2=B2, "Match", "Mismatch")` in an adjacent blank column and drag the formula down to evaluate each row individually.

Can I compare columns without using any formulas?

Yes, the Conditional Formatting feature allows you to highlight duplicate or unique values across both columns automatically without writing a single formula. Just select both columns and apply the 'Duplicate Values' rule from the Home tab.

Why is VLOOKUP not finding a match when the data looks identical?

This usually happens due to hidden spaces, trailing spaces, or differing data formats (e.g., numbers stored as text). Use the TRIM() function to clean up extra spaces, and ensure both columns share the same cell formatting before running the VLOOKUP.

How do I extract only the unique values from both columns combined?

You can copy the data from both columns and paste it into a single, long column. Then, select that new combined column, navigate to the 'Data' tab, and click 'Remove Duplicates' to leave a single list of entirely unique values.

Excel & Data Problems