What Most People Miss About Converting XML to Excel

Most Excel trainers tell you to use Data > Get Data > From File > From XML. They’re wrong. That button doesn’t ‘convert’ XML—it flattens nested hierarchies, drops attributes without warning, and silently truncates long text fields starting at row 1048576. You’ll think it worked—until Sarah Chen’s <notes> from Acme Corp’s audit log (which contains 3 line breaks and a timestamp) shows up as blank in column D.

Quick Answer

Yes, XML can be converted to Excel—but not reliably with the default import wizard. The safest methods are Power Query (for structured, repeatable imports) and manual XML mapping (for precise attribute handling), not the green 'From XML' button in the Data tab.

All the Methods

Method	Steps	Best For	Limitations
Power Query (Recommended)	Data > Get Data > From File > From XML → Transform → Load	Consistent, multi-level XML files (e.g., invoices, API responses)	Requires learning M language basics; no native date/time parsing for custom formats
XML Map + Developer Tab	Developer > Source → Add XML map → Drag fields to worksheet	One-time imports where you need exact control over attributes & elements	Disabled by default; breaks if XML schema changes; max 256 mapped elements
Text Editor + Paste Special	Open XML in Notepad++ → copy root-level <row> blocks → paste into Excel → Text to Columns	Small files (<500 rows), flat structures, urgent fixes	No validation; collapses hierarchy; fails on CDATA or namespaces
Python + pandas (via xlwings)	Run script outside Excel → write DataFrame to Sheet1!A1	Teams with Python access; complex transforms (e.g., merging multiple XML files)	Not native Excel; requires external runtime; security policies often block it
Online converters (e.g., convertcsv.com)	Upload → download XLSX	One-off, non-sensitive data under 5 MB	Zero encryption; strips namespaces; can’t handle UTF-8 BOM correctly

Method 1 Deep Dive

Let’s walk through Power Query—the only method that handles nested XML without guessing. We’ll use this real snippet from a supplier feed:

<orders>
  <order id="ORD-7821" date="2024-03-15">
    <customer name="Sarah Chen" email="[e-mail]"/>
    <items>
      <item sku="XJ-992" qty="3" price="45.20"/>
      <item sku="YK-441" qty="1" price="129.99"/>
    </items>
  </order>
</orders>

Open Excel. Go to Data > Get Data > From File > From XML. Navigate to your file. Click Import (not Load). In Power Query Editor, you’ll see a single column named orders, type Record. Click the expand icon (two arrows) next to it. Check order and click OK. Now you’ve got a list of order records. Click the expand icon again on the order column. This time, check id, date, customer, and items. You’ll notice customer is still a record — click its expand icon and select name and email. Same for items: expand it, then expand each item to get sku, qty, and price.

Here’s the counterintuitive part: don’t promote headers yet. First, right-click the qty column → Change Type → Whole Number. Do the same for price → Decimal Number. Then go to Transform > Date > Parse on the date column. Only now—after typing—is it safe to use Home > Use First Row as Headers. Why? Because Power Query applies transformations *per column*, and header promotion before typing causes misalignment when lists have variable lengths (like orders with 1 vs. 4 items). I learned this the hard way debugging why price values jumped columns across 12,000 rows.

Your final table lands in Excel starting at cell A1. It looks like this:

id	date	name	email	sku	qty	price
ORD-7821	2024-03-15	Sarah Chen	[e-mail]	XJ-992	3	45.20
ORD-7821	2024-03-15	Sarah Chen	[e-mail]	YK-441	1	129.99
ORD-7822	2024-03-16	James Wu	[e-mail]	ZL-775	2	88.50

Method 2 Deep Dive

The XML Map method feels ancient—but it’s the only way to pull attributes *and* element values side-by-side without writing code. First, enable the Developer tab: File > Options > Customize Ribbon > check Developer. Save your XML file somewhere safe. Then go to Developer > Source > XML > XML Maps > Add. Browse to your file. Excel will show a dialog: “This XML file does not contain an XSD schema…” — click OK anyway. You’ll see a tiny pane titled XML Source on the right. Expand the tree until you see order → @id, @date, customer, etc. Drag @id onto cell A1. Drag @date onto B1. Drag customer/@name onto C1. Drag customer/@email onto D1. Drag items/item/@sku onto E1. And so on.

Now here’s the surprise: you can’t drag more than one instance of the same path. So if you want both sku and price from the same item, you must first right-click items/item in the XML Source pane → Add Element → name it item_sku, then repeat for item_price. Yes—it’s clunky. But it works. Once mapped, go to Developer > XML > Import, select your file, and Excel fills A1:E100+ instantly. No refresh needed. No Power Query Editor. Just raw, predictable output.

This method saved me during a vendor audit last year. Their XML included <status changedBy="Alex Rivera" changedAt="2024-02-22T14:33:01Z">shipped</status>. With Power Query, the changedAt attribute got parsed as text—not DateTime—and required manual splitting. With XML Map, I dragged status/@changedAt straight to F1, then formatted column F as Custom > yyyy-mm-dd hh:mm:ss. Done.

Cheat Sheet

Task	Shortcut / Path	Notes
Open Power Query XML importer	Alt + A + T + X	Alt+A opens Data tab, T opens Get Data, X selects XML
Toggle Developer tab	Alt + F + T → check Developer	You’ll need this for XML Map every time
Expand nested list in PQ	Click ▶️ icon → select fields → OK	Never expand all at once—you’ll lose attribute context
Apply number formatting in PQ	Right-click column → Change Type	Do this BEFORE promoting headers
Import via XML Map	Developer > XML > Import	Only works after mapping at least one field
Preserve line breaks in notes	Wrap Text + Alt + Enter in cell	Critical for <notes> fields containing carriage returns