Dear friends,
Today, I am going to reveal a valuable secret: how to combine data from multiple Google Sheets into one spreadsheet. Imagine having a Google Sheets document with multiple sheets that have the same structure (number and column names). You need to combine the data from these sheets into a single overview. But why do it manually by copying and pasting values? There are versatile options in Google Sheets to automate this process and save you from this tedious task. In this tutorial, I will present several functions, as well as a formula-free solution, to merge sheets.
The fastest method to merge Google Sheets and automatically transform the data without using formulas
If you want to automatically combine multiple sheets according to a custom schedule, or if you simply don’t want to bother with formulas in Google Sheets to link one sheet to another, use Coupler.io and its integration with Google Sheets.
Coupler.io is a data automation and analytics platform that allows you to automatically import data into Google Sheets (and other destinations) from different applications (Shopify, Airtable, Xero, etc.) and databases.
The integration with Google Sheets allows you to import a range of data from multiple sheets and merge Google Sheets. Before loading your source data into the destination sheet, you can preview and perform versatile transformations, such as column management, data filtering, and sorting. All of this can be easily done in the Coupler.io interface, without any formula. To set up the integration with Google Sheets, sign up for Coupler.io, click on “Add Importer,” and select Google Sheets as the source and destination.
Next, configure the connections.
- Connect your Google account and select a Google Sheets file on your Google Drive from which you want to transfer the data. Then, select the sheets to merge.
The order of the specified sheets does not influence the order of the merged data. If you need to merge the data in a specific order (e.g., first 2020 Products, then 2021 Products and 2022 Products), make sure you have the sheets in that order in your spreadsheet.
Merge sheets with a predefined name
You can merge multiple sheets according to a pattern! Let’s say you have sheets with invoices from the last ten years. Instead of typing them one by one (Invoices 2010, Invoices 2011, Invoices 2012, etc.), use the following pattern:
{sheet-name}.+
In our case, it would look like this: Invoices 20.+
This way, the sheet names will be matched to this pattern, and if new sheets are added (Invoices 2021, Invoices 2022…), the data will be merged automatically without changing the importer settings.
This feature works the same way with any sheet names you have.
After that, you can preview the data from the sheets to merge and apply any necessary transformations, such as hiding or adding columns, filtering data, sorting, etc.
Optionally, you can choose a specific range within your sheets to merge. To do so, specify the range of cells in the optional “Range” field.
- Connect your Google account and select a Google Sheets file on your Google Drive, as well as a sheet that will receive the merged sheets.
Optionally, you can specify the first cell where you want to import your range of data and change the import mode for your data: you can “replace” your previous information or “add” new rows below the last imported entries.
You can run the import immediately by clicking “Save and Run.” To automate data importation on a schedule, enable “Automatic Data Refresh” and customize the frequency as follows:
- Select the interval (from 15 minutes to monthly).
- Select the days of the week.
- Select hourly preferences.
- Schedule the frequency according to the time zone.
When you’re ready, click “Save and Run” to save the settings and run the first import of data and merge of the specified sheets. Here’s what we got:
Coupler.io adds the “Sheet Name” column, which allows you to easily navigate through the merged Google Sheets.
Google Sheets to Google Sheets is not the only integration offered by Coupler.io. It also allows you to import data from CSV files, Excel, and many other applications.
If you prefer native options to merge sheets in Google Sheets, then let’s take a look at that.
How to merge data from multiple sheets in Google Sheets
Let’s start with a simple task:
There is a Google Sheets document with two sheets: Invoices 2019 and Invoices 2020. Each of these sheets has eight columns (A:H) with the same names. The first row contains the column headers. Our mission is to merge the data vertically from these sheets into one.
Merge sheets using the FILTER function in Google Sheets
FILTER is a Google Sheets function that allows you to filter subsets of data from a specified range based on a given condition.
To merge the sheets using FILTER, apply the following formula:
= {FILTER({sheet-range#1},LEN({first-column-sheet#1})>0); FILTER({sheet-range#2},LEN({first-column-sheet#2})>0);...}
{sheet-range#1}: the data range of the first sheet, including the header row.{sheet-range#2}: the data range of the second sheet without the header row.{first-column-sheet#1}: the first column of the data range of the first sheet.{first-column-sheet#2}: the first column (without the header row) of the data range of the second sheet.
The condition LEN({first-column-sheet#1})>0 in the FILTER formula is necessary to exclude empty rows from the range. Otherwise, the formula will also add empty rows when merging with rows with data.
In our case, the formula would look like this:
= { FILTER('Invoices 2019'!A1:H, LEN('Invoices 2019'!A1:A) > 0); FILTER('Invoices 2020'!A2:H, LEN('Invoices 2020'!A2:A) > 0) }
This way, you can merge more than two sheets together. You just need to add the relevant sheets and their ranges to the formula.
Note: Make sure to specify the data range of the second (and subsequent) sheet without the header row, e.g., A2:H instead of A1:H. Otherwise, the header 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) }
To learn more about how FILTER works, check out this guide on the FILTER function.
Merge sheets using the QUERY function in Google Sheets
QUERY is a Google Sheets function that allows you to extract data based on specified criteria. Additionally, you can modify formatting, change the order of columns, and perform other manipulations with the imported data.
To merge the sheets using QUERY, apply the following formula:
=QUERY({{sheet-range#1};{sheet-range#2};...},"Select * where Col1 is not null")
{sheet-range#1}: the data range of the first sheet, including the header row.{sheet-range#2}: the data range of the second sheet without the header row.
In our case, the formula would be:
=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 by adding the relevant sheets and their ranges to the formula. Just make sure that the ranges of the second and subsequent sheets are specified without the header row, just like with the FILTER function mentioned above.
Note: The QUERY and FILTER formulas mentioned merge sheets with the same number of columns only. For other cases, check out our guide on how to merge sheets with a different number of columns into one.
Merge sheets by consolidating data with QUERY
We have succeeded in merging the sheets with invoice data. However, it would be nice if we could not only merge but also consolidate the data in Google Sheets. For example, the amount of Abatz company’s invoice in 2020 is $1778, and it was $2864 in 2019. The total amount of Abatz’s invoice is $4642.
Our goal is to consolidate the invoice amount for all companies that have records on both sheets. To do this, we modified the above QUERY formula to look like this:
=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 consolidate the data, we don’t need to fetch other columns from the sheets.
Read the practical guide on the QUERY function to learn more.
How to merge sheets from another Google Sheets workbook/spreadsheet
We already know how to merge sheets within a Google Sheets workbook. Now, let’s see how you can import and merge two or more sheets from another workbook and combine them into one. We will explore all possible options, and the first one will be the formula-free method.
Merge sheets into another workbook without formulas
Coupler.io and its integration with Google Sheets is the most convenient solution for importing and merging data from a Google Sheets workbook. We have described the setup steps above. In short, it goes like this:
- Sign up for Coupler.io, click on “Add Importer,” and select Google Sheets as the source and destination.
- Connect your Google account, then select a file on your Google Drive and the sheets you want to merge. In our example, we are merging the sheets called Invoices 2019 and Invoices 2020.
- Connect your Google account, then select a file on your Google Drive and a sheet to receive the merged sheets.
- Click “Save and Run.”
You can do the same using nested QUERY or FILTER formulas with IMPORTRANGE. IMPORTRANGE is a function that allows you to import a range of data from one Google Sheets document to another. Check out the IMPORTRANGE tutorial to learn more about it.
Merge sheets from another workbook using FILTER + IMPORTRANGE
The syntax for the FILTER + IMPORTRANGE formula to merge two or more sheets from another workbook is as follows:
= {FILTER(IMPORTRANGE("{spreadsheet-ID}", "{sheet-name#1}!{sheet-range#1}"),LEN(IMPORTRANGE("{spreadsheet-ID}", "{sheet-name#1}!{first-column-sheet#1})>0); FILTER(IMPORTRANGE("{spreadsheet-ID}", "{sheet-name#2}!{sheet-range#2}"),LEN(IMPORTRANGE("{spreadsheet-ID}", "{sheet-name#2}!{first-column-sheet#2})>0);...}
{spreadsheet-ID}: the ID or URL of the Google Sheets document from which you are importing the data.{sheet-name#1}: the name of the first sheet.{sheet-name#2}: the name of the second sheet.{sheet-range#1}: the data range of the first sheet, including the header row.{sheet-range#2}: the data range of the second sheet without the header row.{first-column-sheet#1}: the first column of the data range of the first sheet.{first-column-sheet#2}: the first column (without the header row) of the data range of the second sheet.
Here’s what the formula would look like 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 sheets from another workbook using QUERY + IMPORTRANGE
The syntax for the QUERY + IMPORTRANGE formula to merge two or more sheets from another workbook is shorter:
=QUERY({IMPORTRANGE("{spreadsheet-ID}", "{sheet-name#1}!{sheet-range#1}");IMPORTRANGE("{spreadsheet-ID}", "{sheet-name#2}!{sheet-range#2}");...},"Select * where Col1 is not null")
{spreadsheet-ID}: the ID or URL of the Google Sheets document from which you are importing the data.{sheet-name#1}: the name of the first sheet.{sheet-name#2}: the name of the second sheet.{sheet-range#1}: the data range of the first sheet, including the header row.{sheet-range#2}: the data range of the second sheet without the header row.
In our case, the formula would be:
=query({importrange("1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck", "Invoices 2019!A1:H");importrange("1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck", "Invoices 2020!A2:H")},"Select * where Col1 is not null")
To learn more about combining QUERY + IMPORTRANGE with examples, refer to the documentation.
IMPORTRANGE #REF! You need to connect these sheets
If you get this warning when running the FILTER + IMPORTRANGE or QUERY + IMPORTRANGE formula for the first time, click “Allow Access” to connect the source and target sheets. After that, the formula will import and merge the Google Sheets. If you encounter any other errors, check out our blog post “Why doesn’t IMPORTRANGE work? Errors and Fixes” for solutions.
How to merge sheets from different Google Sheets documents
Another use case is when you need to merge a sheet from one Google Sheets document with another sheet from another Google Sheets document. You can easily handle this using FILTER + IMPORTRANGE or QUERY + IMPORTRANGE. The difference is that you will need to specify different spreadsheet IDs in the respective parameters of the IMPORTRANGE formula.
QUERY, FILTER, or Coupler.io – What are the options?
Coupler.io Google Sheets integration
If you don’t want to waste time writing formulas and checking their syntax, go for the Coupler.io Google Sheets integration. It is easy to use and allows you to schedule data importation and merging. The integration is especially functional if you need to merge multiple sheets from another Google Sheets document. In this case, it is an advanced alternative to the IMPORTRANGE function.
Additionally, with Coupler.io, you get a list of other importers such as Airtable, CSV, etc. This means you can import data from different sources into a single spreadsheet for further processing. For example, here’s how you can apply it to merge Excel sheets.
FILTER
The FILTER function is useful when you need to merge sheets within the same Google Sheets document. It is straightforward and does not require advanced knowledge. However, the syntax of FILTER nested with IMPORTRANGE is quite lengthy, so it is better to avoid using FILTER to merge sheets from external Google Sheets documents.
QUERY
The combination of QUERY and IMPORTRANGE is the best choice for consolidating data in Google Sheets. Its syntax is easy to understand and less voluminous than that of FILTER.
Of course, it is up to you to decide which method to use, depending on what is best for your project. Good luck with your data!
