Power Query to Compare Two Excel Sheets Step by Step

How-to basics · Updated November 21, 2026

To compare two Excel sheets with Power Query in Microsoft 365 for Windows, turn each data range into a table, load both tables into Power Query, then use Home > Merge Queries > Merge Queries as New and join them on a shared key such as an ID. Choose a Full Outer join to see records found on either sheet, expand the second table’s columns, and compare the paired values; the resulting query can be refreshed after the source data changes.

What Power Query compares

Power Query compares structured data, not the appearance of two worksheets. It can match records by one or more columns, show rows that exist on only one side, and put corresponding values next to each other so you can identify differences. This makes it useful for comparing monthly exports, revised price lists, inventory files, customer records, and other tables that share a reliable identifier.

A shared key is the field that tells Power Query which row in the first sheet corresponds to which row in the second. An invoice number, product code, or employee ID is usually more dependable than a row number. If the same person appears on row 12 in one sheet and row 18 in another, comparing row positions would pair the wrong records; matching by a stable ID avoids that problem.

Power Query does not automatically decide what “different” means for your business. A merge identifies corresponding records, but you choose which columns to inspect and how to treat missing values, duplicates, or minor text variations. The output is a query result, not a live overlay that highlights cells in the original worksheets.

Prepare the two sheets

Start by making each data range a simple table. Give the tables clear headers, remove merged cells from the data area, and check that the columns you intend to compare have consistent meanings. In Excel for Windows, select a cell in the first range and choose Insert > Table, or press Ctrl+T. Confirm that the table has headers, then repeat for the second range. On Mac, the corresponding shortcut is Command+T; the Table command is also available from the Insert tab.

For example, suppose the first table, January, contains ProductID, ProductName, and Price, while the second table, February, has the same fields. ProductID is the key. If one table contains prices such as 12.50 and the other contains the text value “12.50”, the values may not compare as intended until both columns have the same data type.

  • Use a key that is present and unique in both tables whenever possible.
  • Keep one header row and avoid blank rows or totals inside the table.
  • Check that key columns use compatible types, such as text on both sides or whole numbers on both sides.
  • Decide whether spaces, capitalization, punctuation, and leading zeros are meaningful before cleaning them.

If the sheets are in separate workbooks, save both files in a location that will remain available when you refresh. Power Query can import tables or sheets from another Excel workbook, but a query that points to a moved or renamed file may need its source path updated.

Load both tables into Power Query

For tables in the current workbook, select a cell in the first table and choose Data > From Table/Range. In the Power Query Editor, confirm that the headers and data types look right. Choose Home > Close & Load To, select Only Create Connection, and confirm. Repeat for the second table. Keeping these as connections makes them available to merge without adding two preliminary query outputs to worksheets.

For tables in another workbook, use Data > Get Data > From File > From Excel Workbook, select the file, choose the relevant table or sheet in the Navigator, and select Transform Data. Import the other source in the same way. If you select a worksheet rather than a formatted table, inspect the preview carefully: a sheet can include blank rows, notes, or extra headings that should not be treated as records.

In Excel for Mac, Power Query is available in Microsoft 365, but exact import options and connector availability can differ by build. Excel for the web also has Power Query capabilities, but authoring and source support are not identical to the Windows desktop app. If a menu or connector is missing, use the desktop app for the merge steps rather than assuming the web version has the same controls.

Merge the queries on a shared key

  1. In the Power Query Editor, choose Home > Merge Queries > Merge Queries as New. “As New” preserves the two source queries and creates a separate comparison query.
  2. In the Merge dialog, choose the first query in the upper list and the second query in the lower list.
  3. Select the key column in the upper preview, then select its corresponding column in the lower preview. For a compound key, hold Ctrl on Windows while selecting each key column, and select them in the same order in both previews.
  4. Choose the join kind. Use Full Outer when you want all rows from both tables, including records with no match. Select OK.
  5. In the new query, expand the column containing the second table’s nested results. Select the fields you need to compare, and clear the option to use the original column name as a prefix if you prefer shorter headings.

The merge result initially contains a nested table column for the matched records from the second query. Expanding it places the selected second-table fields beside the first-table fields. Depending on the column names, Power Query may add suffixes or prefixes to distinguish the two versions. Rename the resulting columns to clear labels such as January Price and February Price so the comparison is easy to audit.

Choose the right join type

The join kind controls which records appear in the result. For a complete comparison, Full Outer is usually the most useful starting point, but other joins can answer narrower questions:

  • Full Outer: includes matching rows and unmatched rows from both tables. Use it to find additions, removals, and records that appear in both.
  • Left Outer: keeps every row from the first query and includes matching values from the second. Rows found only in the second query are omitted.
  • Left Anti: returns rows from the first query that have no match in the second. This is useful for finding records removed from a later list.
  • Right Anti: returns rows from the second query that have no match in the first. This can reveal new records.
  • Inner: includes only rows with a match on the selected key in both queries. It is useful when you want to inspect shared records, but it hides additions and removals.

