How to Compare Two XML Files in Excel

How-to basics · Updated November 20, 2026

To compare two XML files in Excel, import each one with Power Query, shape both into tables with the same record key, then merge them using a full outer join. Expand the fields you want to check and compare the two versions side by side. This works best when the files contain structured records, such as products or orders; Excel is less suitable for checking whether two XML documents are identical character for character.

The steps below are for Microsoft Excel for Microsoft 365 on Windows. They compare the data represented by the XML, not formatting, whitespace, or the order of tags in the original text. Keep copies of the original files, especially if the comparison is important.

What You’ll Need

  • Both XML files saved in a location you can access. The examples assume each file has records with a stable, unique identifier, such as an ID or product code.
  • Excel for Microsoft 365 on Windows, with Power Query available. In Excel for the web, the full Power Query editing workflow may not be available, so use the Windows desktop app for these steps.
  • A basic idea of which XML elements represent a record and which fields you need to compare. XML files can have different structures, and choosing the wrong level can produce an incomplete comparison.

Step-by-Step

  1. Check how the files are structured. Open each XML file in a text editor or browser, not to edit it, but to identify the repeated record element and its fields. For example, a product file might have repeated <Product> elements, each containing <ID>, <Name>, and <Price>. Confirm that the identifier appears once per record and is present in both files. If the files use different names for the same identifier, note that now so you can match the right columns later.
  2. Import the first XML file into Power Query. In Excel, open a blank workbook or a workbook where you want the comparison. Select Data > Get Data > From File > From XML, choose the first file, and select Import. In the Navigator window, select the table or element that represents the repeating records. Choose Transform Data rather than loading immediately, so you can inspect and prepare the data first.
  3. Find the actual record table in the query. XML imports often show nested values, such as a Table, Record, or List, rather than a flat set of columns. In Power Query Editor, click a nested Table or Record value, or use the expand button (the two-arrow icon) on a column header to expose its fields. Continue expanding the relevant columns until you see one row per record and ordinary columns for the values you need. Do not expand unrelated branches just because they are available; they can create duplicate rows or an unnecessarily complicated query.
  4. Prepare the first query and give it a clear name. Keep the key and the fields you want to compare. Use the expand control to select only those fields, and clear the option to use the original column name as a prefix if you prefer shorter headings. Check the key column for nulls and duplicates. If appropriate, set the key to the Text data type using the data-type icon in the column header; identifiers such as 0017 should usually stay as text so leading zeros are not lost. In the Query Settings pane, rename the query to something clear, such as OldXML.
  5. Import and prepare the second file in the same way. Repeat Data > Get Data > From File > From XML for the newer file. Select the corresponding record level, expand the same fields, and set the key and comparison columns to compatible data types. Rename this query NewXML. Confirm the columns have matching meanings, even if their headings differ. If necessary, rename a heading by double-clicking it or right-clicking it and choosing Rename.
  6. Merge the queries with a full outer join. In Power Query Editor, select OldXML, then choose Home > Merge Queries > Merge Queries as New. In the Merge window, choose NewXML as the second table. Click the key column in the first preview and the corresponding key column in the second preview. If matching depends on more than one field, Ctrl-click each key column in the same order in both previews. Set Join Kind to Full outer (all rows from both), then select OK. A full outer join keeps records found only in either file as well as records with matching keys.
  7. Expand the second file’s fields. The merged query includes a column containing nested matches from NewXML. Click its expand icon and select the fields you want to compare, such as Name and Price. Include the second file’s key as well; this helps identify records that exist only in the newer file because the first file’s key will be blank for those rows. Clear the prefix option if you want simpler headings, or keep prefixes and rename the columns so it is unmistakable which values came from each file. For example, use headings such as Old Name, New Name, Old Price, and New Price.
  8. Load the comparison into Excel. In Power Query Editor, select Home > Close & Load to create a worksheet table. If you want to choose where it goes, use the arrow next to Close & Load and select Close & Load To…, then choose a table and a destination. The merged table should show the key from each side and paired fields for comparison. If it has fewer rows than expected, return to the join settings and check that you used a full outer join rather than an inner join.
  9. Label records that are new, missing, or matched. Suppose the worksheet table has the old key in column A and the expanded new key in column B, starting on row 2. Add a heading such as Record status in C1. With those columns in that layout, enter =IF(A2="","Only in new file",IF(B2="","Only in old file","In both")) in C2 and fill it down. A row with no old key but a new key is only in the new file; a row with no new key is only in the old file. Check a few rows manually to confirm the labels make sense.
  10. Flag changed values in matched records. For example, if Old Price is in D and New Price is in E, add a heading in F1. With old values in D2:D and new values in E2:E, enter =IF(AND(D2="",E2=""),"Same",IF(D2=E2,"Same","Different")) in F2 and fill it down. This treats two empty cells as the same and flags unequal values. Repeat for each field you need to check, changing the column references. Filter the result columns for Different and the record-status column for records found on only one side.
  11. Review and save a useful result. Filter for changed or one-sided records, then inspect a sample against the XML source files before relying on the output. Save the workbook with the queries intact if you expect to compare updated versions later. When the source files change, use Data > Refresh All after updating the query source paths, if needed. Keep the original XML files unchanged so you can reproduce or investigate the comparison.

