Combine Multiple XML Files Into One Excel Workbook

How-to basics · Updated November 19, 2026

Yes. In Excel for Microsoft 365 on Windows, use Power Query to import XML files, select the repeating records you need, and append the resulting queries into one table. For files with the same structure, a folder-based query can make later refreshes easier; for files with different structures, inspect and standardize the data before combining it.

What combining XML files in Excel means

XML stores information in a hierarchy of elements and attributes, rather than in the rows and columns of a worksheet. For example, a file might contain a list of orders, with each order holding a customer, date, and several line items. Excel must turn the relevant repeated elements into rows and the fields within them into columns before the records can be combined.

In Excel, Power Query is usually the right tool for this conversion. It can read XML, shape the data, append rows from separate sources, and load the result to a worksheet or the Data Model. Appending means placing records from one table underneath records from another; it is different from merging, which matches tables using a shared key such as an order ID.

Combining files does not preserve every aspect of the original XML document. The output is a tabular view of selected data, not a new XML document. If you need to retain nested relationships, document metadata, or the original files for an application, keep the XML files separately as well.

Check the files before importing

Start by opening a few XML files in a text editor or browser and comparing their structure. Find the element that repeats once per record, such as <Order> or <Product>, and note which fields you need. A file may contain many nested sections, so choosing the wrong level can produce one row per customer instead of one row per order, for example.

  • Check whether all files use the same element names, nesting, and field types.
  • Look for missing values, optional sections, namespaces, and attributes that may need to become columns.
  • Confirm that the files represent records you can safely stack. If one file contains orders and another contains customers, appending them together will not create a meaningful table.
  • Make a copy of the source files and note their folder location before building a repeatable query.

Files do not have to be byte-for-byte identical, but a consistent schema makes the result much easier to interpret. If one file uses <CustomerID> and another uses <ClientID> for the same field, decide on a common column name before appending.

Import and append files with Power Query

For a small number of files, import each XML file as its own query, then append the queries. In Excel for Microsoft 365 on Windows, use this workflow:

  1. Open the workbook that will hold the combined data. Select Data > Get Data > From File > From XML, browse to the first file, and select Import.
  2. In the Navigator window, choose the table or repeated element that contains the records you want. Select Transform Data if you need to inspect or reshape it before loading.
  3. In Power Query Editor, expand nested tables or records using the expand icon in a column heading. Select the fields to include, and rename columns if needed. Repeat the XML import for each additional file.
  4. For each import, make sure the resulting query has compatible column names and types. In Power Query Editor, choose Home > Append Queries > Append Queries as New.
  5. Select Three or more tables if you have several queries, add the queries to the list, and confirm. For two queries, choose the two-table option and select both sources.
  6. Review the appended preview for missing fields, unexpected rows, and data types. Then select Home > Close & Load to place the result in Excel.

Power Query appends by column name, not by the order of columns in the source. If one query has a column that another lacks, the combined table can contain that column with blank values for the source that did not provide it. That is useful when the difference is expected, but it can also reveal a mismatch that needs attention.

Use a folder for recurring XML files

If new files arrive regularly, putting them in a dedicated folder can reduce repeated manual importing. In Excel for Windows, select Data > Get Data > From File > From Folder, choose the folder, and inspect the file list before combining anything. Filter out unrelated files, temporary files, or subfolders if they should not be included.

When Excel offers a combine option for the folder source, use the sample file to define how each XML file should be transformed, then check that the resulting query returns the same fields for every file. XML support in this workflow can vary by Excel build and by the structure of the files. If the folder preview does not offer a suitable combine operation, use Power Query to create a per-file XML transformation and apply it to the folder contents, or import and append the files individually.

Once the query is set up, refresh it after adding files to the source folder. In Excel, select Data > Refresh All. Confirm that the query points to the intended folder and that incoming files follow the expected structure. A folder query is not a substitute for reviewing the data: a new XML layout can cause refresh errors or silently leave new fields out of the output.

