Want to extract structured data from an XML string directly into your spreadsheet? The FILTERXML function in Excel 🧩 lets you pull specific data points from XML-formatted content using XPath expressions. This makes it incredibly powerful for web scraping, API response parsing, and data transformation — and it’s compatible with Excel on Windows, Mac, and Ubuntu (via Excel Online or Office 365). 📥🧠
In this post, you’ll learn how FILTERXML() works, where it shines, how to write XPath queries, and how to use helpful keyboard shortcuts to boost your productivity.
🔍 What Is the FILTERXML Function?
Syntax:
=FILTERXML(xml, xpath)
xml: A valid XML string or reference to one.xpath: An XPath query to extract nodes or values.
✅ Returns one or multiple values that match the XPath expression.
💡 Real-Life Example of FILTERXML
XML Data (in cell A1):
<items>
<item>
<name>Apple</name>
<price>1.25</price>
</item>
<item>
<name>Banana</name>
<price>0.75</price>
</item>
</items>
Extract the first item’s name:
=FILTERXML(A1, "//item[1]/name")
✅ Output: Apple
📦 Extract all prices:
=FILTERXML(A1, "//price")
🧠 Excel will spill the values into multiple cells if supported (Excel 365+).
⚙️ XPath Quick Tips
| XPath Query | What It Does |
|---|---|
//item | Selects all <item> nodes |
//item[2]/price | Price of the second item |
//name | All name values in the XML |
//item/name | Names under each <item> |
📘 XPath is case-sensitive and space-sensitive — type carefully!
📊 Use Cases for FILTERXML in Excel
| Industry | Application |
|---|---|
| Finance | Extracting market data from XML feeds |
| E-commerce | Parsing product info from API responses |
| Education | Teaching structured data and XPath basics |
| Data Science | Cleaning and transforming web-sourced data |
| Marketing | Pulling lead or form data from CRM exports |
🌐 Especially useful when used with WEBSERVICE() for live data queries (Windows-only).
⌨️ Keyboard Shortcuts for FILTERXML Usage
| Action | Windows/Linux | macOS |
|---|---|---|
| Start formula input | = | = |
| Auto-complete FILTERXML | Type FILTERXML + Tab | Same on Mac |
| Enter formula | Enter | Return |
| Copy formula down | Ctrl + D | Cmd + D |
| Function dialog box | Shift + F3 | Fn + Shift + F3 |
💻 Combine with TEXTJOIN, CONCAT, or INDEX to manage multi-node outputs.
🧪 Combining FILTERXML with Other Functions
| Function Combo | Example | Purpose |
|---|---|---|
WEBSERVICE + FILTERXML | =FILTERXML(WEBSERVICE("url"), "//tag") | Extract data directly from a live XML URL |
TEXTJOIN | =TEXTJOIN(", ", TRUE, FILTERXML(A1, "//name")) | Combine multiple node values into one cell |
INDEX | =INDEX(FILTERXML(A1, "//item/name"), 2) | Return a specific item from a list |
IFERROR | =IFERROR(FILTERXML(A1, "//wrong"), "Not Found") | Handle missing or invalid XPath queries |
🧠 Use IFERROR() to clean up unpredictable XML inputs.
⚠️ FILTERXML Limitations
- ❌ Not available in Excel for Mac (older than Office 365)
- ❌ Doesn’t support non-well-formed XML
- ❌ Cannot parse JSON (use Power Query or scripts instead)
- ✅ Only works in Windows Excel versions (except in Excel Online for Mac/Linux)
🔐 Ensure that XML strings are clean, closed, and correctly nested.
📱 Compatibility: FILTERXML on Desktop, Web & Mobile
| Platform | Support |
|---|---|
| Windows (Excel 365) | ✅ Full Support |
| macOS (Excel 365) | ⚠️ Limited (Online only) |
| Ubuntu/Linux (via web) | ✅ Excel Online |
| Excel Mobile | ❌ Not Supported |
📌 Best used on Excel 365 desktop or online with XML sources from APIs or exported tools.
💬 FAQs About Excel FILTERXML Function
What does the FILTERXML function do in Excel?
It parses an XML string and extracts values using XPath expressions.
Can FILTERXML be used with live web data?
Yes — combine with WEBSERVICE() on Excel for Windows.
Does FILTERXML support JSON?
No — it only works with valid XML. Use Power Query or scripts for JSON.
Can FILTERXML return multiple values?
Yes, and in Excel 365, it spills results into adjacent cells.
Why is FILTERXML returning #VALUE!?
The XML might be malformed, or the XPath expression is invalid.
Is FILTERXML available on Mac?
Not natively — but it works in Excel Online or via Office 365.
🟢 Final Thoughts: FILTERXML Function Excel
The FILTERXML function in Excel 🧩 is a powerful tool for anyone dealing with structured data. Whether you’re pulling product info from APIs, extracting pricing from supplier files, or working with web-sourced XML feeds, FILTERXML() helps you parse and analyze data directly in your workbook. Add in a little XPath magic and you’ve got a seriously versatile formula!
Complete List of Windows Keyboard Shortcuts
If you need help for Windows, you can find a whole list of all keyboard shortcuts here.
https://keyboard-shortcuts.org/
Advertisment