Compare Two Excel Sheets With Power Query

To compare two Excel sheets with Power Query, turn each data range into an Excel table, import both tables into Power Query, then merge the queries on a shared key. Use a Left Anti join to find rows only in the first table, a Right Anti join for rows only in the second, or a Full Outer join to bring both sides together for a broader comparison.

What Power Query Can Compare

Power Query is Excel’s tool for importing, reshaping, combining, and refreshing data. For a sheet comparison, it treats each table as a set of records and matches records using one or more columns you select. A shared key might be an invoice number, employee ID, product code, or a combination such as department and date.

This is different from comparing worksheet cells by their positions. If a row has moved, a position-by-position comparison may report many apparent differences. A query merge instead asks whether the same key exists in both sources, so it can still match a record after rows have been sorted or inserted.

Power Query is especially useful when the comparison will be repeated: for example, checking a new monthly export against last month’s list. You can refresh the queries after replacing or updating the source data. The result is not a live, continuously updating comparison; it changes when you refresh the query.

Prepare the Two Sheets

Before importing, make the source ranges consistent enough to match. Each range should have one header row, one record per row, and no completely blank rows or merged header cells within the data. The two tables do not need to have identical columns, but their matching key columns must represent the same kind of information.

Give each source range a clear header, such as CustomerID, ProductCode, or OrderDate.
Check that key values use compatible formats. A number stored as text in one table may not match a numeric value in the other.
Decide what constitutes one record. If an order can contain several products, an order number alone may not uniquely identify a row; order number plus product code may be a better key.
Look for leading or trailing spaces, inconsistent abbreviations, and spelling differences in text keys.

On Excel for Microsoft 365 on Windows, click inside the first range and press Ctrl+T to create a table; confirm that “My table has headers” is selected if it does. Repeat for the second range. On Mac, Command+T opens the table creation dialog in Excel; menu names and Power Query options can vary by Excel release. Name each table from the Table Design tab, for example CurrentList and PreviousList. Meaningful names make the source steps easier to recognize later.

Import Both Tables into Power Query

Click inside the first table and choose Data > From Table/Range. Excel opens the Power Query Editor and creates a query from that table. In the editor, use the Query Settings pane to rename the query if needed. Choose Home > Close & Load To, then select Only Create Connection if the purpose is to use the query in a merge rather than display it as a separate worksheet table.

Repeat the same process for the other Excel table. You should now have two queries in the workbook, each pointing to one source table. If you want the comparison output on a worksheet, you can load that final query later. Keeping the source queries as connections avoids creating unnecessary copies of the input data.

If the ranges are not Excel tables, you can still import data through Power Query, but turning each range into a table first makes the source easier to maintain. Excel tables normally expand when rows are added directly beneath them, and a refreshed query can then include the expanded table range.

Merge the Queries on a Shared Key

Open Power Query Editor by selecting a query and choosing Data > Queries & Connections, then double-clicking the query. In the editor, choose Home > Merge Queries to merge into the current query, or Home > Merge Queries as New to create a separate comparison query while keeping the originals unchanged.

In the Merge dialog, select the first query in the upper list and the second query in the lower list.
Click the key column in the upper preview, then click its counterpart in the lower preview. For a multi-column key, select the columns in the same order in both previews, using Ctrl-click on Windows. On Mac, use the equivalent modifier shown by that Excel release, commonly Command-click.
Choose a join kind from the menu. The choice controls which records the merged result keeps.
Confirm the dialog. Power Query adds a nested-table column representing related rows from the other query.

The dialog displays a count of matching rows based on the selected columns. Treat that as a useful check, not a guarantee that the key is logically unique. If repeated keys exist, one row from one table can match multiple rows in the other, increasing the number of output rows.

Choose the Join That Answers Your Question

For records present in the first table but absent from the second, use Left Anti. For records present in the second table but absent from the first, use Right Anti. These are often the clearest choices for questions such as “Which products were removed?” or “Which IDs are new?” The direction depends on which query you place first and which you place second in the Merge dialog.

A Full Outer join keeps rows from both tables, matching rows where the selected key agrees and retaining unmatched rows from either side. It is useful for a complete inventory of additions, removals, and records that exist in both sources. A Left Outer join keeps every row from the first table and any matches from the second; it is useful when the first table is the main list you want to preserve.

For example, imagine a product table with ProductCode and Price in this week’s sheet and the same columns in last week’s sheet. A Left Anti join from this week to last week identifies codes newly present this week. Reversing the query order and using Left Anti identifies codes that disappeared. A Full Outer join can bring both sets together and also pair products found in both weeks, after which you can inspect the two price columns for changes.

