FILTERXML Function

Excel 2013+ Windows desktop only

The Excel FILTERXML function pulls values out of an XML string using XPath — usually the response from WEBSERVICE. In Excel 365 a matching set spills, turning an API response into a column of values with one formula.

Quick answer: extract every <name> from an XML response in B2:

=FILTERXML(B2, "//name") // spills all matches in Excel 365

Syntax

=FILTERXML(xml, xpath)

Argument		Description
`xml`	Required	A string of valid XML (often a WEBSERVICE result).
`xpath`	Required	XPath query as text, e.g. `"//item/price"` for every price inside an item.

How to use it

The classic pipeline — call an API, then mine the response:

=FILTERXML(WEBSERVICE("https://api.example.com/rates.xml"), "//rate[@currency='EUR']")

And the famous pre-TEXTSPLIT trick: turn a delimited string into XML, then split it with XPath:

=FILTERXML("<t><s>" & SUBSTITUTE(B2, ",", "</s><s>") & "</s></t>", "//s") // splits B2 by commas - use TEXTSPLIT in 365

Windows desktop only: the Web functions (ENCODEURL, FILTERXML, WEBSERVICE) are not available in Excel for the Web, Excel for Mac, or mobile — cells show #NAME? there. For cross-platform data pulls, use Power Query (Data → From Web) instead.

Try it: interactive demo

Live demo

Pick an XPath query and see what it extracts from a sample product XML.

xpath

<catalog><product><name>Webcam Pro</name><price>129</price></product><product><name>Desk Mat XL</name><price>35</price></product><product><name>Monitor 32in</name><price>399</price></product></catalog>

Result:

Errors & common pitfalls

#VALUE! — invalid XML or XPath. The xml argument must be well-formed (every tag closed, one root element); HTML from a web page usually is NOT valid XML and fails. JSON fails too — FILTERXML speaks only XML.

Pitfall: expecting JSON support. Most modern APIs return JSON. FILTERXML can’t parse it — request an XML endpoint if one exists, or use Power Query, which parses JSON natively.

Power Query is usually the better tool. These functions predate Power Query; for refreshable, authenticated, multi-row web data, Data → Get Data → From Web wins on every axis. The Web functions remain handy for quick one-cell lookups.

Practice workbook

📊

Download the free FILTERXML practice workbook

Every example on this page, ready to open in Excel — plus practice challenges with answers on a separate tab. No sign-up required.

Frequently asked questions

Can FILTERXML parse JSON?

No — XML only. Most modern APIs default to JSON, so check for an XML endpoint (&format=xml is common) or use Power Query for JSON.

How much XPath does it support?

The practical core: paths (//item/price), indexes ([1]), attribute filters ([@id='x']), value predicates ([price>100]), attribute extraction (/@id). Complex axes and functions are hit-and-miss.

Why does my result show only the first match?

In pre-365 Excel, multiple matches need Ctrl+Shift+Enter across a range. In Excel 365 results spill automatically.

Is the SUBSTITUTE-to-XML splitting trick still useful?

Only in Excel 2013-2019 on Windows. In 365, TEXTSPLIT does it cleanly and cross-platform.

Master functions like this in one day

This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related functions: ENCODEURL · FILTERXML · WEBSERVICE · TEXTSPLIT · HYPERLINK