Join direction matters for Left Anti and Left Outer: the first query in the dialog is the left table. If you reverse the query order, “only in the first table” changes meaning. For a broad audit, a Full Outer join is less likely to hide a one-sided record.

Find missing records and changed values

After expanding the second table’s key and comparison fields, filter the expanded key column. A blank value there often indicates that a row from the first table had no matching record in the second; the reverse pattern can be identified by checking the first table’s key. With a Full Outer join, preserve both key columns in the output so you can tell which side supplied each unmatched record. A blank business value is not always proof of a missing row, so use the keys—not an arbitrary field that may legitimately be empty—to identify unmatched records.

For rows that exist on both sides, compare each relevant pair of columns, such as January Price and February Price. You can use Add Column > Conditional Column in the editor to create a readable status field. Set conditions for an unmatched key first, then for a price that differs, and otherwise label the row as a match. Alternatively, filter or sort the two value columns side by side and inspect the differences. A status column is especially helpful when the result has many records.

In the example, a ProductID found in both tables with a price of 12.50 in January and 13.00 in February is a changed record. An ID present only in February is an addition; one present only in January is absent from the newer list. If there are several columns to compare, create a status for each important field or a combined status that clearly names the differing fields. Do not label a row “unchanged” merely because its price matches if its product name or another required field changed too.

Handle duplicate keys and inconsistent data

A merge assumes the selected columns identify records well enough to match them. If ProductID appears twice in one table and three times in the other, Power Query can produce multiple pairings for that key. The output may contain more rows than either source, which is not a software error: the key did not identify one unique record. Check duplicates before interpreting the comparison. If repeated IDs are legitimate, add another key field, such as a line number or transaction date, to make a meaningful compound key.

Inconsistent formatting can also prevent expected matches. “0017” stored as text is not the same kind of value as numeric 17, and trimming spaces may turn “A12 ” into “A12”. In the editor, use the column type controls and, when appropriate, Transform > Format > Trim or Transform > Format > Clean for text. If capitalization should not matter, standardize both key columns consistently, for example by applying Transform > Format > lowercase to each. Keep the original values or document the cleanup if case or spaces carry meaning.

Power Query comparisons also reflect data types and values, not just how cells look. A date displayed as “3/4/2026” may depend on regional interpretation if it was imported as text; confirm that both columns are actual dates. Likewise, displayed rounding can conceal a small numeric difference. If exact cents or precision matter, inspect the underlying values and choose an appropriate rounding policy rather than relying on cell formatting.

Load the result and refresh it

When the comparison is ready, choose Home > Close & Load to return its output to Excel, or use Close & Load To to choose a worksheet destination or create a connection. Keep the source tables separate from the output table; do not place the result inside a source table that the query reads, because that can create confusing dependencies.

After updating either source table, refresh the output with Data > Refresh All or right-click the query result and choose Refresh. A refresh reruns the import and merge; it does not edit the original worksheets. If the query uses an external workbook, the source file must still be reachable. If a table name, file path, or column header changes, review the query steps because a step referring to the old name can fail.

Power Query versus formulas and highlighting

Power Query is a strong choice when the comparison needs to be repeated, the tables are sizeable, or you need a clear list of additions, removals, and changed records. Its applied steps make the process repeatable: refresh rather than rebuilding the comparison each time. It is less suitable when you only want to glance at two small, fixed ranges or need immediate cell-by-cell highlighting directly on the source sheets.

For a one-time visual check, Excel’s conditional formatting can highlight differences, but it requires careful alignment and does not by itself solve records that have moved or been added. Lookup formulas can retrieve a value by key, but you still need to decide how to flag missing keys and compare multiple fields. Power Query handles row matching and transformation in one workflow; it is not a substitute for correcting poor source data or deciding which differences matter.

Frequently Asked Questions

Can Power Query compare two sheets in the same workbook?
Yes. Format both ranges as tables, create a query connection for each with Data > From Table/Range, and merge the two queries. The source tables can remain on separate worksheets.

Which join should I use to find every difference?
Start with Full Outer to retain matched and unmatched records from both sources. Expand the second table, then inspect missing keys and compare the paired fields. An Inner join alone leaves out records that exist on only one side.

Can Power Query show exactly which cells changed?
It can place old and new values side by side and produce status columns for the fields you choose. It does not automatically paint or mark the original worksheet cells; the comparison appears in the query output.

Why did the merge create extra rows?
The selected key is probably duplicated in one or both tables. A repeated key can match multiple rows and multiply pairings. Check key uniqueness or add fields that identify each record more precisely.

Will the comparison update automatically when I edit a sheet?
The query result updates when you refresh it, including with Data > Refresh All. It is not necessarily recalculated immediately after each cell edit, so refresh before relying on the output.

Related

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.