Back to Blog

Tutorial on How to Combine Data from Multiple Sheets Into One Master Overview

Let’s say you have a Google Sheets doc with multiple sheets that have the same structure (number and names of columns). You need to combine data from these sheets into one master overview. Are you going to copy and paste values manually? No way! In Google Sheets, there are versatile options to automate the flow and forget about handwork. In this tutorial, we’ll introduce several functions, as well as a formula free solution, to merge sheets into one. 

Combine data from two and more sheets within a single Google Sheets document

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.

Combine sheets into one with FILTER

FILTER is a Google Sheets function to filter out subsets of data from a specified data range by a provided condition. For more on how it works, read this FILTER Function How-to Guide

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);...}
  • {sheet#1-range} – the data range from the first sheet including the title row.
  • {sheet#2-range} – the data range from the second sheet without the title row. 
  • {sheet#1-range-first-column} – the first column of the data range from the first sheet.
  • {sheet#2-range-first-column} – the first column (without the title row) of the data range from the second sheet.

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 is to 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 like 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)
}

Combine sheets into one with QUERY

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. Read the QUERY How-To Guide to learn more.

To combine sheets using QUERY, apply the following formula:

=QUERY({{sheet#1-range};{sheet#2-range};...,"Select * where Col1 is not null")
  • {sheet#1-range} – the data range from the first sheet including the title row.
  • {sheet#2-range} – the data range from the second sheet without the title row. 

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 with QUERY

We’ve successfully combined sheets with invoices data. However, it would be great if we could not only merge, but also consolidate specific data from those sheets. For example, the invoice amount of the Abatz company in 2020 is $1778, and 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 in 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.

Merge sheets into one on a custom schedule

If you need to combine multiple sheets into one automatically on a custom schedule, or you just don’t want to deal with formulas, use the Google Sheets importer. It allows you to import a data range from multiple sheets and merge them together. 

Google Sheets importer is a part of Coupler.io, a product to import data from different data sources to Google Sheets, Excel, or BigQuery.

Coupler.io is available as a web app and a Google Sheets add-on, the latter needs to be installed from the Google Workspace Marketplace

To set up a Google Sheets importer, sign up to Coupler.io, click “Add new importer” and set up source, destination, and schedule.

Source 

  • Select Google Sheets as a source app from the list.
  • Connect your Google account or choose one from the drop-down list if you’ve already connected some.
  • Select a Google Sheets file on your Google Drive to transfer data from. 
  • Select sheets to merge.

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 Invoices 2020, then Invoices 2019), make sure to arrange the sheets in your spreadsheet in this order.

Merge sheets with a patterned name

You can merge multiple sheets that have a patterned name! Let’s say you have sheets with invoices for the last 10 years. Instead of typing in all them one by one (Invoices 2010, Invoices 2011, Invoices 2012, etc.), use the following pattern: 
{sheet-name}.+
In our case, it will look as follows:
Invoices 20.+
This way the sheet names will be matched by pattern and if new sheets are added (Invoices 2021, Invoices 2022…), the data from them will be merged automatically without changing the importer parameters. 
The feature works similarly with any sheet names you have.
  • Click “Jump to Destination Settings“. Optionally, you can choose a specific range in your spreadsheets to merge. To do this, click “Continue” and specify the range in the optional field.

Destination

  • Pick Google Sheets as the destination application you want to save data to.
  • Connect to your Google account. 
  • Choose a Google Sheets file, which will receive the combined sheets.
  • Specify the first cell where you want to import your data range. The A1 cell is set by default. 
  • Choose the import mode for your data: you can replace your previous information or append new rows under the last imported entries.
  • Toggle on the Last updated column feature, if you want to add a column to the spreadsheet with the information about the last date and time refresh.

You can run the import right away if you click “Save and Run“. If you want to automate data import on a schedule, toggle on the Automatic data refresh and customize the schedule.

Schedule 

  • Select Interval (from 15 minutes to every month)
  • Select Days of the week
  • Select Time preferences
  • Schedule Time zone

Once you’re ready, click Save & Run to save the parameters and initiate the first data import and combine the specified sheets. Here’s what we’ve got:

The importer adds the Sheet Name column, so you can easily navigate the merged data. 

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

How to merge sheets from another Google Sheets doc

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

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.

Merge sheets into one from another spreadsheet without formulas

Coupler.io and its Google Sheets importer (again) is the easiest way to import and merge data from a Google Sheets document. All you need to do is:

  • Select Google Sheets as a source app from the list.
  • Connect your Google account or choose one from the drop-down list if you’ve already connected some.
  • Select a file on your Google Drive to transfer data from 
  • Select sheets to merge. (In our case, Invoices 2019 and Invoices 2020)
  • Pick Google Sheets as the destination application you want to save data to.
  • Connect to your Google account. 
  • Choose a file and a sheet to transfer the merged data to.

Now, the Google Sheets importer will import and merge sheets smoothly.

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.

Merge sheets from another spreadsheet into one with 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);...}
  • {spreadsheet-ID} – the ID or URL of the Google Sheets document, you’re importing data from
  • {sheet#1-name} – the name of the first sheet
  • {sheet#1-name} – the name of the second sheet 
  • {sheet#2-range} – the data range from the first sheet including the title row.
  • {sheet#2-range} – the data range from the second sheet without the title row. 
  • {sheet#1-first-column} – the first column of the data range from the first sheet.
  • {sheet#2-first-column} – the first column (without the title row) of the data range from the second sheet.

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 sheets from another spreadsheet into one 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")
  • {spreadsheet-ID} – the ID or URL of the Google Sheets document, you’re importing data from
  • {sheet#1-name} – the name of the first sheet
  • {sheet#2-name} – the name of the second sheet 
  • {sheet#1-range} – the data range from the first sheet including the title row.
  • {sheet#2-range} – the data range from the second sheet without the title row. 

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")

Read more about QUERY + IMPORTRANGE combination with examples.

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 sheets. If you’ve got another error, check out our blog post Why IMPORTRANGE Is Not Working: Errors and Fixes

How to merge sheets from different Google Sheets docs

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. 

QUERY, FILTER or Google Sheets importer – which way to choose?

Google Sheets importer by Coupler.io

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

Besides, 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.

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

The combination of QUERY and IMPORTRANGE is the best choice for merging sheets from another spreadsheet, as well as different spreadsheets. Its syntax is easy to comprehend and is not as bulky as with FILTER. 

Sure, it’s up to you to decide which way to go, depending on what will be best for your project. Good luck with your data!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free