Excel to XML: export a sheet as XML (and import XML back)

Systems that want XML need it in a specific shape. Excel can produce it once you tell it that shape with an XML map. For a small one-off, a formula is faster.

Way 1: XML map (the proper way)

1. Show the Developer tab (right-click ribbon › Customize the Ribbon › Developer).
2. Save this as schema.xml in Notepad, editing the field names to match your headings:

<?xml version="1.0"?>
<rows>
  <row><name>x</name><city>x</city></row>
</rows>

3. Developer › Source › XML Maps › Add › pick schema.xml › OK (click OK on the “no schema” note).
4. Drag row from the panel onto cell A1. Your table is now mapped.
5. Developer › Export › name the file › Save.

Way 2: a formula (small sheets)

Headings name, city in A1:B1. In C2:

="<row><name>"&A2&"</name><city>"&B2&"</city></row>"

Drag down, copy column C, paste into Notepad between <rows> and </rows>, save as .xml.

Import XML into Excel

Data › Get Data › From File › From XML › pick the file › choose the table › Load. Or Developer › Import.

Check it worked

Open the .xml in a browser — it shows a coloured tree with no error. Or paste it into an online XML validator.

Common mistakes

“Non-exportable map” — the mapped range has merged cells or a list inside a list. Keep it a flat table.

& in the data breaks XML. Replace with &: SUBSTITUTE(A2,"&","&").