Quick Navigation

FILTERXML Function

Excel 2013+, Excel 365

Summary

FILTERXML extracts specific data from XML content using XPath queries. This powerful function enables Excel users to parse structured XML data directly within spreadsheets, making it ideal for processing web service responses, API data, and XML documents.

Syntax

FILTERXML(xml, xpath)

Parameters

Parameter	Type	Required	Description
xml	`String`	Yes	A valid XML-formatted string serving as the data source
xpath	`String`	Yes	XPath query string that specifies the target data elements

Using the FILTERXML Function

FILTERXML transforms Excel into a lightweight XML processor. Use it to extract structured information from web APIs, parse RSS feeds, process XML reports, or handle any XML data source. Combine with WEBSERVICE for dynamic data extraction from online sources.

Common FILTERXML Examples

Extract Wikipedia Recent Changes Titles

=FILTERXML(WEBSERVICE("https://en.wikipedia.org/w/api.php?action=query&format=xml&list=recentchanges&rclimit=3"),"//rc/@title")

Returns array of 3 most recent page titles from Wikipedia API as dynamic array (Excel 365) or requires array formula entry (older versions)

Get Stock Last Price from API

=FILTERXML(WEBSERVICE("http://dev.markitondemand.com/MODApis/Api/Quote/xml?symbol="&ENCODEURL("AAPL")),"//LastPrice")

Extracts current last price for Apple stock (AAPL) from Markit on Demand XML API response

Extract Multiple XML Nodes

=FILTERXML("<invoice><item price='10.99'>Widget</item><item price='25.50'>Gadget</item></invoice>","//item")

Returns array: {"Widget","Gadget"} from inline XML string

Extract XML Attributes

=FILTERXML("<invoice><item price='10.99'>Widget</item></invoice>","//item/@price")

Returns "10.99" - extracts attribute value using @ syntax

Frequently Asked Questions

Occurs when XML is invalid, XPath is malformed, or namespace prefixes are incorrect. Verify XML structure and XPath syntax.

No. FILTERXML requires Windows Excel features and is unavailable in Excel for Mac or Excel for the web.

In Excel 365, returns dynamic arrays automatically. In older Excel, select output range first and use Ctrl+Shift+Enter.

No, only valid XML. Use other methods like Power Query for JSON parsing.

Common Errors and Solutions

#VALUE! error

Cause: Invalid XML format or malformed XPath

Solution: Validate XML string and test XPath expression separately

Empty results

Cause: XPath doesn't match any nodes in XML

Solution: Use browser XPath tester or simplify query (e.g., //* for all elements)

Single cell instead of array

Cause: Older Excel version without dynamic arrays

Solution: Select full output range before entering formula, press Ctrl+Shift+Enter

Network timeout errors

Cause: WEBSERVICE call fails

Solution: Check internet connection and API endpoint availability

Notes

Windows-only function (Excel 2013+ desktop)
Returns #VALUE! for invalid XML or XPath
Array formulas spill automatically in Excel 365
Use double quotes within XML strings: "<tag>\"value\"</tag>"
XPath supports: // (descendants), / (children), @attr (attributes)
Maximum XML size limited by cell string limits (32,767 characters)

Compatibility

Available in: Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel 365 (Windows desktop only)

Not available in: Excel for Mac, Excel for the web, Excel 2010 and earlier, Excel Mobile

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2013+, Excel 365