Dec

Video series: 3 quick ways to merge multiple Google sheets

by Natalia Sharashova | updated on December 24, 2020 No Comments

There's a perfect solution we all seek to combine data from multiple sheets: open the table, make 3 mouse clicks at most, and get the exact desired result without any extra tools or adjustments. But until there's an AI like in movies, we have to use other software at hands. Today I will show you 3 powerful add-ons that will help you combine several Google sheets into one.

Despite all the amounts of instructions on the Web about merging numerous Google spreadsheets, eventually, they all come down to 3 major tasks:

Match and merge related records, usually from two sheets
Pull all data from another sheet and place it underneath the existing one
Pull data but consolidate common values in Google Sheets

On one hand, each task can be solved by the standard spreadsheet means (e.g. functions) that, sadly, require lots of manual modifications.

On the other hand, there are special add-ons that can be installed from Google Workspace Marketplace. Today I intend to cover 3 of them based on the task you are to solve: Merge Sheets, Combine Sheets, Consolidate Sheets.

Feel free to click one of three major problems above to jump right to the required solution or journey through all three tools (videos + transcripts) with me.

How to match and merge two sheets in Google Sheets

In this first video of a 3-part series on combining Google Sheets, I'm going to show you our Merge Sheets add-on. Use it whenever you need to match and merge two sheets into just one table by common values in one or more columns.

Tip. You will find a transcript of the video right below it.

Merge Sheets add-on for Google Sheets

Here's an example: a small business needs to purchase some winter equipment based on last year sales. The thing is, this time they've got a much shorter list of essentials.
The list of items before merge.

I will use Merge Sheets to match both lists and update the necessary information for all essential items.

Select main and lookup sheets

I go to the Add-ons menu and run the tool. It is available as an individual tool and as part of the add-ons collection – Power Tools.

Once the add-on is started, it picks up my current table immediately.

On the next step, I need to select a lookup sheet: it's a table with the info that I need to pull to my first main table. It's in a separate file – which is not a problem for the add-on. The tool lets me select any spreadsheet from Drive and even Search the entire Drive by a part of the spreadsheet name. I Add the file to the add-on, select the sheet, can also open and double-check the range, and go to the next step.

Fine-tune the options to merge two sheets

Here I am to pick matching columns to compare.

I have Item in my current file and, as you can see, the add-on auto-detects the same column in another spreadsheet. But, if necessary, I am free to choose any other column between existing ones.

Then goes the most important part: select columns to update or even add to my current list. Let's see:

I need to pull the correct ArticleIDs into my ID column.
Then, I think, I want to see Categories in my sheet as well – let's add it to the end of the table.
And all Sold numbers will become my Stock values – I mean the number of items to buy.

Add or update columns.
I press Next, and see some extra options that will help me merge two Google Sheets:

I'm going to tick off the first one to pull rows with all non-matching items just in case – they can be optional purchases for future.
Then I will go with a status column – it will help me filter the merged result by updated, or new items.
And I also want to color updates cells just to see them right away.
As for the place for the result, I'd like to update my main table directly.

I hit Finish and wait for the resulting message.

Now let's see what I've got.

Enjoy the result

The fill color hints right away that all ID and Stock numbers have been successfully updated based on the Items.
The Category column has been added based on the Items as well.
Thanks to the Status column, I can also see the rows that have a match and have been updated, as well as all newly added rows.

Merged lists of items.

I've got all these in just 5 quick steps of the Merge Sheets add-on. I invite you to install the tool from the add-ons' store to match and merge two similar Google sheets of your own.

If you have any questions, feel free to leave them in the comments section down below.

And look for part 2 of the Combine data series where I will show you our next add-on – Combine Sheets. Thank you.

How to combine multiple Google Sheets into one

This is our second video of a 3-part series on combining Google Sheets. This time, I will pull data from several Google sheets into one big table that dynamically changes along with the original tables. Another special add-on – Combine Sheets – will help me do that in just 2 steps while taking care of contents from common columns.

Tip. A transcript for the second video also follow right after.

By the way, if you haven't seen the first video where I matched and merged two Google Sheets, you can watch it here.

Combine Sheets add-on for Google Sheets

Now, for today, I have 3 different spreadsheets with shipping details from different stores. All tables have the same sets of columns arranged in no particular order. I want to combine them into one sheet to track all orders, existing and future ones.

Select sheets

