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:
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.
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.
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.
I will use Merge Sheets to match both lists and update the necessary information for all essential items.
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.
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 press Next, and see some extra options that will help me merge two Google Sheets:
I hit Finish and wait for the resulting message.
Now let's see what I've got.
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.
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.
By the way, if you haven't seen the first video where I matched and merged two Google Sheets, you can watch it here.
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.
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.
Here the add-on lets me fine-tune some options:
The resulting message says:
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.
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.
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.
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.
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.
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.
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.
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.
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!