How to Import a XML File Into Excel

What this does

Excel offers three routes and they suit different jobs. Power Query (Get Data ▸ From XML) parses the file into a table you can expand node by node and refresh later. File ▸ Open reads the file straight into a sheet, inferring a schema and flattening repeated elements, which is quick but not repeatable. The Developer ▸ Source pane maps an XML schema onto specific cells, which is the only route that also exports back out to XML. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. Treat “import a xml file into excel” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That turns a data step that keeps your analysis trustworthy into a method you can reuse, explain, and defend when the workbook leaves your screen.

A worked example

orders.xml nests several Item elements inside each Order. Data ▸ Get Data ▸ From File ▸ From XML, then click the expand arrow on the Items column and choose Expand to New Rows. Twelve orders become forty-one item rows, and Close & Load writes them to a table that Refresh All updates when the file changes. XML arrives from systems that will send it again next month, so the difference between a one-off open and a refreshable query is the difference between a task and a chore. When the result is not what you expected, undo straight away rather than repairing it by hand — undo restores the sheet exactly, while manual fixes tend to leave small inconsistencies behind that surface later.

In Google Sheets

This is a command rather than a formula, so there is no syntax to carry over. Google Sheets arranges its commands in menus across the top instead of a ribbon, names some of them differently and does not have every Excel command — look for the equivalent in the Sheets menus or its help before assuming the steps match. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. The short version of “import a xml file into excel”: the answer is at the top of this page, and the sections above explain why each step matters, so a variation of the problem does not stump you. Try it once on a copy of a real file and the steps stick far better than re-reading them.

Common mistakes

• Using File ▸ Open on nested XML and accepting the inferred schema, which flattens repeating elements into columns that no longer line up.
• Expecting FILTERXML to read a file — it parses XML held in a cell as text, typically fetched by WEBSERVICE.
• Trying to export to XML without an XML map; a plain sheet has nothing to export against.
• Ignoring namespaces, which are why an element that is visibly in the file may not appear in the Navigator.

Frequently asked questions