Power Query Problems

How to Compare Two Excel Lists for Matches and Unique Items

Anum Mushtaq Oct 23, 2026 868 views

Question details

The user needs to compare two Excel lists to extract exact matches, similar values, and unique items into separate output columns.

How to Compare Two Excel Lists for Matches and Unique Items

Product: Excel
Device & OS: not provided
Scenario: Comparing two distinct datasets to identify data overlaps and differences without relying on complex VBA code.
Observed behavior: The user is looking for an automated solution, initially preferring a macro, to cleanly separate matching and unique data into distinct columns.

Before you start

Ensure both of your Excel lists are formatted as Tables (press Ctrl+T) so they can be easily loaded into the Power Query Editor.

Solution 1Recommended

Use Power Query Joins to Merge and Compare Lists

Power Query is the most efficient, macro-free method to compare two lists by using different types of joins (Inner, Outer, and Anti-joins).

Instead of writing and maintaining complex VBA macros, Power Query provides a visual interface to merge tables.

You can use an Inner Join to find exact matches, a Full Outer Join to see all values side-by-side, and Left/Right Anti-joins to extract items unique to either list.

Load data into Power Query

Select any cell in your first list, go to the Data tab on the ribbon, and click 'From Table/Range'. Once the editor opens, choose 'Close & Load To...' and select 'Only Create Connection'. Repeat this process for your second list.

Merge the queries

Navigate to the Data tab, click 'Get Data', select 'Combine Queries', and then choose 'Merge'. This opens the Merge dialog box.

Select columns to compare

In the Merge window, select your first table from the top dropdown and your second table from the bottom dropdown. Click on the column headers in both preview panes that contain the values you wish to compare.

Choose the Join Kind

Under the 'Join Kind' dropdown, select 'Inner (only matching rows)' to find exact matches, or 'Left Anti (rows only in first)' to extract unique items. Click OK.

Load the final results

In the Power Query Editor, review the resulting dataset. Click 'Close & Load' on the Home tab to output the compared data into a brand new worksheet.

Automated Updates: Power Query automatically saves your steps. If you add new data to your original lists, simply right-click the output table and select 'Refresh' to update the comparisons instantly.

Solution 2

Compare Lists Using Conditional Formatting

If you only need a visual comparison of your data rather than separating them into distinct columns, Conditional Formatting is a rapid alternative.

Free Microsoft Office alternative

Easily Manage and Compare Spreadsheets with WPS Office

While Power Query is a dedicated Microsoft Excel tool, WPS Office provides a highly compatible, free, and lightweight alternative for managing complex datasets. It fully supports standard data analysis functions like VLOOKUP, MATCH, and Conditional Formatting for effortless list comparisons, offering a familiar interface that makes data processing seamless.

1. Download and Install: Download WPS Office for free from the official website and complete the quick installation.
2. Open your Spreadsheet: Open your existing Excel worksheet directly in WPS Spreadsheet with perfect formatting retention.
3. Compare Data: Utilize built-in Lookup functions or the 'Manage Duplicates' tool under the Data tab to compare your lists effortlessly.

Fully compatible with Microsoft Excel formats including .xlsx, .xls, and .csv.Familiar, easy-to-use interface with absolutely no steep learning curve.Built-in advanced formulas and data filtering tools for quick list comparison.Lightweight software architecture that runs smoothly even on older devices.

microsoft office alternative - wps office

Frequently Asked Questions

What is the easiest way to find unique values between two lists?

The most efficient method is using a Left Anti-join or Right Anti-join in Power Query. This join type automatically filters out matching rows and only returns the items that exist in one specific list but not the other.

Can I compare two Excel lists without using a macro or VBA code?

Yes, you can use Power Query to merge the lists visually. Alternatively, you can use standard spreadsheet functions like VLOOKUP or MATCH in a helper column to flag matching and non-matching rows.

What does an Inner Join do in Excel Power Query?

An Inner Join compares two tables and returns only the rows where there are exact matching values in the specified columns of both lists, dropping any rows that are unique to either table.

How do I handle similar but not exact matches between lists?

For similar matches, Power Query includes a 'Use fuzzy matching to perform the merge' option in the Merge Queries dialog box. You can enable this setting and adjust the similarity threshold to catch slight typos or formatting differences.

Excel & Data Problems