The equality formula above compares values as Excel sees them. Excel’s ordinary equals comparison is not case-sensitive for text, so values such as ABC and abc may be treated as equal. For a case-sensitive text comparison, with the same old and new values in D2 and E2, use =IF(EXACT(D2,E2),"Same","Different"). Decide which behavior matches your data before interpreting the results.

Common Mistakes

  • Choosing the wrong XML level. Importing a parent element can leave the fields nested or aggregate multiple records into an unexpected shape. Expand until each row represents one comparable record, then verify the row count against the source.
  • Joining on a non-unique field. Names and descriptions can repeat or change. A merge on a duplicate key can multiply rows and make the results misleading. Prefer a stable unique ID. If no single field is unique, use a combination of columns that identifies a record reliably.
  • Using mismatched key types or values. A text key may not match a number key, and leading zeros, extra spaces, or inconsistent capitalization can prevent a match. Set both keys to the same type and clean them deliberately. Do not remove spaces or change capitalization if those differences are meaningful in your data.
  • Using the wrong join kind. An inner join shows only records found in both files, hiding additions and removals. A full outer join is the appropriate starting point when you want to find all records that changed or appear on only one side.
  • Comparing XML text when you mean data. Two XML files can contain the same values but differ in whitespace, tag order, or formatting. Power Query compares extracted fields; it does not prove that the original files are textually identical.
  • Letting automatic type conversion change values. Excel may interpret dates, numbers, or identifiers differently from the XML text. Check types and sample values before comparing. Treat codes and IDs as text when their formatting, including leading zeros, matters.

Tips

  • Keep the original key fields from both files in the merged output. They make it easier to distinguish a new-only row from an old-only row and to trace a result back to its source.
  • Compare only the fields that matter for the task. If XML contains nested child records, such as several line items under one order, decide whether each child is its own record or whether the parent record is the comparison unit. Expanding a one-to-many relationship can repeat parent rows.
  • Use meaningful query and column names such as OldXML, NewXML, Old Status, and New Status. Clear labels reduce the risk of reading a difference backward.
  • For a repeatable process, keep the query structure and replace the source files with new versions that have the same paths and schema. Select Data > Refresh All, then check for refresh errors and unexpected row-count changes.
  • For large files, close other workbooks and avoid loading unnecessary XML branches. Power Query can process substantial data, but the available memory and complexity of the XML affect refresh time.

Troubleshooting

  • The XML option is missing. Confirm you are using the Windows desktop version of Excel for Microsoft 365 and look under Data > Get Data > From File. If the file is actually a different format with an .xml-like name, confirm its extension and contents. Excel for the web may not expose the same import and query-editing options.
  • The Navigator shows no useful table. Try selecting another listed element, then choose Transform Data and inspect the query. Expand nested List, Record, or Table values until the repeating records and fields appear. If the XML uses namespaces or an unusual structure, you may need to explore several levels or ask the file provider for a tabular export.
  • Expected matches appear as old-only and new-only. Check both key columns for different data types, leading or trailing spaces, leading zeros, and inconsistent key formats. In Power Query, select each key column and apply the same data type and appropriate cleanup steps to both queries before merging. Refresh the merge after the changes.
  • The merge produces duplicate rows. Look for duplicate keys in either input. If a key appears multiple times on both sides, a merge can create multiple combinations for that key. Confirm the true record grain, remove duplicates only if they are genuinely redundant, or add more key columns to identify individual records.
  • Values look different but are marked the same, or vice versa. Check whether the values were converted to numbers or dates, whether text case matters, and whether blanks are being interpreted as nulls or empty strings. Compare the original XML values for a sample and adjust the data types or comparison logic to match the intended rules.
  • A refresh fails after the files move. Open the queries using Data > Queries & Connections, right-click the relevant query, and choose Edit. In Power Query, check the source step and select the correct file path. Then refresh again and verify that both queries are using the intended files.

Frequently Asked Questions

Can Excel compare two XML files directly?
Excel does not provide a simple side-by-side XML difference viewer. Power Query can import the files as structured data and compare fields after you identify matching records.

Do the two XML files need exactly the same structure?
No, but they need to expose comparable fields and a reliable way to match records. If one file nests a field differently or uses a different field name, prepare or rename the columns in Power Query before merging.

What if there is no unique ID?
Use a combination of fields that reliably identifies each record, selecting the same columns in the same order in both merge previews. If records can still share that combination, the results may be ambiguous; resolve the key design before trusting the comparison.

Can I compare the files character by character in Excel?
That is not what this workflow does. It compares selected values after parsing the XML. For exact file-level differences, use a text comparison tool designed for XML or text files.

Will the comparison update when the XML changes?
Yes, if the query sources still point to the intended files and their structure remains compatible. Use Data > Refresh All, then inspect errors and confirm that the output row counts and key fields look reasonable.

Can I use this approach on a Mac?
Power Query is available in some Microsoft 365 Excel for Mac builds, but available connectors and editing features can differ from Windows. Check the Data tab for the XML connector and query tools; if they are unavailable, use Excel for Microsoft 365 on Windows for this workflow.

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.