How to Create an XML File in 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. For “create an xml file in 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 layout choice that keeps the sheet readable and sortable 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 before you scale it up: build it once on a small block of test data, confirm the number against the tool on this page, and only then point it at your real sheet. That one habit catches almost every mistake while it is still cheap to fix, long before a wrong figure reaches a report or a colleague.

In Google Sheets

Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. 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, the tool proves it, and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Here is the takeaway for “create an xml file in excel”: copy the answer if you are busy, but if you have a spare few minutes, rebuild the example in Excel yourself with the tool above open beside it. That single pass — type it, run it, watch the result move when you change an input — is what turns a formula you found into a technique you trust. Keep your inputs labelled and referenced, never hard-coded, and the same sheet stays correct and auditable as it grows. Done that way, you will not need to look this up again, and you will be the person others ask.

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