Combine data in multiple worksheets

The goal is to combine data from different worksheets with a formula. Note that we are not restructuring the data in any way, we are simply combining data in different worksheets that already have the same structure. At a high level, the formula we are using combines data from multiple sheets with the VSTACK function and removes empty rows with FILTER. The data in the workbook comes from 3 separate worksheets (Sheet1, Sheet2, and Sheet3) and is combined in another worksheet named Summary. The Diagram below provides an overview:

Notice that the range in the formula below is fixed as B5:B16 which includes empty rows on each worksheet. This means the solution needs to take into account removing these empty rows.

Normal ranges with 3D reference

To combine data in "normal ranges" (not in named ranges, not in Excel Tables), one nice approach is to use the VSTACK function with a 3D reference to the range that holds data on each sheet. Then we use the FILTER function to remove empty rows that result from using a fixed range with the 3D reference. This is the approach used in the worksheet example above, where the formula in cell B5 is:

=LET(data,VSTACK(Sheet1:Sheet3!B5:E16),FILTER(data,CHOOSECOLS(data,1)<>""))

Notice we are using the LET function to streamline the formula a bit. Working from the inside out, we first use the VSTACK function to collect the data from the three worksheets with VSTACK and assign the result to a variable named "data":

=LET(data,VSTACK(Sheet1:Sheet3!B5:E16)

The reference "Sheet1:Sheet3!B5:E16" is known as a "3D reference". It points to the range B5:E16 on Sheet1 through Sheet3 which, in this case, is Sheet1, Sheet2, and Sheet3. VSTACK retrieves data from the range B5:E16 on each sheet and stacks together each data set vertically. This is the core of the formula, the part that does the actual data consolidation. Next, the FILTER function is used to remove empty rows like this:

FILTER(data,CHOOSECOLS(data,1)<>"") // remove empty rows

Here, the array in FILTER is provided as data, the variable data defined by LET in the previous step. The logic to remove empty rows, provided as the include argument, is based on the CHOOSECOLS function:

CHOOSECOLS(data,1)<>"" // test column 1

CHOOSECOLS retrieves the first column from data and checks each cell to confirm that it is not empty. The result is an array of TRUE and FALSE values, one per row. The TRUE values correspond to rows in data that have a value in the first column. These rows make it into the result returned by FILTER while the rows associated with FALSE values are discarded.

With Excel Tables

If you are combining data that already exists in Excel Tables, this problem is much easier.  For example, with the data in this example in three tables (Table1, Table2, and Table3) we can combine the data with a simple VSTACK formula like this:

=VSTACK(Table1,Table2,Table3)

There is no need to remove empty rows because Excel Tables automatically expand and contract to fit the data they contain. This means we don't need to use any fancy tricks to figure out how many rows to retrieve, or how many empty rows to remove.