How to Open XML Files in Excel and Import Their Data

How-to basics · Updated November 21, 2026

To open an XML file in Excel for Microsoft 365 on Windows, use Data > Get Data > From File > From XML, select the file, inspect its contents, and load the data into a worksheet. You can also open an XML file through File > Open and follow Excel’s import prompt. XML often contains nested data, so Power Query is usually the better choice when you need to select, reshape, or combine parts of it.

XML is a text format that stores structured information using tags. Excel can turn suitable XML data into worksheet tables, but the result depends on how the file is organized. Before importing, keep the original file unchanged and decide whether you need all of its data or only particular records and fields.

What You’ll Need

  • Microsoft Excel for Microsoft 365 installed on Windows. The Windows desktop app has the most consistent XML import options.
  • An XML file saved locally or in a location you can browse to. If the file is compressed, extract the XML file first.
  • Permission to access the file, and enough worksheet space for the imported rows and columns.
  • If the file is important, a backup copy. Importing usually leaves the source file alone, but keeping a copy makes it easier to recover from accidental edits or save-over mistakes.

Excel for the web does not provide the same full XML import workflow as the Windows desktop app. On a Mac, available Power Query connectors and menu labels can depend on the Excel build; if you cannot find an XML connector, use Excel for Microsoft 365 on Windows for this process.

Step-by-Step

  1. Check the file and make a working copy. Confirm that the file ends in .xml, not .xlsx or .xls. Those Excel workbook formats are different from a standalone XML data file. If the XML came from a website or another person, make sure you trust the source before opening it. Copy it to a folder you can find easily, and avoid editing the original file in a text editor unless you know how its tags are structured.

  2. Start the XML import from the Data tab. Open a blank workbook, then choose Data > Get Data > From File > From XML. In the file picker, browse to the XML file, select it, and choose Import. This opens the Power Query import experience, which lets you preview the file before placing data in the workbook. If you do not see the XML option, check that you are using the desktop version of Excel for Windows, rather than Excel for the web.

  3. Review the Navigator preview. Excel reads the XML structure and displays available tables or elements in the Navigator. Select an item to preview it. Look for the element that represents the repeating records you actually need—for example, a list of orders, products, or contacts—rather than selecting a top-level container that may include several nested groups. If the preview is empty or shows an unexpected structure, try another listed item before loading anything.

  4. Choose whether to transform or load. If the preview already has the correct rows and columns, choose Load to bring it into Excel. If you need to remove fields, change data types, or unpack nested information, choose Transform Data to open Power Query Editor. Transforming first is useful when XML contains many layers or unnecessary metadata. It does not alter the source XML file; it defines how Excel will prepare the imported result.

  5. Expand nested records when needed. In Power Query Editor, a column may contain the words Record, Table, or List instead of ordinary values. These indicate structured data inside that field. Use the expand icon in the column header to choose which fields to bring into columns. For a nested table or list, expand the relevant level and check the preview after each expansion. Avoid expanding every field automatically if the file contains unrelated or very large sections.

  6. Check column names and data types. Review the preview for blank columns, repeated headers, missing values, or numbers that Excel has treated as text. Identifiers such as ZIP codes, product codes, and account numbers often need to remain text so leading zeros are preserved. Dates and decimal values may also need attention if the XML uses a format or regional convention that Excel interprets differently. Correct obvious type issues in Power Query before loading, and compare a few rows with the source if accuracy matters.

  7. Load the prepared data to a worksheet. In Power Query Editor, use Home > Close & Load to load the result. To choose a destination or output type, use the arrow next to Close & Load, then select Close & Load To and choose the option you need, such as a table in a new worksheet. After it loads, check the top, middle, and bottom of the result and confirm that the number of records looks reasonable.

  8. Save the workbook in an Excel format. Choose File > Save As and save the imported result as an Excel workbook, typically .xlsx. This gives you a workbook containing the imported table and, when loaded through Power Query, its query connection. Keep the original XML separately if you may need to import it again. Saving as an Excel workbook does not convert the source file in place.

Alternative: open the XML file directly. You can also choose File > Open > Browse, select the XML file, and open it. Excel may show an XML import prompt with choices such as opening the file as an XML table, as a read-only workbook, or using the XML Source task pane. The wording and choices can vary. Choose the table option for a straightforward import when offered; use Power Query instead when you want to inspect and shape nested data before loading.

