The first thing most people do when they see an XML file attached to an email or downloaded from a vendor is double-click it — expecting Excel to open it like a .xlsx file. That’s almost always the wrong move. Excel doesn’t natively run XML like a spreadsheet; it maps, imports, or exports it using structured rules. You’ll get a blank screen, an error about schemas, or worse — garbled text in column A. (Trust me, I learned this the hard way after spending 47 minutes trying to ‘open’ a 12MB SAP export named invoice_data_2024Q2.xml.)
Quick Answer
No — Excel is not XML. XML is a markup language for structuring data; Excel is a spreadsheet application. But Excel can import XML as tables, export ranges to XML, and map XML schemas to worksheets — if you use the right method and avoid the default double-click trap.
All the Methods
| Method | Time for 10K rows | Accuracy | Difficulty |
|---|---|---|---|
| Import via Data > Get Data > From File > From XML | ~18 sec | High (auto-detects hierarchy) | Low |
| Open with XML Source pane + Schema mapping | ~2 min (setup) | Very High (schema-bound) | Medium-High |
| Paste special > Text (then parse with TEXTSPLIT) | ~45 sec | Medium (depends on consistency) | Medium |
| Export range to XML using mapped schema | ~6 sec (after mapping) | Very High (validates against XSD) | High |
| Power Query XML.Native() (M code) | ~12 sec | Highest (handles nested arrays) | High |
Method 1 Deep Dive
This is the fastest, safest path for one-off imports — especially if you’re handed raw XML without a schema. Go to Data tab → Get Data → From File → From XML. Excel will scan the file and show a preview. Click Load. It lands in a new worksheet as a table — but here’s the counterintuitive part: don’t edit that table directly. If you delete a row or sort it, Excel breaks the XML link. Instead, copy the data (Ctrl+C), then paste values into A1 of a clean sheet (Alt+E+S+V).
Try it with this snippet (save as orders.xml):
<?xml version="1.0" encoding="UTF-8"?>
<Orders>
<Order ID="ORD-7821" Date="2024-03-15">
<Customer>Sarah Chen</Customer>
<Amount>45200</Amount>
<Status>Shipped</Status>
</Order>
<Order ID="ORD-7822" Date="2024-03-16">
<Customer>Acme Corp</Customer>
<Amount>12950</Amount>
<Status>Pending</Status>
</Order>
</Orders>
After importing, you’ll see columns: ID, Date, Customer, Amount, Status. The ID and Date come from attributes — Excel pulls those automatically. Values go into rows starting at A2. No manual parsing needed.
Method 2 Deep Dive
This is for repeatable, validation-critical work — say, importing daily EDI files that must match your company’s XSD schema. First, go to Developer tab → XML → XML Source. If Developer isn’t visible, enable it via File > Options > Customize Ribbon. Then click XML Maps → Add and select your .xsd file (not the .xml!). You’ll see a tree of elements on the right. Drag Customer onto cell A1, Amount onto B1, etc. Now, go to Data tab → Get Data → From File → From XML — but this time, choose Import to existing worksheet and point to your mapped range (A1:E100). Excel will auto-fill only cells bound to the schema.
Here’s what most miss: the mapping stays active even after closing/reopening. So next time, just drop a new orders_v2.xml into the same folder and hit Alt+A+R+R (Data > Refresh All). Your A1:E100 updates — and if a field violates the XSD (e.g., <Amount>abc</Amount>), Excel throws a clear error instead of silently inserting #N/A.
Sample mapped output (starting at A1):
| ID | Date | Customer | Amount | Status |
|---|---|---|---|---|
| ORD-7821 | 2024-03-15 | Sarah Chen | 45200 | Shipped |
| ORD-7822 | 2024-03-16 | Acme Corp | 12950 | Pending |
| ORD-7823 | 2024-03-17 | Terra Logistics | 8760 | Delivered |
| ORD-7824 | 2024-03-18 | Nexus Labs | 32400 | Shipped |
| ORD-7825 | 2024-03-19 | Vista Systems | 6500 | Processing |
Cheat Sheet
| Action | Shortcut / Steps | Notes |
|---|---|---|
| Import XML (no schema) | Data → Get Data → From File → From XML → Load | Paste values immediately if editing needed |
| Open XML Source pane | Alt+D+L+X (or Developer → XML → XML Source) | Requires Developer tab enabled |
| Refresh all XML maps | Alt+A+R+R | Only works if maps are defined and data linked |
| Export range as XML | Right-click mapped range → Export → XML... | Fails if data violates schema — good for QA |
| Split raw XML text | =TEXTSPLIT(A1," | Use with TRIM() and SUBSTITUTE() for cleanup |
| Validate XML structure | Online: https://www.xmlvalidation.com/ (paste content) | Never skip this before mapping — saves hours |
