Consolidate Data from Multiple Sheets Using Formula in Google Sheets

0
121
girl using pc - google sheet data consolidation

Many people think there is no Excel Data Consolidation alternative in Google Sheets. Of course there is no such command in Google Sheets. But don’t write off Google Sheets! There is a powerful function in Google Sheets to do the data consolidation in a much better way! Do you know which formula is that? I’m talking about the Query function in Google Sheets. In Google Sheets you can consolidate data from multiple sheets using formula. We can use the Query function in Google Sheets to combine multiple sheets and consolidate data. What more! The data consolidation by this way is not limited to sheets in a single file. You can use sheets from even different files in this method.

In this tutorial no Google Sheets script or plugin used to consolidate data from multiple Sheets.

If you are new to data consolidation, see below what we meant by stating consolidate data from multiple sheets using formula.

What We are Going to Do With Consolidate Data from Multiple Sheets Using Formula?

We are Combining multiple sheets into one single sheet and summarise it. Multiple sheets means multiple tabs in a single file. As already mentioned above no script or plugin is required to do it. If you ask me which function we can use to consolidate data, I will suggest the Query function. We can also use filter function at some extend.

Consolidate data from multiple sheets from a single file or from different files?

You can consolidate multiple sheets from different spreadsheet files also. To do that you should use the Google Sheet IMPORTRANGE function together with Query. That part I’m skipping now. I will try to write another Google Sheets tutorial for that. Other wise you will get confused.

girl using pc - google sheet data consolidation

The Advantage of Using Query Function to Consolidate Data in Google Sheets

  • First of all we are doing to data consolidation without any Script
  • Also no plugins required for data consolidation.
  • When using formula to consolidate data, it’s more flexible.
  • The consolidated data will automatically get updated when we change the source data.
  • Finally it’a fast and also you can one time set up and forget it.

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

Below you can see four sheets. The first two sheets contain sample data which we will first combine in the third sheet and finally summarise in the sheet four. So there are two steps involved – data consolidation and combine. The third and fourth sheets we can use for this. I will split the tutorial below accordingly.

sheet tabs used to consolidate data in google sheets using g-query

The first two sheet contain some sample data. See it below. The first sheet name is “junesheet”.

sample data sheet 1 for combine and data consolidation

Now see the data in second sheet named “julysheet”.

sample data sheet 2 for combine and data consolidation

How to Combine Data from Multiple Sheets Using Query Formula

Now let us see how to combine the above two sheets to a single sheet in the same Google Sheets file, that 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 “)

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

1. You should change the data range from A2:A5 to your data range.

2. Change the sheet name to your sheet names. I’ve used two sheets to combine. If suppose there is one more sheet named “augsheet” in your file, you can include that sheet as below. Just put a semi column and add the sheet name with range.

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

The above changes is only required when you are combining your own multiple sheets. Again check the below image to understand the query formula we used to combine multiple sheets in Google Sheets.

query formula used in combine multiple data sheet explained

Now we want to summarise or consolidate the data that we just combined. Below are the steps. Before that see the combined data below.

google sheets - multiple sheet combined data sample

How to Summarise or Consolidate Data from Multiple Sheets Using Formula

The below part is using 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 this result.

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

The formula again I’ll explain with the help of an image. See that below.

query formula used to consolidate data explained

We used the Query grouping to perform aggregation in this data consolidation formula. In our sample data column D contain the item description. We summarise the data based on this item field. When same item appears multiple times, this formula will merge and add it to one row.

The consolidated data will look like as below.

Consolidate Data from Multiple Sheets Using Formula - Result Sheet

That’s all. Hope you learned the above tutorial to consolidate data from multiple sheets using formula.

If you find any issue you can feel free to copy the mocked data file by following the link below.

Data Consolidation Sample

Conclusion

You can use the above query formula as an alternative to Pivot Table to summarise data. Also Query can be used an alternative to filter function.

Related: How to use QUERY function instead of FILTER function

Query function is very useful if you utilise it properly. So take your own time and learn the above Query function tutorial. It’s one single formula and you can learn it quickly. So try it.

See you again with another awesome Google Sheet tutorial.

LEAVE A REPLY

Please enter your comment!
Please enter your name here