Common Mistakes

  • Opening the wrong file type. An .xlsx file is already an Excel workbook, even though its internal structure uses XML. Do not try to import it as a standalone XML data file; open it normally with File > Open.
  • Choosing the first XML element without checking it. The first item in Navigator may be a container rather than the repeating records. Preview the available items and select the one that produces the intended rows.
  • Assuming every tag becomes a useful column. XML can contain nested collections, optional fields, attributes, and metadata. Some columns need expanding, while others are irrelevant. Inspect the table rather than treating the first preview as final.
  • Letting Excel change identifiers into numbers. A code such as 00427 can become 427 if imported as a number. Set identifiers to text before relying on them in lookups or exports.
  • Overwriting or renaming the source carelessly. Save the finished work as an Excel workbook in a separate file. Do not replace the only copy of the XML with a workbook or change its extension to make Excel accept it.

Tips

  • Use Power Query for repeat imports. If the XML is updated regularly, keep the query in the workbook. When the source file is replaced or updated at the same location, use Data > Refresh All to refresh the imported results. If the file moves or is renamed, edit the source location in the query settings.
  • Load only what you need. Removing unused columns and expanding only relevant nested fields can make the result easier to review and may reduce unnecessary worksheet data.
  • Keep a record of the source. Note where the XML came from and when it was downloaded. This is helpful when refreshed data differs from an earlier import or when you need to confirm which source produced a table.
  • Check Excel’s worksheet limits. A worksheet supports up to 1,048,576 rows and 16,384 columns. If an XML import is larger, it will not fit in one worksheet; filter or transform it in Power Query, or use a database or another tool designed for larger datasets.
  • Be cautious with external data. XML can include links or instructions intended for other applications. Only import files from sources you trust, and do not enable content or follow links just because a file asks you to.

Troubleshooting

The From XML option is missing. Confirm that you are in the Excel desktop app and that the Data tab is visible. In Excel for the web, open the workbook in the desktop app. On a Mac, check the current Excel build’s Data or Get Data options; if the XML connector is unavailable, use the Windows desktop app rather than relying on a similarly named but different import command.

Excel reports that the XML is invalid or cannot be opened. The file may be incomplete, damaged, or not actually XML. Try opening a copy in a plain-text editor to see whether it contains readable tags such as <record>. Do not edit or resave it there unless you understand the format. If the file was downloaded, download it again from the original source. A web error page saved with an .xml extension will not become valid XML simply because of the filename.

The preview is blank or the data appears missing. Select a different item in Navigator, especially one lower in the hierarchy. Some XML files use namespaces or place records inside nested lists, so the root element may not display the actual records as a table. In Power Query, expand the relevant Record, Table, or List column and review the preview again.

Some rows or fields do not appear. Look for filters applied in Power Query and check whether the selected element contains all records. Compare the imported row count with the source or with a reliable count supplied by the data provider. Very large data may exceed worksheet limits, and optional XML fields may simply be absent from some records. If Excel shows a warning about a schema or mapping, the document may use a structure that Excel cannot map cleanly to a worksheet table.

Dates, decimals, or leading zeros are wrong. In Power Query Editor, select the affected column and choose an appropriate data type, or keep identifiers as text. For values that depend on regional conventions, inspect the original XML and confirm whether the source uses a day-first date or a decimal point or comma. Correct the data type before loading rather than trying to fix a large table cell by cell.

Refresh fails after the first import. Check whether the source file still exists at the location used by the query and whether you still have permission to access it. If the file was moved, update the source path in the query. If the XML structure changed, open the query for editing and adjust the selected element or expanded columns to match the new structure.

Frequently Asked Questions

Can I open an XML file in Excel without importing it?
Yes. Use File > Open > Browse and select the XML file. Excel may offer a read-only workbook or XML Source view, but Power Query is generally more useful for producing a clean table.

Does Excel change the original XML file?
No. Importing XML into a workbook reads the source and creates a separate result. Save your work as an Excel workbook, and keep the original XML if you want to preserve or reuse it.

Why does Excel show tags or nested fields instead of a normal table?
XML represents data hierarchically, so some elements contain other elements. In Power Query, expand the relevant Record, Table, or List field to expose the fields you want as columns.

Can Excel open a very large XML file?
Excel can process XML through Power Query, but the final worksheet is limited to 1,048,576 rows and 16,384 columns. If the data exceeds those limits or strains available memory, filter it, load only needed fields, or use a database tool.

Can I edit the imported data and save it back as XML?
You can edit the worksheet result, but saving a workbook as .xlsx does not update the source XML. Exporting data in the original XML structure may require an XML map or a purpose-built export process, especially if the source has nested elements.

Will the import instructions work in Excel for Mac or the web?
The steps are written for Excel for Microsoft 365 on Windows. Excel for the web lacks the same XML import tools, and XML connector availability can vary between Mac builds. Use the Windows desktop app if the required XML option is not available.

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.