Handle nesting and files with different structures

XML often has several layers. Power Query may initially show a column containing nested tables or records rather than a flat list of fields. Expand the level that represents one record at a time, then expand the fields inside it. If an order contains multiple line items, expanding the line-item table usually creates one row per item. Order-level details may then repeat on each item row, which is often appropriate for analysis but changes the row count.

Files with different structures need a decision about what the final table should represent. If both files describe the same kind of record but use different field names, rename equivalent columns, add or tolerate missing columns, and standardize types before appending. If one file has a different record type, keep it in a separate query or create separate tables rather than placing unrelated rows together.

XML namespaces can also make element names look different from the simple names shown in documentation. They identify names in a particular XML vocabulary; they are not necessarily extra business fields. Inspect the Navigator and Power Query preview, and avoid deleting or renaming elements until you know whether they identify the records you need.

Check the combined table and fix common issues

Before relying on the output, compare its row count with the source records and sample several records against the original XML. Confirm that identifiers, dates, and amounts have not been misread. An ID such as 00127 should generally remain text if its leading zeros matter; converting it to a number would change its displayed value.

  • Unexpected blanks: Check whether some files omit optional elements or use different field names.
  • Too many rows: You may have expanded a nested list, such as order line items, creating multiple rows per parent record.
  • Refresh errors: A file may be malformed, inaccessible, or shaped differently from the sample used in the query.
  • Dates or numbers look wrong: Check the column type and regional interpretation. A date string can be ambiguous when day and month order differs.
  • Duplicate records: Appending includes records from every source; it does not automatically identify duplicates. Decide whether duplicates are valid, then remove them using an appropriate key if necessary.

Also consider worksheet capacity. A worksheet can hold up to 1,048,576 rows and 16,384 columns. If the combined result exceeds the row limit, load it to the Data Model or use another data store rather than expecting a worksheet table to hold every record.

Alternatives and practical limits

Power Query is a good fit when the goal is a refreshable table for filtering, PivotTables, or analysis. It is generally more maintainable than copying and pasting rows or writing a macro for a one-time task. Manual imports can still be reasonable for a couple of small files that will never change, but they are easier to repeat incorrectly.

Excel for the web does not provide the same full set of Power Query authoring options as desktop Excel. Power Query capabilities and available connectors also differ across Mac versions and builds. The menu path above is for Microsoft 365 on Windows; if you do not see the XML or folder connector on Mac, use a supported Windows desktop version or a suitable data-conversion tool, then open the resulting workbook in Excel.

Other tools may be preferable for very large XML collections, deeply nested data, or a process that must run unattended on a server. A programming language or specialist ETL tool can offer more control over validation and error handling. Whatever the tool, preserve the source XML and document how the flattened columns correspond to the original elements.

Frequently Asked Questions

Can Excel combine multiple XML files into one worksheet?
Yes. Import the XML data with Power Query, shape each source into compatible columns, and append the queries. Load the appended result to a worksheet if it fits within worksheet limits.

Can I combine XML files from a folder and refresh them later?
Usually, yes, with a folder-based Power Query workflow. The query must be configured to parse each XML file consistently. Check that your Excel build supports the required connector and inspect the result after adding files with a changed structure.

Does Excel merge XML files automatically?
No. Excel does not simply concatenate arbitrary XML documents into a valid new XML document. Power Query extracts selected elements into tabular data; use an XML-processing tool if the deliverable must remain XML.

What if the XML files have different tags or fields?
Decide which fields mean the same thing, rename them to consistent column names, and handle fields that appear in only some files. If the files describe different kinds of records, keep them in separate tables instead of appending them.

Can I combine nested XML data in Excel?
Yes, if you expand the appropriate nested tables or records in Power Query. Be aware that expanding a list of child records can create multiple output rows for one parent record.

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.