Excel Power Query: Compare Two Columns Step by Step

How-to basics · Updated November 18, 2026

In Excel Power Query, compare values on the same row by opening Add Column > Custom Column and entering an expression such as if [Plan] = [Actual] then "Match" else "Different". To compare values in two separate tables or lists, use Home > Merge Queries and choose the matching columns and an appropriate join kind.

Choose the right comparison method

Start by deciding what “compare two columns” means for your data. If each row contains a pair of values that belong together, such as a planned amount and an actual amount, compare the two fields in the same row. A custom column can label each pair as a match or a difference.

If the columns are in different tables, or represent lists whose rows are not aligned, merge the queries using a shared key such as a product ID. A merge matches records by their key values; it does not compare row 2 in one list with row 2 in another. This distinction matters because sorting, inserted rows, and missing records can make two lists look aligned when they are not.

  • Same row, same query: Add a custom column with an equality test.
  • Two tables with a shared identifier: Merge on that identifier, then inspect matched or unmatched records.
  • Two lists with no reliable key: Create or identify a meaningful key first; comparing by position is usually unsafe.

Compare two fields in the same query

These steps are for Excel for Microsoft 365 on Windows. Select a cell in the source data and choose Data > From Table/Range. If the source is not already an Excel table, Excel will prompt you to create one. In Power Query Editor, check that both columns are present and have suitable data types.

  1. Choose Add Column > Custom Column.
  2. Enter a name such as Comparison.
  3. In the custom column formula box, enter if [Plan] = [Actual] then "Match" else "Different", replacing the bracketed names with your exact column names.
  4. Select OK, review the resulting values, and choose Home > Close & Load to return the result to Excel.

The expression is Power Query M language, not an Excel worksheet formula. In the Custom Column dialog, enter it without a leading equals sign. For example, if Plan contains 25 and Actual contains 25, the result is Match; if Actual contains 20, the result is Different. The comparison checks each record’s two fields, so the source row needs no separate worksheet formula or fill-down.

For a simple TRUE/FALSE result instead of labels, use [Plan] = [Actual] in the custom column box. A conditional column is another option when you prefer menus: choose Add Column > Conditional Column, select the first column, set the condition to equals, and choose the comparison value available in the dialog. The custom column is more direct when the condition must compare one field with another field in the same row.

Compare two separate tables or lists

When two tables contain records that should match by an identifier, load each into Power Query and merge them. For instance, a current inventory table and a previous inventory table might both contain a SKU column. Merging on SKU lets you find which products occur in both tables and which occur on only one side.

  1. Load both tables as queries, using Data > From Table/Range for each Excel table.
  2. Open the query you want to treat as the primary table, then select Home > Merge Queries. Choose Merge Queries as New if you want a separate result and to leave the original queries unchanged.
  3. In the merge dialog, select the second query and click the corresponding key column in each preview. If matching requires multiple fields, select the fields in the same order in both previews.
  4. Choose a join kind, then select OK. Expand the new column if you need to see fields from the other query.

A Left Outer join keeps every row from the first table and includes matching rows from the second. A Left Anti join returns rows from the first table that have no match in the second. Choose Right Anti to find rows present only in the second table. A Full Outer join keeps rows from both tables, including unmatched ones, and is useful for a broad reconciliation. The names and choices appear in the Merge dialog’s join kind list.

For example, to find SKUs in the current inventory that are absent from the previous inventory, make the current inventory the first table and use a Left Anti join on SKU. To identify missing records in both directions, run the anti-join comparison in each direction or use a Full Outer join and inspect which side has null fields after expansion.

Understand matches, duplicates, and join results

A merge is a key-based match, not a cell-by-cell comparison of entire rows. If you merge only on SKU, two records with the same SKU can match even when their descriptions or prices differ. To compare several identifying fields together, select each key field in the merge dialog. To compare non-key values after matching, expand the second table’s fields and add a custom column comparing the corresponding values.

Duplicates deserve special attention. If a key appears more than once in either table, a merge can produce multiple matching combinations and increase the number of output rows. Before trusting a reconciliation, check whether the key is meant to be unique. If it is, use Home > Remove Rows > Remove Duplicates only when removing duplicates is appropriate, or investigate why duplicates exist rather than discarding them automatically.

