Dynamically Append Tables Across Sheets using FILTERXLM

Welcome to Excel Jet Consult blog post. We previously learn how to split values into multiple columns using FILTERXLM Function. In this tutorial, we will consider how we can write a robust and easily understandable formula that will dynamically append tables across sheet sheets using the FILTERXML Function. Let’s get started


Sample Data

In the caption below, we have Sales Report for 2017 to 2018

There are six columns in each of the sheet tabs. In Summary sheet, we want to combine the report from Sales for 2017 and  Sales for 2018 respectively. Performing this operation using Power Query is perhaps the only method known by users who have knowledge of Power Query. But, I am going to show us how to use formula to append the tables without the use of Power Query!

Format Data As Excel Tables

Since we want to make the append to be dynamic in the sense that when new data is added in any of the source data, it will automatically update, hence, we are going to format Sales for 2017 and Sales for 2018 as Excel Tables.

  • Click inside the dataset in each of Sales for 2017 sheet tab and execute CTRL + T
  • In the Create Table dialogue box as seen below

  • Click on OK
  • Repeat the above steps for the Sales 2018

Descriptive Table Names

By default, Excel uses Table1 and Table2 for each of the Table created. We want to provide descriptive names for our Tables.


  • In the Properties group of the Table Design tab, click inside Table1 and for this example, we provided Sales2017 as the official Table name

Repeat the steps above and name the Table as Sales2018. Note, its important to hit Enter after typing the Table name


The Excel FILTERXML returns specified data from the XML content by using the specified XPATH

FILTERXML Syntax:      =FILTERXML(xml,xpath)

xml – exTensible Markup Language

xpath – XML Path Language


  • In cell A2 of the Summary sheet, execute: =INDEX(FILTERXML(“<jerry><tom>”&TEXTJOIN(“</tom><tom>”,TRUE,Sales2017,Sales2018)&”</tom></jerry>”,”//tom”),SEQUENCE(COUNTA(Sales2017[Date],Sales2018[Date]),6))
  • Click Enter.

Note, you need to have Dynamic Array SEQUENCE Function for this formula to work. In another video, I will show you how to write the formula without need for DA SEQUENCE Function

In the caption below, we can see that the data from the two sheets have been appended and we have 288 rows


We have seen that it is possible to append data using FILTERXML Function

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *