The FILTERXML function is a Web formula that can return specific data from an XML content, using a given XPath. This ability makes the function suitable for use in conjunction with the WEBSERVICE function. The FILTERXML function is available for 2013 and later versions of Excel on the Windows platform. In this guide, we’re going to show you how to use the FILTERXML function, and also go over some tips and error handling methods.


Supported versions

  • Excel 2013 and later versions for Windows. Not available in Excel Online and Excel for Mac.

FILTERXML Function Syntax

FILTERXML(xml, xpath)


Arguments

xml An XML string.
xpath A string specifying XPath.

Examples

FILTERXML Function's Single Value Return

The FILTERXML function requires two arguments: The first is the XML text, and the second is the query language called XPath. The XPath stands for XML Path Language, and it uses a “path-like” syntax to identify the where the data is to be placed. “Path-like” here means a string that starts with double slashes, and contains a single slash for each folder (tag operator in XML).

Here is our sample data which contains information about stock quote for MSFT:


    
        SUCCESS
        Microsoft Corp
        MSFT
        137.08
        -1.82
        -1.3102951764
        Tue Jul 16 00:00:00 UTC-04:00 2019
        1050419091440
        22726128
        101.57
        34.9611105641
        139.05
        136.52
        138.96
    

If we need to grab the Name value from this XML, we need to use an XPath string like this:

 

//QuoteApiModel/Data/Name

Let's see how it works in Excel:

=FILTERXML(B1,"//QuoteApiModel/Data/Name")

FILTERXML Function's Array Return

An alternative way to use the FILTERXML function is using it as an array formula to return all values with the same tags. This usage comes in handy if the return XML contains similar data, such as the dishes in a breakfast menu.

Here is another XML string that lists the stock values by a specified keyword. We used the “software” keyword in our example. Since the stock market doesn’t consist of a single company, we again get a long XML string with the same tags used more than once, signifying the same kind of data with different values.


    
        CHKP
        Check Point Software Technologies Ltd
        NASDAQ
    
    
        PRGS
        Progress Software Corp
        NASDAQ
    
    
        TTWO
        Take-Two Interactive Software Inc
        NASDAQ
    
    
        AMSWA
        American Software Inc
        NASDAQ
    
    
        GWRE
        Guidewire Software Inc
        NYSE
    
    
        DATA
        Tableau Software Inc
        NYSE
    
    
        PAYC
        Paycom Software Inc
        NYSE
    
    
        CYBR
        Cyberark Software Ltd
        NASDAQ
    
    
        COUP
        Coupa Software Inc
        NASDAQ
    
    
        PVTL
        Pivotal Software Inc
        NYSE
    

To get the symbol names we need to use a path like below.

 

//LookupResultList/LookupResult/Symbol

If you try creating this path the same way as before, you will only get the first symbol of the stocks. The reason behind this is that the function can get all symbols as an array, but can only show the first one, if it is not set as an array formula. To convert a regular formula into an array equivalent can be done like this:

  1. Begin by selecting a range that contains the same number of cells as the return values. For example, E3:E12
  2. Type in your formula (i.e. =FILTERXML(E1,E2) ). Cell E2 contains the same XPath string: //LookupResultList/LookupResult/Symbol
  3. Press the Ctrl + Shift + Enter key combination instead of Enter. You do not need this step if you have the current version of Excel 365. Enter will be enough to see all values. For more information, please visit our article about Excel's new concept: Dynamic Arrays

All that's left to do is getting all the symbols together.

Download Workbook


Tips

  • Use the FILTERXML to parse the XML returns from the WEBSERVICE function.
  • The FILTERXML function may appear in the Excel for Mac function gallery, but it doesn't work on Mac, because of the function's dependencies on Windows operating system features.