Why does your XML file open as garbled text in Excel? Why does 'Open With' show Excel but then crash on load? Why does it work for your vendor’s sample file but fail on your own exported invoice data?
The Problem
You get an XML file from a supplier, a government portal, or an internal system — say invoices_2024_q2.xml. You double-click it. Excel launches… then shows a blank workbook, an error dialog ('XML file is not well-formed'), or worse: a single column of angle-bracket soup in A1 like <?xml version="1.0"?><Invoices><Invoice id="INV-7821"><Vendor>Acme Corp</Vendor><Amount>45200.00</Amount>.
This isn’t broken Excel. It’s Excel doing exactly what it was designed to do — and most users don’t know the difference between *parsing* XML and *importing* it.
| Symptom | Cause | Fix |
|---|---|---|
| Single-column wall of text in A1 | Excel opened the file as plain text, not structured XML | Use Data → Get Data → From File → From XML (not File → Open) |
| Error: "The specified XML source does not refer to a schema" | File has no inline XSD or Excel can’t infer structure | Import via Power Query and promote headers manually |
| Data appears but dates are 45201 instead of 2024-03-15 | Excel auto-converted ISO date strings to serial numbers | Change column type to Text *before* loading, or use Power Query’s Date.FromText() |
| Only first 1000 rows imported | Legacy XML import wizard truncates large files | Use Power Query (Data → Get Data → From File → From XML) — handles 100k+ nodes |
| Nested elements (e.g., <LineItems>) collapsed into one cell | Default import treats hierarchy as flat text | Expand nested tables in Power Query using the double-arrow icon in column header |
The Solution
Can Excel open XML? Yes — but only if you bypass the File → Open menu entirely. The correct path is Data tab → Get Data → From File → From XML. This launches Power Query, which respects XML structure, namespaces, and nesting.
- Go to Data tab, click Get Data → From File → From XML.
- Navigate to your file (e.g.,
C:\Exports\orders_20240617.xml) and click Import. - In Power Query Editor, you’ll see a single column named Content. Click the double-arrow icon ▶️ next to it — this expands the root element.
- If your XML has repeating records (like <Order>), find that table column and click its expand icon again. You’ll now see columns like OrderID, CustomerName, TotalAmount.
- Right-click each column header → Change Type → choose Text, Decimal Number, or Date as needed. For dates like
2024-06-17, select Date — Power Query parses ISO format correctly. - Click Close & Load. Your data lands in a new worksheet starting at A1, fully structured and editable.
Try it with this real sample — here’s what loads after expansion:
| OrderID | CustomerName | TotalAmount | OrderDate | Status |
|---|---|---|---|---|
| ORD-9812 | Sarah Chen | $24,890.50 | 2024-06-12 | Shipped |
| ORD-9813 | BrightLine Logistics | $11,200.00 | 2024-06-13 | Processing |
| ORD-9814 | Nexus Labs Inc | $8,450.75 | 2024-06-14 | Pending |
| ORD-9815 | Veridian Systems | $32,100.00 | 2024-06-15 | Shipped |
| ORD-9816 | TerraForm Group | $19,630.20 | 2024-06-16 | Shipped |
The beauty of this approach is that Excel never touches the raw XML string — Power Query handles parsing, type inference, and expansion before any data hits your worksheet. No macros. No add-ins. Just native Excel 365 / 2021.
Going Further
You can open XML in Excel — but can you refresh it? Yes. After loading, go to the Data tab → Queries & Connections → right-click your query → Properties. Check Refresh data when opening file. Now every time you open the workbook, Excel re-reads the XML file.
Need to merge multiple XML files? In Power Query, use Get Data → From File → From Folder, filter for *.xml, then combine and expand across all files. Works with 27 XML invoices from different departments — no manual copy-paste.
Here’s the counterintuitive tip: If your XML uses namespaces (e.g., <ns:Invoice>), don’t panic. Power Query ignores prefixes by default. But if values disappear, click Advanced Editor and replace Xml.Tables with Xml.Tables(Source, [Namespaces = [ns="http://example.com/invoice"]]). One line — saves hours.
When NOT to Use This
Don’t use Excel’s XML import if your file is >500 MB — Power Query will stall or crash. Use Python (xml.etree.ElementTree) or command-line tools like xmlstar first, then load the cleaned CSV.
Avoid it if your XML contains CDATA sections with HTML or scripts — Excel strips them silently. You’ll lose embedded notes or formatted descriptions.
And crucially: don’t use File → Open → Select XML → OK. That triggers Excel’s legacy ‘XML Spreadsheet’ mode (a proprietary .xml format from 2002). It only works with files explicitly saved *from Excel* as XML — not generic XML exports. That’s why double-clicking fails 9 times out of 10.
Also skip this method if you need XPath queries or conditional transforms. Excel doesn’t support XPath in import — use Power BI or Altova XMLSpy for that level of control.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Power Query XML import | Alt → A → T → X | Alt+A opens Data tab, T=Get Data, X=From XML |
| Expand nested table in Power Query | Ctrl + Click on expand icon | Loads all columns, not just selected ones |
| Open Queries & Connections pane | Alt + F3 | Fast access to refresh or edit queries |
| Toggle Power Query Editor | Alt + F12 | Jump between Excel sheet and editor without mouse |
