Video: how to consolidate data from multiple Google sheets by common columns

This video shows how to merge several Google sheets into one and calculate all numbers from the same columns at the same time. With a handy add-on that we offer, you won't have to open each file and copy-paste data manually anymore.

How to merge multiple sheets and consolidate data at the same time: video transcript

Whether you need to track the student progress or attendance over a few weeks or months, or figure out the average annual earnings per employee, there's got to be a clever solution in spreadsheets.

However, soon enough you discover that merging several Google Sheets into one is enough of a problem; let alone consolidating numbers from common columns across all sheets at the same time.

So. Is there an effortless way to summarize monthly sales reports from several spreadsheets into one?

Summarize three tables by labels.
How can you find the total sold in each region for the past 3 months?

Standard ways to consolidate in Google Sheets

The only Google Sheets function capable of solving this on its own is QUERY. But unless you are a power-user, you'd spend more time learning its language than summarizing data manually.

You might also be familiar with the Consolidate tool from Excel. Even so, importing all spreadsheets to another platform and back can mess up all your formatting and waste your time.

The QUERY function and Consolidate for MS Excel.

Why Consolidate Sheets is a better solution

The good thing is – an extremely easy solution for this task does exist. We offer Google Sheets its own Consolidate Sheets add-on! It aggregates your data across multiple sheets from one or different files.

The tool is highly intuitive, letting you consolidate data in just 3 quick steps with 11 aggregate functions.

The Consolidate Sheets add-on for Google Sheets.

You will create a full summary sheet of all numbers either by their position in tables or by common table headers. This extends the number of possible results for the same data sets even further.

And guess what – there is absolutely no need to open each file: everything is done straight from the add-on.

Let's have a quick look at it, shall we?

Run the add-on and choose the sheets

Open the tool from the Add-ons menu. If you have Power Tools installed, you will find Consolidate Sheets in the Merge & Combine group.

The first thing you are to do is select those sheets that you want to merge and calculate. You can quickly search your Drive for as many additional spreadsheets as you need, add them to the list, and tick off the sheets to sum across.

Select sheets to consolidate.

Tweak consolidation options

The next step invites you to fine-tune consolidation options.

The add-on will calculate your numbers using one of 11 Google Sheets functions. Whether you need to sum, count, find the maximum value or the average of all records, just pick the corresponding function from the drop-down list.

Here you should also decide on the way to summarize your cells:

  • Choose to consolidate by label if all your tables throughout all sheets have the same headers.
    • To consider common headers in the top row and add up records in columns, select the option use header label.
    • If the leftmost column is more important, use left column label instead to merge and calculate in rows.
    • Feel free to tick off both options to calculate numbers at the intersection.
  • In case your sheets don't actually have headers nor do they share the structure, the last option is just right for you – it summarizes data based on the position of the cells.

Select consolidation options.

Decide where to place the summary

The final step lets you appoint a place for the result:

  • You can have the add-on consolidate your data in Google Sheets and put the summary either to a brand new spreadsheet or to a new sheet within your current file.
  • If you already have some specific cells for the outcome in mind, you can set them using a Custom location option.

Choose where to place the result.
Once you're ready, click Consolidate and enjoy a ready-made summary from multiple spreadsheets.

I invite you to install Power Tools or a single Consolidate Sheets from the add-ons store to try everything out on your data. And if you still have any questions left, don't hesitate and contact us – we'll be happy to help!

You may also be interested in

Ukraine flag War in Ukraine. Here's what Ablebits is doing to make sure our team and projects are safe.