HomeGoogle DocsSpreadsheetConsolidate Data from Multiple Sheets Using a Formula in Google Sheets

Consolidate Data from Multiple Sheets Using a Formula in Google Sheets

Published on

Many people think there is no Excel Data Consolidation alternative in Google Sheets. Of course, there is no such command. But you can consolidate data from multiple Sheets using a formula in Google Sheets.

Don’t write off Google Sheets! There is a powerful function in Google Sheets to do data consolidation in a much better way! Do you know which function/formula is that?

I’m talking about the Query function. In this function, you can specify the data range in multiple Sheets in two ways.

Manual Entry (Hardcoded):- Use it when you have fewer Sheets to combine.

Using a Custom Named Function:- Use it when you have several Sheets to combine. 

I’m using only the native functions, not Apps Script, or any third-party plugins, to consolidate data from multiple Sheets.

The data consolidation in this way is not limited to Sheets in a single file. You can use Sheets from even different files by importing them.

Understanding Consolidate Data from Multiple Sheets

I will combine data from multiple Sheets (multiple tabs in a workbook or file) into one single Sheet and then summarise it.

What about consolidating data in two workbooks in Google Sheets?

If that is the cause, you require to use the IMPORTRANGE function to import data first into a master Sheet.

I’ll skip that part in this tutorial to avoid unnecessary confusion.

Advantages of Using a Formula (Query Function)

Here are some of the advantages of consolidating data from multiple Sheets using a formula in Google Sheets

  • Data consolidation without any Script means better Spreadsheet performance.
  • Formulas are faster and more reliable if we write them correctly.
  • If we use any plugin (I don’t know about any such plugin at the time of writing this post), we can’t ensure the availability of that plugin always. Even if you find one, add it after reading the user reviews.
  • No need to pay any bucks to hire a developer to code an Apps Script for you.
  • When using a formula to consolidate data, you get more flexibility. The consolidated data will automatically get updated when we change the source data.

How to Consolidate Data from Multiple Sheets Using a Formula in Google Sheets

Please follow the step-by-step instructions below.

Below you can see four sheets (screenshots). The first two sheets, I mean sheet tabs, contain sample data which we will combine in the third sheet and summarise in the fourth sheet.

Sheet Tabs in Use in the Formulas

We can combine and summarise the data using one single formula. But to make the steps simpler, I am doing it separately.

Note:- You will get the detail to combine and summarise data using a single Query in the last part of this tutorial.

So there are two steps involved – data consolidation and combination. The third and fourth sheets we can use for this. I will split the tutorial below accordingly.

The first two sheets contain some sample data. See that below.

The name of my first sheet is junesheet, and here is the data in it.

Content in the First Sheet

Now see the data in the second sheet named julysheet.

Content in the Second Sheet

You can open my sample file below to see my data and formulas.

Data Consolidation Sample

How to Combine Data from Multiple Sheets Using Query Formula in Google Doc Sheets

Now let us see how to combine the above two sheets into a single sheet in the same Google Sheets file. I mean in the third sheet named Combined.

Below is the Query formula to combine the above two Sheets’ data into a single sheet.

=query({junesheet!A2:H5;julysheet!A2:H5},"Select * where Col1 is not null ")
Consolidate Data from Multiple Sheets: Combining Data

The formula is for our sample data above. You can use it as it is but with changes suitable for your spreadsheet data. The changes that you may require to make may be as follows.

  • Change the range A2:H5 to your actual data range.
  • Change the sheet names to your actual sheet names.
  • I’ve used two Sheets to combine. Suppose there is one more Sheet to include named augsheet. Just put a semi-column and enter the third sheet name with the range.

E.g:-

{junesheet!A2:H5;julysheet!A2:H5;augsheet!A2:H5}

Important:- Combine only similar data types (avoid mixed data types in a single column). Never combine a text column with a date column or numeric column.

In your workbook, if column A contains text strings in the first sheet and numbers in the second sheet, don’t combine them. They will make the combined data mixed data type.

It’s against data consolidation using Query. The Query will return incorrect output when mixed data types are in columns.

Infinite Range in Consolidation

If you want you can use infinite ranges like A2:H in the formula. Then the formula will be like this.

=query({junesheet!A2:H;julysheet!A2:H},"Select * where Col1 is not null ")

Do you have several Sheets to combine? Then you may try one of these custom-named functions.

  1. REF_SHEET_TABS: Reference a List of Tab Names in Query in Google Sheets.
  2. COPY_TO_MASTER_SHEET: Combine Data in Multiple Tabs in Google Sheets.

We want to summarize or consolidate the data we combined from multiple Sheets. Below are the steps. Before that, first, see the combined data below.

Combined Data Result

Summarising or Consolidating Data from Multiple Sheets Using Query Formula in Google Sheets

The below part uses the Query function. You can also do this by using the Pivot Table.

Just by using one more Query formula in the last Sheet named “Consolidate,” we can achieve the required result.

=query(Combined!A1:H10,"select D, sum(H) where A is not null group by D")

See this formula explained with the help of an image below.

Consolidate Data from Multiple Sheets: Query Formula Explanation

I have used the Query grouping to perform Sum aggregation in this data consolidation formula.

In our sample data, column D contains the item description. I have summarised the data based on this item field.

When the same item appears multiple times, this formula merges that into one row and sums its values in column H.

The consolidated data will look as below.

Consolidate Data from Multiple Sheets Using Formula - Result Sheet

That’s all. I hope you enjoyed my tutorial and learned how to consolidate data from multiple sheets using a formula in Google Sheets.

As I promised at the beginning, here is the single formula that combines and summarises the data.

=query({junesheet!A2:H;julysheet!A2:H},"select Col4, sum(Col8) where Col1 is not null group by Col4")

What to Do if the Number of Columns is Different in Each Tab?

Usually, it won’t happen. But by any chance, if the number of columns in the data range in each tab is different, the above method doesn’t work.

You can try the below formula to first combine the data and then consolidate it.

Google Sheets: Combine Two Tables with Different Numbers of Columns in a Query.

The Query function is quite handy if you utilize it properly. So take your own time and learn the above Query function tutorial.

First, I thought about sharing some more Query tutorials here.

But there are 100+ Query tutorials on Info Inspired. So, better please use the Search bar to find it.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

67 COMMENTS

  1. Thank you for this tutorial. Is there a way to reference a dynamic set of tab names without changing the query formula?

    For example, if I have the tab names in cells A1-A3, could I use ArrayFormula and Concatenate to put text in cell A4 that mirrors your reference range perfectly?

    But I can’t seem to get the query to recognize that text as a valid set of references using the “INDIRECT(A4)” formula. Thank you.

LEAVE A REPLY

Please enter your comment!
Please enter your name here