Video: how to pull data from other Google sheets into one table

Today's video features the easiest way to combine numerous Google sheets into one. Combine Sheets add-on brings data from different documents and common columns together, keeps your formatting, and automatically supplies the resulting table with the up-to-date info from the source sheets.

Video transcript: combine data from numerous spreadsheets

Combine Sheets is your must-have add-on if you often pull data from multiple sheets into one.

It will either match headers and combine records in common columns regardless of their order, or simply place all your tables one after the other.

You don't need to open each file or copy-paste the data anymore. Combine Sheets will do everything for you and make sure the result stays in sync with the original tables.
2 ways to combine data.
Just don't mistake this tool for another – Merge Sheets. That one works a bit different – similar to the VLOOKUP function. It compares key columns and updates related records in the main sheet with new values from the lookup table.

So as to back up every word, let me quickly combine data from multiple sheets using Combine Sheets add-on.

Run the add-on

I'm going to combine 5 lists of books available in a book store into one complete list.

Once you install the tool, it will appear in the Add-ons menu.

Select sheets to combine

On its first step, select all sheets you want to combine.

You'll see that the current file is already in the list, so you just need to add other spreadsheets. There's no need to open them for that at all – you simply import them from Drive directly to the add-on.

What's more, you can quickly locate certain files by their names – for example, all my spreadsheet names have the word 'book' in them.

So: add the files to combine, select the required sheets, and that's – half the job already!
Select sheets to combine.

Adjust extra settings

On the next and the last step, you just need to fine-tune some extra options:

  • I'll keep the first one selected so the add-on could place records from the same columns in all files together.
  • And I suggest going with a formula if you want all new future records to appear in your resulting table automatically.

Then choose where you want to have that combined sheet and hit Combine.
Tweak additional settings.

Have Combine Sheets build formulas for you

Whenever you decide to pull data using a formula, the standard Google Sheets IMPORTRANGE function helps us do that on top of our own custom functions.

And in this case, there's one more simple thing you need to do: connect IMPORTRANGE to all sheets you combine. Just hover your mouse over each error you see next to a sheet name and press Allow access. If you're new to IMPORTRANGE, we will explain this in detail in another video.

When all statuses are green and say Connected, the combined data will appear.
Grant IMPORTRANGE access to other sheets.
All 5 tables in one sheet, with records arranged by their columns.

If I make any changes to one of the source sheets, the same will appear in the result.

Google Sheets limits to keep in mind

Just keep in mind that your resulting spreadsheet must fit the Google Sheets limit for 5 million cells.

Also, there are some things you should know about the way the formula returns data. To look through them, visit the help page by clicking the question mark icon at the bottom of the add-on. There you'll learn, among other things, why you won't see empty rows in the result, and what happens with the mixed data within a column.

Find your Combine Sheets in the Google Store as a separate add-on or as part of add-ons collection – Power Tools.

If you have any questions, 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.