Power Query compares values according to their data types and text values. The number 12 and the text value “12” are not necessarily the same key. Set both columns to compatible types using the type icon in the column header or the Transform > Data Type command. Text comparisons are case-sensitive in Power Query, so ABC and abc can be treated as different values.

Clean data before comparing

Many apparent mismatches come from inconsistent source data rather than genuinely different values. A product code may contain a trailing space, one date column may be stored as text, or a number may have been imported with a different type. Inspect the columns before concluding that records do not match.

  • Remove extra spaces: For text columns, use Transform > Format > Trim. Use Clean as well if unwanted nonprinting characters are suspected.
  • Standardize letter case: Use Transform > Format > UPPERCASE or lowercase both key columns before merging when case should not distinguish records.
  • Set consistent types: Convert both keys to the intended type. Do not convert codes to numbers if leading zeros are meaningful; a code such as 0042 may need to remain text.
  • Check blanks and nulls: A blank cell and a missing value may be represented differently after import. Filter the columns and inspect the affected records.

If a same-row comparison should ignore spaces and capitalization, add a custom column using Text.Upper(Text.Trim([Plan])) = Text.Upper(Text.Trim([Actual])), substituting your text column names. This expression is appropriate for text fields; it does not replace careful type handling for dates or numbers. If source cells may contain errors, handle those errors deliberately in Power Query rather than assuming every comparison will produce a regular match result.

Refresh the comparison and load the result

Power Query stores the steps used to transform and compare data. When the source changes, choose Data > Refresh All in Excel to rerun the queries and update their loaded results. If you want a source table to include newly added rows on refresh, format the source as an Excel table before creating the query. A query based on a fixed range may not include rows added outside that range.

To adjust the comparison later, open the query from Data > Queries & Connections and edit it in Power Query Editor. The Applied Steps pane records operations such as changing types, merging, and adding custom columns. Editing an earlier step can change later results, so review the output after changing a key or cleanup step.

On Excel for Mac, Power Query is available in Microsoft 365, but availability and the exact interface can vary by build and supported data source. The Windows ribbon paths above describe the standard workflow; look for the Get Data or Power Query commands in the Data tab on your Mac. Excel for the web may let you view or refresh some queries, but query creation and editing capabilities can differ from desktop Excel.

Power Query compared with formulas and other options

A worksheet formula can be quicker for a small, one-off comparison. For example, if values are in A2 and B2, an Excel formula such as =IF(A2=B2,"Match","Different") returns Match when the values are equal and Different otherwise. Fill it down to compare the remaining rows. This is an ordinary Excel formula, unlike the M expression used in a Power Query custom column.

Power Query is a better fit when the data is imported repeatedly, needs several cleanup steps, comes from more than one source, or should be refreshed without rebuilding the comparison. Its steps are saved with the query. Conditional formatting is useful when the goal is simply to highlight differences visually in a worksheet, but it does not create a cleaned, refreshable reconciliation table. Choose based on whether you need a visual cue, a quick cell result, or a repeatable data-preparation process.

Frequently Asked Questions

Can Power Query compare two columns row by row?
Yes. In one query, choose Add Column > Custom Column and compare the two fields in an M expression. This compares values within each record.

How do I find values in one column that are missing from another?
Load the lists as separate queries, merge them on a reliable key, and choose a Left Anti join to find values present only in the first list. Reverse the tables for the opposite direction.

Why does Power Query say two values do not match when they look identical?
Check for different data types, leading or trailing spaces, capitalization, hidden characters, and leading zeros. Standardize the values only in ways that preserve their meaning.

Can I compare entire rows in Power Query?
Yes, but first match the records using a stable key. Then expand the relevant fields from the other table and compare each pair of values. A merge on only one key does not automatically confirm that every other field is equal.

Will the comparison update when I change the source data?
Yes, after refreshing the query. Use Data > Refresh All, and make the source an Excel table if new rows should be included as the table grows.

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.