In this Google Sheets tutorial, you will receive a custom-named function as well as a formula to dynamically combine data vertically from multiple sheet tabs.

Google Sheets and similar apps support organizing data in sheet tabs within a workbook, aiding data organization. However, there is a challenge.

For instance, if you have monthly data organized in different tabs in Google Sheets, creating a summary may necessitate copying and pasting this data into a master sheet. Alternatively, you might need to specify each range individually within a VSTACK formula as follows:

=VSTACK(Sheet1!A2:C5, Sheet2!A2:C5, Sheet3!A2:C5)

What about specifying the range as a list and using that dynamically?

To circumvent the need for copy-pasting or individually specifying data ranges, we introduce the COPY_TO_MASTER_SHEET function.

Features of the Formula for Dynamically Combining Sheet Tabs Vertically

The formula (also the named function) combines data from multiple sheet tabs in Google Sheets and offers the following features:

Vertical and Sequential Appending: The formula appends data vertically and in sequence, creating a larger array. In other words, the second sheet’s data will be beneath the first sheet, the third sheet’s data beneath the second sheet, and so on.

Retention of Data Types: Neither the COPY_TO_MASTER_SHEET named function nor the formula uses the SPLIT function, preserving the data types in the result.

Recommendation for Closed Data Ranges: I suggest using closed data ranges in the formula. For example, use A1:C1000, not A1:C, as combining data vertically may result in several blank rows below each sheet’s data. If you prefer an open range, wrap my named function or formula with QUERY or FILTER to filter out blank rows.

Prerequisites

Assume you want to dynamically combine the data in Sheet1, Sheet2, and Sheet3 in a fourth sheet named Master. The formula requires all tab names as a list in the Master sheet tab.

You can manually enter the tab names in column A of the Master sheet or use a Google Apps Script, which you can obtain at webapps.stackexchange.com.

To add the script, follow these steps:

1. Go to the “Extensions” menu in your Google Sheets and select “Apps Script.”
2. Delete the default text. Copy-paste the Apps Script from the above page and save the project by selecting the floppy icon.

Note: Alternatively, you can obtain it by creating a copy of my sample sheet provided a few paragraphs below.

Use the following formula in cell A1 on your Master sheet tab to get all tab names.

=sheetnames()

When using my COPY_TO_MASTER_SHEET function or formula to combine the data in sheet tabs “Sheet1,” “Sheet2,” and “Sheet3” in the “Master” sheet, do not specify A1:A. Specify A2:A only.

This is because A1 will contain the name “Master,” which we don’t want to include in the combination.

Combine Data Dynamically in Multiple Tabs Vertically: Formula

We will vertically merge data from Sheet1, Sheet2, and Sheet3 in the range A2:C5 into the Master sheet.

Here is the expected output after vertically merging sheets 1 to 3 dynamically.

For the sample data, named function, and formula, click the button below to copy the sheet.

COPY_TO_MASTER_SHEET

In the example above (please refer to the screenshot above), I utilized the following formula in cell B2:

=REDUCE(
   TOCOL(, 1), 
   TOCOL(A2:A, 1), 
   LAMBDA(a, v, IFERROR(VSTACK(a, INDIRECT(v&"!"&"A2:C5"))))
  )

Where:

• A2:A is the list of tab names.
• "A2:C5" is the range in each sheet to merge vertically.

Now, let’s understand how the formula dynamically combines multiple sheet tabs vertically in Google Sheets. Here is the formula explanation.

Note: When you use open ranges in the formula, for example, “A2:C” instead of “A2:C5”, wrap the formula with QUERY, as follows:

=QUERY(..., "SELECT * WHERE Col1 IS NOT NULL", 0)

Formula Breakdown

The formula employs the REDUCE function to dynamically combine multiple sheet tabs vertically. Here is the syntax:

Syntax:

REDUCE(initial_value, array_or_range, lambda)

In the formula:

• initial_value: TOCOL(, 1) – The TOCOL function returns an empty cell if specified as TOCOL(, 0). By changing 0 to 1, it eliminates that empty cell, fulfilling the initial_value requirement for REDUCE. It essentially returns nothing.
• array_or_range: TOCOL(A2:A, 1) – It returns the list of sheet names, removing empty cells.

Here is the Lambda function part of the formula:

• a: name of the accumulator.
• v: each value (sheet name) from array_or_range.

The REDUCE function iterates over each sheet name in the array_or_range, using the function INDIRECT(v&"!A2:C5"). In each iteration, the v represents Sheet1, Sheet2, and Sheet3.

The result at each step is stored in the accumulator, which is then vertically stacked using VSTACK as VSTACK(a, …). This outlines the REDUCE logic behind dynamically combining multiple sheets vertically.

Combine Data Dynamically in Multiple Tabs Vertically: Named Function

My above formula is already custom-named function-ready.

You just need to copy-paste it into the formula definition part of the Data menu > Named functions > Add new functions. Replace cell range references with names and add those names within argument placeholders.

I’ve done it for you. You only need to import it into your sheet to use.

Syntax:

COPY_TO_MASTER_SHEET(list, array)

Arguments:

• list: The cell or cell range that contains the tab list to combine.
• array: The range of data to combine is specified within double quotation marks.

Example:

We can replace our earlier formula in Master!B2 with my COPY_TO_MASTER_SHEET named function as follows.

=COPY_TO_MASTER_SHEET(A2:A,"A2:C5")

When you want to combine multiple sheets dynamically with open ranges, use the below one that includes the QUERY:

=QUERY(COPY_TO_MASTER_SHEET(A2:A,"A2:C"), "SELECT * WHERE Col1 IS NOT NULL", 0)

With the help of this named function, anyone can quickly combine data in multiple sheet tabs into a master sheet tab in Google Sheets.

How to Copy the COPY_TO_MASTER_SHEET Named Function

Here are the quick steps.

1. To use a named function, import it into the sheet where you want it to work.
2. First, make a copy of my sample sheet above.
3. Go to File > Settings and under “Locale,” select the same country as the sheet in which you want to use my function.
4. Then open the sheet in which you want my COPY_TO_MASTER_SHEET function.
5. Go to the Data menu in that sheet. Select Named functions > Import function.
6. Select the copied sheet (you can search by sheet name) and select Insert. Follow the onscreen instructions that follow.

You will find the code to import.

Resources

This tutorial covered a formula as well as a named function for dynamically combining data vertically from multiple sheet tabs. Interested in horizontal stacking? You can find the relevant tutorial and some additional tips from the resources below.

1. Consolidate Data from Multiple Sheets Using a Formula in Google Sheets
2. Consolidate Only the Last Row in Multiple Sheets in Google Sheets
3. How to Combine Multiple Sheets in Importrange and Control Via Drop-Down
4. Dynamically Combine Multiple Sheets Horizontally in Google Sheets
5. SUMIF Across Multiple Sheets in Google Sheets
6. Vlookup Across Multiple Sheets in Google Sheets
7. How to Include Future Sheets in Formulas in Google Sheets