I open Combine Sheets from the Add-ons menu. I will use it from our collection of add-ons – Power Tools > Merge & Combine group, but you can get its single version as well.

On the first step, the tool loads all sheets from the current spreadsheet. There's only one tab in my current file, which I'm going to select since I need to have its data combined with others.

Then I add other necessary files from Drive. The spreadsheets have similar names so I can quickly search by name part instead of browsing folders. As soon as the files are added to the list, I select all sheets I want to combine and go to the last step.

Choose how to pull data from other sheets

Here the add-on lets me fine-tune some options:

I need to pull data based on the same column headers. But since the order of the columns differs in all my tables, I have to select this first option. The add-on will consider all headers and place data to the corresponding columns.
I also want to see all future orders and changes on the existing orders in the combined result. Luckily, the add-on can build a special formula that will take care of that.
Let's place the result to a new spreadsheet and Combine our data.

Adjust combining options.

Pull data from multiple tabs in Google Sheets and see the result

The resulting message says:

how many sheets have been combined
gives a link to open the result right away
and supplies some instructions on how to enable the formula

Let me open the spreadsheet and prove you that it's not rocket science.

Look, the file has two sheets: the combined data will appear on the second one. For it to happen, I have to enable some functions used by the formula.

The first tab explains how to do that.

First, I have to start the add-on in this new file to turn on our custom function. Once you do that, this cell turns green meaning it's worked and the custom function is enabled. You can go ahead and close the add-on.
The next function to enable is IMPORTRANGE. It's the standard one for Google Sheets and is used to pull data from other documents. Here, I just need to give it access to each sheet I combined. This is also very easy: one-click next to a sheet name – and then press Allow access. Repeat for the rest until all cells turn green and show Connected.

Enable custom and the IMPORTRANGE functions.
And that's all! The formula is fully-functional now and successfully combines all my Google Sheets into one.

If I go ahead and change some values or even add a new record into one of the files, these changes will be reflected in the result.

To see all these for yourself, just install Combine Sheets from the add-ons store.

That's it for this part of the video series. Feel free to drop by the comments sections with your thoughts and questions. And check out our next video where I show how to not just pull data but consolidate common numbers in Google Sheets. Thank you.

How to consolidate data in Google Sheets

In this last video of a 3-part series on combining Google Sheets, I'm going to consolidate data from several tables together. That's right: I will not only pull data from other sheets – I will also aggregate it. Or, in other words, calculate numeric values on the go. Consolidate Sheets is my tool for the job today.

Tip. Look for a video transcript below.

If your task doesn't require consolidation, you can watch one of the previous videos where I combined several tables and matched and merged two Google Sheets.

Consolidate Sheets add-on for Google Sheets

You're looking at a simplified report on the store's revenue for the last couple of months. The thing is, I have two more reports like this from two other branches. They sell the same items, and obviously, the sales differ.

I'm going to show you how using Consolidate Sheets add-on you can combine all three spreadsheets into one sales report. In just 3 steps I will calculate the total revenue for each item from all three stores.

Select tables to consolidate in Google Sheets

Let's open Consolidate Sheets from Power Tools. If you watched our previous video on Combine Sheets, this add-on will look very familiar.

You start by selecting those sheets that you need to consolidate. At first, you'll see only the tabs from the current file in the list. But you can add any files from Drive with this special button. Though I can browse all folders, I prefer searching for the files by the word all file names contain.

Once all spreadsheets are added, I select sheets to consolidate, and proceed to the next step.

Pick the way to consolidate data

Here I need to choose the way to consolidate data.

Depending on the function I select, all numbers will be aggregated one way or the other. Since I want to get the total revenue, I will go with the SUM function.

Below I decide whether column headers or position of the values matter most for my result. As you can see, there are not only column headers in my tables, but also different items in the first column. The total must depend on both these factors. That's why I need to consolidate by both header labels and left column labels.
Select consolidation options.

Consolidate in Google Sheets in a place of your choice

Last but not least, I should choose a place for the result. A completely new spreadsheet will do just fine for me.

I click Consolidate and wait till the add-on is finished.

Review results

When the report is created in a new spreadsheet, the add-on offers me a link to open it right away.

And here you go: a total revenue for each item from all 3 stores together.

If you incorporate Consolidate Sheets to your daily job, I promise that getting aggregated data like that will become a matter of just a couple of minutes. Install the add-on from the Google Workspace Marketplace and let us know if you have any questions. Thank you!