What Most People Miss About Opening XML in Excel

Yes, XML files can be opened in Excel — but only if you treat them like structured data sources, not documents. If you double-click an XML file expecting a clean spreadsheet, you’ll likely get scrambled tags, missing attributes, or a blank sheet with a cryptic error bar.

The Problem

You’re handed sales_report_2024.xml from your logistics vendor. You double-click it. Excel opens — but instead of columns for OrderID, CustomerName, and ShipDate, you see this:

Symptom	Cause	Fix
Empty sheet with yellow warning bar saying "XML Source"	Excel loaded the schema but no data — file lacks inline schema or is malformed	Use Data → Get Data → From File → From XML (not Open)
All data crammed into column A as raw XML strings	Excel treated it as plain text, not structured XML	Don’t use File → Open. Use Data tab → Get Data → From XML
Missing nested fields like <Address><City>Seattle</City></Address>	Excel flattens hierarchy by default unless you map elements manually	In Power Query Editor, expand nested records using the double-arrow icon next to the column
Dates like 2024-03-15 appear as 45365 (serial numbers)	XML date values imported as text, then auto-converted incorrectly on paste	Change column type to Date *before* loading — right-click column header in Power Query → Change Type → Date

This isn’t theoretical. Last week, Sarah Chen at Acme Corp tried opening inventory_feed.xml from their warehouse API. She got 127 rows — all in A1:A127. The <Item> elements were invisible. Her team spent 90 minutes reformatting manually before realizing Excel never parsed the structure.

The Solution

Here’s how to actually open XML in Excel — correctly, reliably, and without losing relationships between parent/child elements:

1. Don’t double-click. Close Excel if it’s open. Launch it fresh.
2. Go to the Data tab → click Get Data → From File → From XML. (Alt + A → T → X)
3. Browse to your file — say C:\Reports\orders_q1.xml — and click Import.
4. In the Navigator window, select the root table you want (e.g., Orders). Uncheck "Enable load" if you plan to transform first.
5. Click Transform Data to open Power Query Editor.
6. Find columns with small double-arrow icons (▶) — those are nested tables. Click one (e.g., Customer) → choose Expand → check CustomerName, Country, and Region.
7. Right-click the OrderDate column → Change Type → Date. Same for Amount → Decimal Number.
8. Click Close & Load. Your data lands cleanly in Sheet1, starting at A1.

After following these steps, Sarah’s orders_q1.xml became this — no manual cleanup needed:

OrderID	CustomerName	Country	OrderDate	Amount
ORD-7821	Ling Zhang	China	2024-01-12	$2,450.00
ORD-7822	Miguel Ruiz	Mexico	2024-01-14	$1,890.50
ORD-7823	Amina Diallo	Senegal	2024-01-16	$3,120.75
ORD-7824	James Okafor	Nigeria	2024-01-18	$985.30
ORD-7825	Priya Mehta	India	2024-01-20	$4,210.00

Going Further

If your XML has repeating groups (like multiple <LineItem> per order), don’t just expand once. In Power Query, after expanding LineItems, you’ll see another ▶ next to that column — click it again to drill into product codes, quantities, and unit prices. That’s how you get true relational structure — not flat junk.

Need to refresh? Right-click any cell in your loaded table → Refresh. Or press Alt + F5. Excel will re-read the XML file and update all transformations — even if the file changed on disk.

One counterintuitive tip: If your XML uses namespaces (e.g., <ns:Order>), Power Query sometimes chokes. Before importing, open the file in Notepad++, delete the namespace declaration (xmlns:ns="http://example.com/ns") and replace ns: prefixes with nothing. Save as orders_clean.xml. Then import. Yes — editing raw XML is faster than fighting namespace parsing.

When NOT to Use This

Don’t use Excel’s XML import if your file is over 5 MB. Excel may hang, crash, or truncate data silently. For large feeds, use Python + pandas or a dedicated ETL tool — then export CSV for Excel.

Avoid this method if your XML contains CDATA sections with embedded HTML or scripts. Excel strips or misinterprets them. Open in a browser or XML editor first to verify integrity.

Never import XML containing sensitive PII (like full SSNs or credit card numbers) directly into Excel — especially if sharing the workbook later. Power Query caches raw source data in memory. Clear credentials and disable query history under File → Options → Data → Privacy.

Keyboard Shortcuts

Action	Shortcut	Notes
Open Power Query Editor	Alt + A → T → X	Data tab → Get Data → From XML
Refresh all queries	Alt + F5	Works even if focus is in a cell or formula bar
Expand nested column	Ctrl + Click on ▶ icon	Faster than right-click → Expand
Apply & Close in Power Query	Ctrl + S	Saves and loads — not just save!