How Do I Create an XML File from 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. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “create an xml file from excel”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the tool above before you touch the real workbook. 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. The point is a layout choice that keeps the sheet readable and sortable, but the practical win is that someone else can open the file and understand what happened without asking you.

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. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.

In Google Sheets

Google Sheets handles this almost identically to Excel. The formula syntax above is the same, and the menu lives under a slightly different label rather than a ribbon tab. Use the platform toggle at the top of the page to switch every keyboard shortcut between Windows and Mac, and expect at most cosmetic differences in naming. 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. If you take one thing from this page on “create an xml file from excel”, make it the habit rather than the keystrokes: set the problem up with labelled inputs, reference those cells, and let Excel do the recomputing. Bookmark the page for the syntax, but do the example once in a blank sheet and check it against the tool above — five minutes of hands-on practice fixes the method in memory far better than re-reading, and it surfaces the small snags while they are still harmless. After that the technique is genuinely yours: faster than searching for it again, and reliable enough to drop into work that other people depend on.

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