Convert XML to Excel: Import, Clean, and Save Data

How-to basics · Updated November 22, 2026

To convert XML to Excel in Microsoft 365 for Windows, choose Data > Get Data > From File > From XML, select the file, shape the data in Power Query if needed, and choose Close & Load. Excel places the imported data in a worksheet, where you can filter, analyze, and save it as an .xlsx workbook.

The quickest way to import an XML file

Excel’s XML connector reads the file’s structure and presents available data for import. For a simple XML document with repeating records, the process may take only a few clicks. For nested data, you may need to expand fields in Power Query before the records become a useful table.

  1. Open a blank or existing workbook in Excel for Microsoft 365 on Windows.
  2. Go to Data > Get Data > From File > From XML, then browse to and select the XML file.
  3. In the Navigator window, select the table or data element you want. Choose Transform Data if you need to inspect or reshape it first.
  4. In Power Query, expand nested columns or remove fields you do not need. Choose Home > Close & Load to put the result in a worksheet.
  5. Use File > Save As and save the workbook as an Excel Workbook (.xlsx).

The exact data items shown in Navigator depend on how the XML is organized. If the first selection is a list or record rather than rows and columns, use the expand controls in Power Query to reach the repeating records.

What XML contains—and why conversion can vary

XML, or Extensible Markup Language, stores information inside named elements, often with attributes and nested child elements. The tag names describe the structure, but XML does not prescribe a single table layout. An invoice, product catalog, and set of sensor readings may all be XML while organizing information in very different ways.

For example, an order document might contain one order element with customer details and several nested item elements. A spreadsheet, by contrast, usually represents a list of items as rows and their properties as columns. Importing XML therefore involves deciding which repeating element should become a row and how its parent and child fields should be represented.

XML tags are case-sensitive: <Name> and <name> are different names. Values can be stored as element text, as attributes, or in both places. Those details affect what Excel displays and whether fields need to be expanded or renamed after import.

Shape nested XML in Power Query

Power Query is Excel’s data-import and transformation tool. It lets you inspect the incoming structure before loading it, and it records the applied steps so you can repeat them when the source changes. Imported XML may appear as a table with columns containing the words Record or Table, or as a list of items rather than a finished grid.

When a column contains nested records or tables, select its expand icon—the small button with arrows in the column header—to choose the fields to bring into the current table. You can expand a child table to create multiple rows for each parent record, which is often appropriate when each order contains multiple line items. If you expand customer details instead, the customer fields can become columns alongside the order fields.

Before loading, consider the intended grain of the worksheet: what one row represents. A table with one row per order differs from one row per order item. Flattening an order and its items creates repeated order information across several rows; that is expected if each row represents one item, but it can distort totals if you later sum the repeated order amount. Keep separate tables or remove duplicate parent-level amounts when the analysis requires it.

You can also rename columns, change data types, remove unnecessary fields, and reorder columns in Power Query. Set dates and numbers to the appropriate types when the import does not identify them correctly. A value that looks numeric but is actually text may sort alphabetically or fail in calculations, so check representative values before loading.

A small XML example

Consider this simplified file, where each <product> element describes one product:

<catalog>
  <product id="P104">
    <name>Notebook</name>
    <price>4.50</price>
    <stock>28</stock>
  </product>
  <product id="P105">
    <name>Pen set</name>
    <price>3.25</price>
    <stock>60</stock>
  </product>
</catalog>

The repeated product elements are natural row candidates. A useful worksheet would have columns such as id, name, price, and stock, with one row for each product. Depending on the way Excel displays the imported structure, you may first need to expand the product list, then expand its fields. The id is an attribute rather than an element, but it can still be exposed as a field during expansion.

After import, check that 4.50 and 3.25 are recognized as numbers or currency values, not text. Also verify that the IDs remain intact. If an identifier contains leading zeroes, preserve it as text; Excel may otherwise treat it as a number and remove those zeroes.

