FILTERXML
Returns specific data from XML content by using the specified XPath expression. Useful for parsing XML responses from web services (combine with WEBSERVICE).
FILTERXML(xml, xpath)Arguments
xmlA string in valid XML format.xpathA string in standard XPath format identifying the data to extract.
=FILTERXML(A1,"//book/title")Returns the text of the first <title> element under <book>
=FILTERXML(WEBSERVICE("https://..."),"//rate")Combines with WEBSERVICE to parse a live API response
=FILTERXML(A1,"//item[@id='1']/name")Uses an XPath predicate to filter by attribute
- •Available since Excel 2013 — not available in Excel for Mac or Excel Online
- •Returns #VALUE! if the XML is invalid or the XPath is malformed
- •If the XPath returns multiple nodes, results spill into adjacent cells (Excel 365)
- •Often paired with WEBSERVICE to consume REST/XML APIs directly from a sheet
- •Passing malformed or invalid XML to FILTERXML, which returns a #VALUE! error rather than attempting to parse a partial or best-effort result
- •Writing an XPath expression that doesn't match the actual structure of the XML, which also produces a #VALUE! error rather than an empty result
- •Forgetting that FILTERXML isn't available in Excel for Mac or Excel Online, so a formula built and tested on Windows desktop Excel can break for collaborators using those platforms
WEBSERVICEFetches raw content from a URL, commonly paired with FILTERXML to parse the XML response of a web API.TEXTJOINCan combine multiple values extracted by FILTERXML into a single delimited string.IFERROROften wraps FILTERXML to substitute a fallback value when the XML is invalid or the XPath finds no match.Why does FILTERXML return a #VALUE! error?
Either the XML string itself isn't valid XML, or the XPath expression doesn't correctly identify any matching node - double-check both independently before combining them.
Can I use FILTERXML with live web data?
Yes, it's commonly paired with WEBSERVICE, which fetches the raw response from a URL that FILTERXML then parses with an XPath expression.
Is FILTERXML available in Excel for Mac?
No, it's only available in Excel for Windows and requires Excel 2013 or later - formulas using it will break for Mac or Excel Online users.
Need to translate a formula using FILTERXML?
Use our translator to convert your complete formula