Easy Way to Combine Data in Google Sheets

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.

How to combine data from multiple sheets in Google Sheets

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 using FILTER in Google Sheets

FILTER is a Google Sheets function to filter 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);...}                              

• {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) }

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

Combine sheets into one using QUERY 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")              

• {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 in Google Sheets 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 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 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.

Merge Google Sheets into one automatically

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 Coupler.io and its Google Sheets integration.

Coupler.io is a data integration solution to import data from different sources to Google Sheets, Excel, or BigQuery. It's available as a web app and a Google Sheet add-on, the latter needs to be installed from the Google Workspace Marketplace.

The Google Sheets integration allows you to import a data range from multiple sheets and merge Google Sheets together. To set up the Google Sheets integration, sign up to Coupler.io, click Add importer, and select Google Sheets both as the source and destination apps.

Then configure the connections.

Source

• Connect your Google account and select a Google Sheets file on your Google Drive to transfer data from. Then select the 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.

Optionally, you can choose a specific range in your spreadsheets to merge. To do this, click Continue and specify the cell range in the optional field.

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.

Destination

• Connect your Google account and select a Google Sheets file on your Google Drive and a sheet that will receive the combined sheets.

Optionally, you can specify the first cell where you want to import your data range and change the import mode for your data: you can replace your previous information or append new rows under the last imported entries

You can run the import right away if you click Save and Run. To automate data import on a schedule, toggle on the Automatic data refresh and customize the frequency as follows:

• 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:

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.

How to merge sheets from another Google Sheets spreadsheet/workbook

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.

Merge Google Sheets into one from another spreadsheet without formulas

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:

• Sign up to Coupler.io, click Add importer and select Google Sheets as the source and destination apps.
• Connect your Google account, then select a file on your Google Drive and sheets that you want to merge. In our example, we're merging sheets named Invoices 2019 and Invoices 2020.

• Connect your Google account, then select a file on your Google Drive and a sheet to transfer the merged data.
• Click Save and Run

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

How to merge cells in Google 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 Google 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 Coupler.io – which way to choose?

Google Sheets integration by Coupler.io

If you don't want to waste time on writing formulas and checking their syntax, go with the Google Sheets integration. It's easy to use and allows you to schedule data import & merge. The integration is especially functional if you need to merge multiple sheets from another Google Sheets spreadsheet. 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. For example, here is how you can apply it to merge Excel 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

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.

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

Focus on your business

goals while we take care of your data!

Try Coupler.io

sheraffen1961.blogspot.com

Source: https://blog.coupler.io/combine-sheets-into-one/

Share this post