How to Change XML to 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. Before you run it on a workbook other people depend on, try it on a copy or a few rows first. Undo only reaches back through the current session, so a quick trial run is the cheapest way to see exactly what will change before the file is saved and shared. For “change xml to excel”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected cells change, and only then apply the same setup to the full sheet. 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 is what makes a workflow that saves repeating the same clicks every week useful in real work: repeatable, auditable, and not dependent on memory or luck.

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. A practical tip: try it on a copy of the sheet or a handful of sample rows first and check the result before you apply it to the real data. That one habit catches almost every surprise while it is still cheap to reverse.

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. If you take one thing from this page on “change xml to excel”, make it the order of checks rather than the individual clicks: confirm what is selected, apply the step, and look at the result before moving on. That small routine is what keeps Excel work predictable when the same task comes back in a slightly different workbook.

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