Coupler.io Blog

How to Combine Data from Multiple Google Sheets Into One 2025 Tutorial

  • Zakhar Yung

    • Do you have a Google spreadsheet with structurally identical sheets and need to combine data from them? If so, there are versatile options to automate the flow and forget about manual work. Let’s explore several functions and a formula-free solution to merge sheets into one. 

    You can also check our video tutorial on the Coupler.io Academy YouTube channel.

    Merge Google Sheets fast and without any formulas

    To combine sheets automatically on schedule or just avoid formulas in Google Sheets to link to another sheet, use Coupler.io. It’s a reporting automation platform that allows you to import data between sheets in minutes. You can pull a data range from multiple sheets or merge them all together. Here’s how you can do this step by step:

    The order of the sheets specified doesn’t influence the order of the merged data. If you need to merge data in a specific order (for example, first Products 2020, then Products 2021, and Products 2022), make sure to arrange the sheets in your spreadsheet in this order.

    Merge sheets with a patterned name

    You can merge multiple sheets by pattern! Let’s say you have sheets with product items for the last 10 years. Instead of typing in all of them one by one (Products 2010, Products 2011, Products 2012, etc.), use the following pattern: 

    {sheet-name}.+
    In our case, it will look as follows:
    Products 20.+

    This way, the sheet names will be matched by pattern, and if new sheets are added (Products 2021, Products 2022, etc.), their data will be merged automatically without changing the importer parameters. 

    The feature works similarly to any sheet name you have.

    Then, you can automate reporting on schedule by turning on the Automatic data refresh. Specify an update interval from every month to daily and even every 15 minutes (that will make your report near real-time).

    Finally, save and run the importer. Here’s what you’ll get:

    Coupler.io adds the Sheet Name column so you can easily navigate the merged Google Sheets.

    Google Sheets to Google Sheets is not the only integration provided by Coupler.io. It also allows you to import data from CSV files, Excel files, and numerous apps.

    Try Coupler.io for free

    If you prefer the native options to combine sheets in Google Sheets, then let’s check them out below.

    How to combine data from multiple sheets in Google Sheets with formulas

    Let’s begin with a simple task:

    There is a Google Sheets doc with two sheets: Invoices 2019 and Invoices 2020. Each of these sheets has eight columns (A:H) of the same name. The first row contains the column titles. Our task is to merge data vertically from these sheets into one.

    Use FILTER in Google Sheets 

    FILTER is a Google Sheets function that filters out subsets of data from a specified data range by a provided condition.

    To combine sheets using FILTER, apply the following formula:

    ={FILTER({sheet#1-range},LEN({sheet#1-range-first-column})>0);
FILTER({sheet#2-range},LEN({sheet#2-range-first-column})>0);...}

    The LEN condition (LEN({sheet#1-range-first-column})>0) in the FILTER formula is required to skip empty rows in the range. Otherwise, the formula will also add empty rows when merging the rows with data.

    In our case, the formula will look as follows:

    ={
  FILTER('Invoices 2019'!A1:H, LEN('Invoices 2019'!A1:A) > 0);
  FILTER('Invoices 2020'!A2:H, LEN('Invoices 2020'!A2:A) > 0)
}

    In this way, you can merge more than two sheets together. All you need to do is add relevant sheets and their ranges in the formula.

    Note: Make sure to specify the data range from the second sheet (and subsequent ones) without the title row, such as A2:H instead of A1:H. Otherwise, the title row will also be imported. For example, 

    ={
  FILTER('Invoices 2019'!A1:H, LEN('Invoices 2019'!A1:A) > 0);
  FILTER('Invoices 2020'!A1:H, LEN('Invoices 2020'!A1:A) > 0)
}

    For more on how it works, read this FILTER Function How-to Guide. 

    Use QUERY in Google Sheets

    QUERY is a Google Sheets function to fetch the data based on specified criteria. Additionally, you can amend formatting, change the order of columns, and perform other manipulations with the imported data.

    To combine sheets using QUERY, apply the following formula:

    =QUERY({{sheet#1-range};{sheet#2-range};...,"Select * where Col1 is not null")

    In our case, the formula will look as follows:

    =query({'Invoices 2019'!A1:H;'Invoices 2020'!A2:H},"Select * where Col1 is not null")

    You can merge more than two sheets together with QUERY if you add relevant sheets and their ranges in the formula. Don’t forget that the ranges from the second and subsequent sheets should be specified without the title row, just like with the FILTER function above. 

    Mind that the mentioned QUERY and FILTER formulas merge sheets with the same number of columns only. For other cases, read our guide on How To Combine Sheets With a Different Number of Columns Into One.

    Combine sheets into one and consolidate data using QUERY

    We’ve successfully combined sheets with invoice data. However, it would be great if we could not only merge but also consolidate data in Google Sheets. For example, the invoice amount of the Abatz company in 2020 was $1778, and it was $2864 in 2019. The Abatz’s total invoice amount is $4642. 

    Our goal is to consolidate the invoice amount for all companies that have records on both sheets. For this, we’ve modified the QUERY formula above to get the following:

    =query({'Invoices 2019'!A1:H;'Invoices 2020'!A2:H},"Select Col2,sum(Col8) where Col1 is not null group by Col2")

    Since company_name is the only iterative parameter for which we are consolidating data, we do not need to query other columns from the sheets. Read the QUERY How-To Guide to learn more.

    How to merge sheets from another Google Sheets spreadsheet/workbook without formulas

    We already know how to combine sheets within a Google Sheets workbook. Now let’s explore how you can import two or more sheets from another spreadsheet and merge them into one.

    I have a spreadsheet on my personal Google account – let’s name it External spreadsheet. I need to import and merge two sheets (Invoices 2019 and Invoices 2020) from the spreadsheet that has been featured in the examples above. This spreadsheet is on my working Google account. We’ll check out all possible options, and the no-formula one will be first.

    Coupler.io and its Google Sheets integration is the most actionable way to import and merge data from a Google Sheets workbook. We’ve described the setup flow above. In brief, it looks as follows:

    .
    Try Coupler.io for free

    Use formulas to merge sheets from different Google Sheets docs 

    You can do the same using QUERY or FILTER formulas nested with IMPORTRANGE. It is a function that allows you to import a data range from one Google Sheets document to another. Read the IMPORTRANGE tutorial to learn more about it.

    Another use case is when you need to merge a sheet from one Google Sheets doc and a sheet from another Google Sheets doc. You can easily handle this using either FILTER+IMPORTRANGE or QUERY+IMPORTRANGE. The difference is that you’ll have to specify different spreadsheet IDs in the respective parameters of the IMPORTRANGE formula.

    Use FILTER + IMPORTRANGE

    The FILTER+IMPORTRANGE formula syntax to combine two or more sheets from another spreadsheet is the following:

    ={FILTER(IMPORTRANGE("{spreadsheet-ID}", "{sheet#1-name}!{sheet#1-range}"),LEN(IMPORTRANGE("{spreadsheet-ID}", "{sheet#1-name}!{sheet#1-first-column})>0);
FILTER(IMPORTRANGE("{spreadsheet-ID}", "{sheet#2-name}!{sheet#2-range}"),LEN(IMPORTRANGE("{spreadsheet-ID}", "{sheet#2-name}!{sheet#2-first-column})>0);...}

    This is how the formula looks for our use case:

    ={
  FILTER(importrange("1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck", "Invoices 2019!A1:H"), LEN(importrange("1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck", "Invoices 2019!A1:A")) > 0);
  FILTER(importrange("1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck", "Invoices 2020!A2:H"), LEN(importrange("1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck", "Invoices 2020!A2:A")) > 0)
}

    Merge cells in Google Sheets with QUERY + IMPORTRANGE

    The QUERY+IMPORTRANGE formula syntax to combine two or more sheets from another spreadsheet is shorter:

    =QUERY({IMPORTRANGE("{spreadsheet-ID}", "{sheet#1-name}!{sheet#1-range}");IMPORTRANGE("{spreadsheet-ID}", "{sheet#2-name}!{sheet#2-range}");...,"Select * where Col1 is not null")

    In our case, the formula will look as follows:

    =query({importrange("1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck", "Invoices 2019!A1:H");importrange("1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck", "Invoices 2020!A2:H")},"Select * where Col1 is not null")

    IMPORTRANGE #REF! You need to connect these sheets

    It’s OK if you’ve got this warning during the initial run of either FILTER+IMPORTRANGE or QUERY+IMPORTRANGE formula. Click Allow access to connect the source and target spreadsheets. After that, the formula will import and merge Google Sheets. If you’ve got another error, check out our blog post Why IMPORTRANGE Is Not Working: Errors and Fixes. 

    Which option is best to combine Google Sheets?

    FILTER

    The FILTER function is good when you need to merge sheets within a single spreadsheet. It’s simple and doesn’t require any advanced knowledge. At the same time, the syntax of FILTER nested with IMPORTRANGE is quite voluminous, so you’d better avoid using FILTER for merging sheets from external spreadsheets.

    QUERY+IMPORTRANGE

    The combination of QUERY and IMPORTRANGE is the best choice to consolidate data in Google Sheets. Its syntax is easy to comprehend and is not as bulky as with FILTER. 

    Google Sheets integration by Coupler.io

    If you don’t want to waste time writing formulas and checking their syntax, go with the Google Sheets integration by Coupler.io. It’s easy to use and allows you to schedule data import & merge. The integration is especially functional if you need to combine multiple sheets from other Google Sheets spreadsheets. In this case, it’s an advanced alternative to the IMPORTRANGE function.

    Besides, with Coupler.io, you can import data from 70+ cloud apps into a single spreadsheet for further processing. For example, here is how you can apply it to merge Excel sheets.

    Import and combine data in Google Sheets with Coupler.io

    Get started for free
    Exit mobile version