Expand Matched Data and Find Changed Values

After merging, use the expand button (the two-arrow icon) in the nested-table column header to choose which columns from the other query to bring into the result. For a price comparison, expand the prior table’s Price column and keep the current Price column. Rename the resulting columns clearly, such as CurrentPrice and PreviousPrice, so it is obvious which source each value came from.

A join on the key tells you whether records match; it does not automatically decide whether every other field is equal. To find changed fields, compare the expanded values. In the Power Query Editor, use Add Column > Conditional Column to label a row based on whether the two price columns are equal, or use a custom column when you need more complex rules. For more than one field, create a status for each important field or build a broader review process that identifies which columns differ.

With a Full Outer join, unmatched records have null values on the side where no matching row exists. Those nulls are meaningful: they help distinguish a new record from an existing record with a changed value. Avoid replacing nulls with zero or blank text until you have decided whether that replacement preserves the meaning of the comparison.

Handle Keys, Duplicates, and Data Quality

Reliable results depend on a reliable key. If the key is missing, inconsistent, or not unique, the merge can omit expected matches or produce unexpected ones. For instance, if two employees share a surname and you merge only on surname, the query may pair the wrong records. Use a stable ID where available, or combine enough columns to identify a record unambiguously.

Duplicates deserve special attention. If the first query has two rows with key A and the second has three rows with key A, a merge can produce multiple pairings for that key. Power Query is not necessarily making a mistake; the inputs describe several possible matches. Decide whether duplicates should be removed, grouped, or compared using an additional key column. Do not remove duplicates automatically if repeated rows represent legitimate transactions.

For text keys, consider cleaning the data before merging. The Transform tab includes tools for trimming and cleaning text, and you can standardize letter case if capitalization differences should not matter. For dates and numbers, confirm that both columns have the intended data type in the Power Query Editor. A date that is actually text, or a code with leading zeros converted to a number, can cause a match to fail or change the identifier.

Load, Refresh, and Maintain the Comparison

When the merge and any cleanup steps are complete, choose Home > Close & Load, or use Close & Load To to choose where the result goes. Loading to a worksheet is convenient for review and filtering. A connection-only result can be appropriate when another query or workbook feature will consume it.

When source tables change, refresh the result with Data > Refresh All. Refresh reruns the saved transformation steps against the current sources; it does not preserve manual edits made inside the loaded result table as part of the query logic. If the source table names or columns change, the query may need adjustment. Keep the key columns and expected headers stable, and document what each join direction means so another person can interpret the output.

Power Query is available in Excel for Microsoft 365 on Windows with a full set of authoring features. Excel for Mac also provides Power Query capabilities, but supported connectors and some editing details can differ by release. Excel for the web has more limited Power Query authoring support than the Windows desktop app, so use the desktop version when a required merge or transformation option is unavailable.

Common Misconceptions and Alternatives

A common misconception is that “compare sheets” always means finding every changed cell. A merge is primarily a record-matching operation. It is excellent for identifying missing keys and comparing selected fields, but a wide table with many columns may need additional conditional columns or a deliberate comparison design to report every changed value clearly.

For a one-time, small comparison, worksheet formulas or conditional formatting may be quicker, especially if both sheets have rows in the same order. For repeated imports, differently sorted records, or data that needs cleaning before comparison, Power Query is usually more maintainable. Excel’s Inquire add-in can compare workbook structures and cell differences in supported Microsoft 365 editions, but it is a different tool and is not available in every Excel edition. Choose based on whether the task is record reconciliation, visual cell checking, or workbook auditing.

Frequently Asked Questions

Can Power Query compare two sheets in the same workbook?
Yes. Convert each sheet’s range to an Excel table, create a query from each with Data > From Table/Range, and merge those two queries. The tables can be on separate worksheets in the same workbook.

How do I find rows that are only in one sheet?
Merge the queries on a suitable key and choose Left Anti to return rows only from the first query. Reverse the query order and use Left Anti to find rows only in the other query, or use Right Anti when that direction is clearer in the dialog.

Does Power Query show changed values automatically?
No. A merge matches records based on selected key columns. Expand the fields you want to compare, then add a comparison step, such as a conditional column, to label differences.

Why are some records missing from the match?
Check that the key columns have compatible data types and values, and look for spaces, inconsistent formatting, missing IDs, or an incorrect key. Also confirm that the join kind keeps the unmatched rows you intend to inspect.

Will the comparison update when I edit a source sheet?
Not immediately. Refresh the query, for example with Data > Refresh All, to apply the saved steps to the updated source tables.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.