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. So, first you’ll need to install Coupler.io from the G Suite Marketplace

After that, open a spreadsheet and go to the Add-ons menu. Launch Coupler.io, click Open Dashboard, click +Add Importer, and select Google Sheets. 

Complete the following steps to set it up:

1. Fill out the “Title” field

Add the name of your importer.

2. Set up the Source

2.1. Connect to your Google Account
  • Click Connect.
  • Pick the Google Account you want to connect to.
  • Sign in to the chosen Google Account.
  • Coupler.io will need access to view your Google spreadsheets. Click Allow to grant access.
2.2. Fill out the “Spreadsheet URL” field

Insert the URL or ID of the Google Sheets doc you’re going to import data from. You can get it from the URL bar of your browser. 

Spreadsheet URL example:

https://docs.google.com/spreadsheets/d/1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck/edit#gid=1968747040

Spreadsheet ID example:

1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck
2.3. Fill out the “Sheet name(s)” field

Enter the names of the sheets to merge, each on a new line. If you leave this field blank, the importer will import data from:

  • the first sheet of the document, if no sheet has been specified in the “Spreadsheet URL” field.

Example:

Invoices 2019
Invoices 2020

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.

Cool hidden benefit!

You can merge multiple sheets by pattern! 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: 
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.
2.4. Fill out the “Range” field

Click Show advanced to expand the “Range” field and enter the range of cells to merge. If you leave this field blank, the importer will import the entire data set of the specified sheets.

Example:

A1:H

3. Set up the Destination

3.1. Fill out the “Sheet name” field

Enter the name of the sheet, which will receive the combined sheet.

Additionally, you can specify the address of the first cell where the data range will be imported. For this, click Show advanced and fill out the “Cell address” field.

4. Configure Settings

In this section you can:

For more on this, refer to the Coupler.io Knowledge Base

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:

  • Connect your Google account
  • Copy the ID or URL of your spreadsheet and paste it into the “Spreadsheet URL” field. In our case, the spreadsheet ID is 1nQBbnawdY_V44lk55A3UDsdGcEQB9YR6DIpODKc94Ck
  • Enter the names of the sheets to merge into the “Sheet(s) name” field. Be careful not to add extra spaces to your sheets names. In our case, these are:
Invoices 2019
Invoices 2020
  • Enter the range into the “Range” field. In our case, this is A1:H.
  • Name the sheet that will receive the merged sheets.
  • Click Save & Run to get the combined sheets.

Important note: The spreadsheet that you’re importing data from must be open for access to the Google account that is requesting data. Otherwise, you’ll get the following error.

To open access to the doc, click Share and add the email of the Google account that will be receiving data.

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