FILTERXML Function in Microsoft Excel: Syntax, Examples & Troubleshooting
Extract local XML data in Excel with FILTERXML and XPath. Use a fixed offline fixture, select attributes and nodes, understand array output and Windows-only support.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: FILTERXML(xml, xpath) extracts matching data from an XML string. A local string is enough; no website, API, account or live web request is required for the examples below. Microsoft states that the function is unavailable in Excel for Mac and Excel for the web.
Syntax and platform requirements
=FILTERXML(xml, xpath)
Both arguments are required. xml must be a valid XML string, and xpath is a standard XPath expression selecting the wanted nodes or attributes. The function parses the supplied text; it does not download a URL merely because that URL appears in a cell.
The reference notes that FILTERXML may appear in the Mac function gallery but relies on Windows features and will not return results there. Do not equate a visible gallery entry with working support.
Worked example: extract names from a fixed XML document
Paste this plain text into A2:
<orders><order id="A1"><item>Pen</item><qty>3</qty></order><order id="A2"><item>Pad</item><qty>5</qty></order></orders>
- In C2 enter
=FILTERXML(A2,"//order/item"). - Reserve C2:C3 for the two selected values.
- Expected text sequence: Pen, then Pad.
In current Microsoft 365 with dynamic arrays, confirm in the top-left cell with Enter. In a legacy array workflow, select both output cells first and confirm with Ctrl+Shift+Enter. Excel supplies array braces itself. Clear the intended output area before entering a spilling formula.
The XML cell contains the complete document. It is not a formula and does not require escaping quotation marks with a backslash. If constructing XML inside an Excel quoted formula instead, quotation marks require Excel’s own doubling convention.
Second example: select an attribute and one matching record
=FILTERXML(A2,"//order/@id") is expected to return A1, then A2. The @ symbol selects the id attribute rather than a child element.
=FILTERXML(A2,"//order[@id='A2']/item") is expected to return Pad. The predicate narrows the selection to the order whose id attribute equals A2. The single quotes inside the XPath string coexist with Excel’s double quotes around the overall expression.
These results are independently checked by parsing the fixed document structure; they are not outputs captured from Excel or a remote service.
Advanced application: separate text extraction from numeric use
Extract a single quantity with =FILTERXML(A2,"//order[@id='A2']/qty") and explicitly convert its numeric text when needed, for example =VALUE(FILTERXML(A2,"//order[@id='A2']/qty")). Its expected numeric result is 5, and multiplying by a unit price of 2.5 gives 12.5.
The reviewed Excel page does not establish compatibility with every scalar XPath aggregation expression. Keep the XPath focused on selecting nodes and use ordinary worksheet arithmetic for the fully specified calculation rather than assuming a separate XML parser’s entire expression behavior applies to Excel.
For a two-row report, extract item and quantity nodes using the same order selection so their rows stay aligned. Independently the quantities are 3 and 5, totaling 8. Do not combine differently filtered columns and assume their row positions still represent the same orders.
Troubleshooting and boundaries
Microsoft documents #VALUE! for invalid XML and for a namespace with an invalid prefix. A missing closing tag such as <orders><order></orders> is not valid XML. XML is case-sensitive, so item and Item are different names.
A blocked dynamic-array range can prevent output expansion. A legacy selection smaller than the match count can conceal values. The reference does not establish a native result for every no-match or unsupported XPath case, so do not invent an error-code rule from another parser.
Keep XML input bounded and inspect its source before using it. These examples do not execute an external web request or rely on the obsolete stock-service URL shown in the vendor’s older examples.
Compatibility and verification
Microsoft’s reference checked on October 17, 2026 lists: Excel for Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016. Microsoft’s alphabetical catalog, checked October 17, 2026, identifies Excel 2013 as the introduction-version marker for FILTERXML. This perpetual-release marker does not establish an earlier Microsoft 365 preview or rollout date. Platform restrictions remain separate. The last functioning version is unverified; no retirement is asserted.
Example verification: Expected numerical results are independently calculated; text and shape expectations follow the documented contract. These examples have not been tested in Microsoft Excel. No screenshot or native execution is claimed.
Related guides and source
VALUE in Excel | SUM in Excel | IFERROR in Excel
- Microsoft FILTERXML reference, retrieved October 17, 2026.