Common import problems and how to fix them

  • The file is not listed or cannot be opened: Confirm that it is an XML file, that it is saved locally or at an accessible location, and that it is well-formed. A missing closing tag or invalid character can prevent parsing. If the file is very large, try a smaller valid sample to distinguish a size or complexity issue from a formatting error.
  • Navigator shows a list or Record values: This usually means the XML is nested. Choose Transform Data, then expand the relevant list, table, or record until you reach the repeating items.
  • Some columns are blank: The fields may be optional, or values may occur only in certain records. Check several source records before assuming the import lost data. The chosen row level may also exclude fields stored higher in the XML hierarchy.
  • Numbers or dates look wrong: XML may store values in a format or locale that Excel does not infer as intended. In Power Query, change the column’s data type and check the resulting values. For ambiguous dates, confirm whether the source uses month-day-year or day-month-year before interpreting them.
  • Names have prefixes or unusual characters: XML namespaces can qualify element names, and a source may use names that are not convenient as worksheet headers. Rename columns in Power Query rather than editing the source if you only need readable Excel labels.
  • The imported data is incomplete: Check whether you selected the correct repeated element and whether child records were expanded. Also check the worksheet row limit: an Excel worksheet supports up to 1,048,576 rows and 16,384 columns.

Do not assume that every blank or repeated value is an error. In hierarchical data, a parent value may apply to several child records, and a flat table may repeat it on each corresponding row. Compare the loaded result with a few source records to confirm the intended meaning.

Save the workbook and refresh the import

Once the data is loaded, saving as .xlsx gives you a standard Excel workbook containing the worksheet and, when loaded through Power Query, the query steps. The workbook is not the same thing as the original XML file: the XML remains a separate source, while the workbook contains the imported result and connection information.

If the source file changes, you can refresh the query instead of repeating every transformation. In Excel, use Data > Refresh All to refresh workbook queries. If you moved or renamed the XML file, the saved query may no longer find it; edit the source location through the query’s settings or reconnect to the new file. Refreshing updates the query output, so take care if you have manually edited cells in the loaded table—those edits may be overwritten by a refresh.

For a one-time conversion, saving the resulting workbook is usually enough. For a recurring report, keep the XML source in a stable location and test refresh after changing it. If someone else will use the workbook, provide access to the source file too, unless the recipient only needs the already-loaded snapshot.

Excel version differences and practical limits

These steps are for Excel for Microsoft 365 on Windows. In current Microsoft 365 versions for Mac, Power Query is available, and the XML connector may be reached from Data > Get Data (Power Query) > From File > From XML. Menus and connector availability can vary by Excel build and update channel; if the XML option is absent, update Excel or use the Windows desktop version. Excel for the web is not the best choice for a new XML import because its data-import and Power Query authoring features are more limited than the desktop app.

XML file size alone does not determine whether a worksheet can hold the result. The number of resulting rows and columns matters, as does the memory required to parse a complex document. Excel’s worksheet limits are not a guarantee that every file up to those limits will import smoothly. Very large or deeply nested XML may be better handled by a database or a data-processing tool before loading a manageable result into Excel.

Safety and alternatives

Only import files from sources you trust, especially when an XML file is part of a larger downloaded package. XML itself is a data format, not an Excel macro, but a workbook or related file can contain other active content or links. Review workbook security prompts, avoid enabling macros in files you do not trust, and do not treat imported values as verified simply because Excel parsed them. For sensitive data, follow your organization’s rules for storing source files and workbooks.

If the source is a simple, small XML document, a text editor can help you inspect its tags, but copying and pasting raw XML into cells does not reliably convert its hierarchy into a table. For repeatable transformations and refreshes, Power Query is generally the most convenient Excel method. For data that exceeds worksheet limits or requires relationships across several complex XML documents, consider a database or a dedicated XML-processing tool, then export a suitable table to Excel.

Frequently Asked Questions

Can I open an XML file directly in Excel?
Excel can work with XML, but for a predictable table import use Data > Get Data > From File > From XML on Windows. The connector lets you inspect the available data and transform nested records instead of relying on how a direct open interprets the file.

How do I convert XML to Excel without losing nested data?
Use Power Query and decide what one row should represent before expanding fields. Expand child records or tables deliberately, and compare the output with sample source records. If parent and child information have different row levels, separate tables may preserve the relationships more clearly than one flattened table.

Can Excel convert XML to CSV?
Yes. Import the XML, load the desired table into a worksheet, then use File > Save As and choose a CSV format. CSV stores a single flat table, not nested XML structure, so extra tables, formatting, and relationships are not preserved in that file.

Can Excel turn a large XML file into a worksheet?
It depends on the number and shape of the records and the computer’s available resources. A worksheet is limited to 1,048,576 rows and 16,384 columns. If the result exceeds those limits or becomes too slow to work with, filter or transform the data before loading, or use a database or another suitable tool.

Will the Excel file update when the XML changes?
It can, if it was imported as a Power Query connection and the source remains accessible. Use Data > Refresh All to update the query result. If the XML file has moved, update the query’s source location first.

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.