What Most People Miss About Converting XML to Excel

Yes, Excel can open XML files directly. But if you double-click an XML file and expect a tidy spreadsheet, you’ll get raw tags, blank rows, and missing relationships — not a usable table.

The Problem

You’ve just received sales_report_2024_q1.xml from your ERP system. It contains 87 orders, customer names, product SKUs, dates, and amounts. You need to analyze it in Excel — filter by region, sum revenue by month, compare against last quarter. So you drag it into Excel… and see this:

A1	B1	C1	D1
<?xml version="1.0" encoding="UTF-8"?>
<orders>
<order id="ORD-7821">
<customer>Sarah Chen</customer>
<product_sku>XTR-904</product_sku>
<date>2024-03-15</date>
<amount>45200.00</amount>
</order>
<order id="ORD-7822">
<customer>Javier Mendoza</customer>
<product_sku>ZEN-117</product_sku>

That’s not a dataset — it’s a document. No headers. No consistent columns. No way to sort or pivot. And worse: Excel didn’t auto-detect the structure because the XML lacks a schema (.xsd) or inline DTD. You’re stuck copying-pasting, using Notepad++, or begging IT for a CSV export.

The Solution

Excel has built-in XML import — but it’s buried under Data > Get Data > From File, and only works reliably when you don’t use the legacy ‘Import XML’ button (which is deprecated and often fails on nested data). Here’s what actually works — tested on Excel 365, 2021, and LTSC:

Open Excel → go to the Data tab. Don’t click “From XML” in the legacy Import group (it’s grayed out in newer versions anyway).
Click “Get Data” → “From File” → “From XML”. Navigate to your file and select it. Click Import.
In the Navigator window, expand the root node (e.g., orders). You’ll see child nodes like order, customer, product_sku. Select order — that’s your repeating record.
Click “Transform Data” (not “Load”). This opens Power Query Editor — where the real magic happens.
In Power Query, right-click the column with XML attributes (e.g., Attribute:id) and choose “Remove Columns”. Then select all attribute columns (Attribute:*) and remove them.
Click “Expand” (the double-arrow icon) next to the main record column (e.g., order). Check every field you need: customer, product_sku, date, amount. Uncheck “Use original column name as prefix”.
Right-click each column → “Change Type”: set date to Date, amount to Decimal Number, product_sku to Text.
Click “Close & Load”. Your data lands cleanly in Sheet1 starting at A1 — no manual cleanup needed.

Here’s what you get after those 8 steps:

A1	B1	C1	D1	E1
customer	product_sku	date	amount	id
Sarah Chen	XTR-904	2024-03-15	$45,200.00	ORD-7821
Javier Mendoza	ZEN-117	2024-03-16	$12,850.00	ORD-7822
Priya Kapoor	XTR-904	2024-03-17	$45,200.00	ORD-7823
Liam O’Sullivan	TUR-202	2024-03-18	$8,990.00	ORD-7824
Anya Petrova	ZEN-117	2024-03-19	$12,850.00	ORD-7825
Diego Silva	XTR-904	2024-03-20	$45,200.00	ORD-7826
Maya Rahman	TUR-202	2024-03-21	$8,990.00	ORD-7827

You now have a proper table — ready for filters, PivotTables, and formulas like =SUMIFS(E2:E100,"XTR-904",B2:B100). And yes — this answers how do i convert xml to excel without add-ins or coding.

Going Further

Once you’ve got the basics down, three things will save you hours:

Reimporting updated XML: If your source file changes (e.g., nightly exports), just right-click any cell in your loaded table → “Refresh”. Power Query reruns the full transformation — even if the new XML adds fields like <region>EMEA</region>. Just go back to Power Query Editor, expand the new column, and change its type.
Handling hierarchical XML: Some files nest deeply — e.g., <order><customer><address><city>Berlin</city></address></customer></order>. Don’t panic. In Power Query, click the nested table icon (⧉) next to customer, then expand address, then expand city. It’s iterative — but predictable.
Converting XML with attributes + elements: If your XML looks like <item sku="XTR-904" price="45200.00">Premium Widget</item>, Power Query splits those into two columns automatically: Attribute:sku and Value. Rename Value to description, then promote the first row to headers if needed.
Shortcut tip (the surprising one): Press Alt+D+T to open the legacy XML Import Wizard — but only if your XML has an inline DTD or references an external XSD. It fails silently on most modern REST API exports. So unless you’re working with legacy EDI/XML from SAP R/3 circa 2005, skip it. Seriously — I wasted two mornings on this once.

When NOT to Use This

This method breaks down in four specific cases — and knowing when to walk away saves time:

Files over 50 MB: Excel’s XML parser chokes. You’ll get “Not enough memory” or freeze for 8+ minutes. For large files, use Python (pandas + lxml) or PowerShell — then paste the result into Excel.
XML with mixed content: E.g., <note>Shipped today — tracking #UPS123</note>. Excel can’t separate formatting from text. The whole block lands in one cell as plain text. Clean it in Notepad++ first with regex <[^>]+> → replace with nothing.
No repeating root: If your XML is flat — like <config><timeout>30</timeout><debug>false</debug></config> — there’s no “table” to build. Use Data → Get Data → From Other Sources → Blank Query, then enter =Xml.Tables(File.Contents("C:\data\config.xml")) manually.
Namespaces everywhere: If your XML starts with <root xmlns="http://example.com/ns">, Power Query ignores all elements unless you strip the namespace first. Add a step in Power Query: = Table.TransformColumns(#"Previous Step",{{"Column1", each Xml.Document(Text.Replace(_, "xmlns=\"http://example.com/ns\"", "")), type text}}).

Keyboard Shortcuts

These five shortcuts cut your XML import time by ~60% — especially when you’re doing batch imports:

Shortcut	Action	When to Use
`Alt+D+P`	Open Power Query Editor directly	After loading XML — skip the ribbon clicks
`Ctrl+Shift+F10`	Toggle column selection mode (for multi-column ops)	When expanding 5+ nested fields at once
`Alt+H+O+I`	Auto-fit column width	After expanding — avoids horizontal scrolling
`Alt+F5`	Refresh all queries	When reimporting daily XML dumps
`Ctrl+Shift+U`	Toggle formula bar (shows full XML path)	When debugging why a column won’t expand