Combine data from multiple sheets into one

This tutorial shows how to use the Combine Sheets add-on to join tables from multiple sheets together. Learn how to save the column order and initial formatting of combined tables and put the result where you decide. Have the united data pasted as values or get it as a formula to reflect all future changes made in the original sheets.
Combine sheets based on their headers.

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

Before you start

Please bear in mind that Google Sheets limits the total number of possible cells in your file up to 10 million cells.

How to use the Combine Sheets add-on

Start Combine Sheets

Run the utility from the Google Sheets menu: Extensions > Combine Sheets > Start:
Run the add-on from the Google Sheets menu.

Tip. You can also find Combine Sheets in the Merge & Combine group in the Power Tools add-on:
The Combine Sheets icon in Power Tools.

Step 1: Select the sheets to combine

On this step you choose the spreadsheets you want to combine:
Check the sheets you want to combine.

  1. Tick off the Sheets checkbox if you want to combine all spreadsheets from the tree view together.
  2. Press Add files from Drive to import and merge more files.
    Tip. If you don't remember the exact location of the file or simply don't want to browse from folder to folder, use the Search field at the top of the Import files from Drive window instead.

    Simply enter the word your spreadsheet name may contain, press Find, and the add-on will scan all Drive folders, including Shared with me, for both exact and partial name matches. You will see the list of these spreadsheets in the Combine Sheets tree view.

    Hover the mouse cursor over any spreadsheet, and you will see its location:
    Search for specific files all over your Google Drive using a part of its name.

    Tip. You can add a few files at a time. Just click their names to see them highlighted in light-blue. Click Add to work with these files or click on the names once again to cancel the choice:
    Add more sheets to bring them together.
  3. If you added the spreadsheet by mistake or simply no longer want to use it for merging, select it in the list and click the Exclude button.
  4. If some sheets have extra data, you can limit the range of interest. To do that, click All data next to the sheet name under the Ranges column and do one of the following:
    • Type the range.
    • Click the Select range icon and highlight the cells on the sheet manually.
    • Click the Select range icon, select any cell within your table, and press Auto select. This will automatically pick the used range — till the first empty column & row:
      Select the needed range manually.

When the sheets you want to work with are selected, click Next.

Step 2: Tweak extra options to combine spreadsheets into one

The add-on lets you personalize the way it's going to combine data:
Use additional options to merge sheets.

Choose how to copy the data from the sheets

  • If your tables contain similar columns arranged in different ways, pick Consider table headers. This option lets the add-on combine worksheets based on their headers.
    Tip. This way you will avoid extra header rows before each table — only the data will be transferred.
  • Use a formula to combine sheets. This one will pull data using a specially created formula. The formula will auto-update records whenever they change in source sheets or if the number of rows and/or columns changes in original tables.
    Note. There are some peculiarities in the way formula combines data. We strongly suggest you go through them to understand what exactly you can expect from the formula.
    Tip. This is a beta feature at the moment: your feedback is eagerly anticipated and much appreciated!
  • If you want to save the original look of each table, check the box called Preserve formatting.
  • The last option, Separate the copied ranges by a blank row, will add an empty row between the tables so that you could easily find where one range ends and the next one begins.

Decide where to put the result

  • Select New spreadsheet to review the resulting table in a new file. Combine Sheets will create it automatically.
  • Use the New sheet option to place your output table to a new tab within the opened file.
  • In case you want to put the combined data to a particular place in a spreadsheet, tick off Custom location and enter or select the leftmost cell of the future joined table.
  • Last but not least is the possibility to add more sheets to the existing result. Just select that resulting sheet in this drop-down (assuming the add-on is opened from the spreadsheet with the result). All sheets selected on Step1 will then be added to this resulting table.
    Tip. Look through the detailed instructions on how to add sheets to the existing result below.

If you want to select other sheets or add a few more, click Back.

Press Combine to merge the data.

Get the result

As the final confirmation, you will see the message saying how many sheets have been combined. Your data will be collected together in a new place according to the options you selected.

Tip. If you choose to put the results to a new spreadsheet on Step2, the add-on will offer you a link to open the new file right away.
Tip. You will also have the link for the instructions on how to quickly add more sheets to this result in future. When you combine sheets using a formula, the instructions will be placed on the Connect files tab along with the info about the formula.
Get the result message saying how may tables were joined.

Combine several files with a formula

When you combine data from different files and choose to have the result as a formula, the add-on uses standard Google Sheets IMPORTRANGE to pull records together.

By default, for the function to work properly, you must grant it permissions to connect to other spreadsheets. Otherwise, you will see formula errors rather than the combined data.

Our add-on suggests the way of doing that correctly. See how to do that in the following video or read the instructions below:

  1. Whenever IMPORTRANGE is used in the outcome formula, you will see a resulting message telling you how to connect the function to other files:
    The resulting message with the instructions on how to connect the files.
  2. For your convenience, the add-on creates two sheets:
    • Combined data with your outcome formula.
    • Connect files to IMPORTRANGE where you are to grant all permissions.
    Tip. If you place the result to a new spreadsheet, click the Open new spreadsheet link to jump right to the Connect files tab. Run Combine Sheets first to enable our custom formula, then proceed with the steps below.

    If you place the result to the current file, the Connect files sheet will become active automatically.

  3. The Connect files tab will always contain the following:
    • the description of how to connect the sheets
    • the reminder to run Combine Sheets (which is unnecessary unless you put the result to a new spreadsheet where our own custom function must be enabled as well)
    • the list of all spreadsheets that must be accessed by the formula
    • the access status

    Just hover your mouse over the errors in the Access status column and press each Allow access button that will appear:
    You need to connect these sheets to IMPORTRANGE.

  4. As soon as you do that, the Access status will say Connected and the cells will turn green:
    When the IMPORTRANGE has all permissions.

Once all spreadsheets are connected, go to the Combined data tab and wait a bit till the result is loaded.

Limitations of standard functions used in the formula

Along with our own custom functions, the resulting formula uses a couple of standard ones: QUERY and IMPORTRANGE. Here are some things you should know about the result returned by the formula:

  1. If you place the formula to a new spreadsheet, our custom functions (COLUMN_LIST_CS, CONSIDER_HEADERS_CS) won't start working until you run Combine Sheets once. Since that file is newly created, this way you enable our custom function there.

    Do that before granting the IMPORTRANGE access and you won't have to reload the spreadsheet as well.

  2. Formulas won't work without our add-on. Whether you use the tool to create formulas or make copies of the shared files with formulas, you should have the add-on installed. Otherwise, there's just nothing to maintain our custom functions.
  3. The QUERY function from the outcome formula doesn't include completely blank rows to the result.
  4. If there are mixed data types (e.g. numbers and text) within a column, the QUERY function will return only the majority data type leaving the rest as empty cells.
  5. If some columns are missing from one or more tables, as a result, you would normally expect to see empty cells in such columns:
    Empty cells in the result where the column was missing.
    However, QUERY makes us place strings of spaces into these cells turning them into non-blanks. If you try and use COUNTA on such a column, the result will be misleading since COUNTA processes whitespaces as well.
  6. For the same reason, if on Step2 you don't choose the option Consider table headers, QUERY will add an entire extra column to the right of the resulting table. The column will not be empty — its cells will be filled with whitespaces and you won't be able to edit them.
  7. If one of your tables has only 1 row and you select the option Consider table headers, the QUERY function will return an error. In cases like this, we recommend you to exclude such sheets from combining or avoid matching columns.
  8. Due to the way Google Sheets processes formulas, the number of columns in the outcome table is always fixed. It means that new columns added to your source tables won't appear in the result — Google Sheets will not adjust the ranges.

    However, if you choose to put the formula to a new tab of the opened file rather than to a new spreadsheet, the result will include columns inserted in the middle of the original tables.

  9. Google Sheets functions cannot process and take any objects (e.g. images, charts, conditional formatting, etc.) but values to a resulting table.

Add more sheets to the existing result

Combine Sheet offers a way to quickly add a few more sheets to the previously combined data.

Tip. This is especially useful if your result consists of dozens of sheets and you don't want to combine them all over again.
  1. Open the spreadsheet that has your resulting table and run the add-on from there: Extensions > Combine Sheets > Start.
  2. On Step 1, select that resulting table along with all those sheets that you want to add to the result:
    Select the resulting sheet as well as the sheet(s) you want to add.
  3. On Step 2, select Add to the existing result and pick the sheet with your previously combined data from the drop-down:
    Select the sheet with the existing result to add more data to it.

    Note. If you previously combined data using a formula, don't forget to tick off Use a formula to combine sheets as well. The add-on will then detect all sheets with the formula and offer them in the drop-down.
  4. Click Combine.

The add-on will add additional selected sheets to the existing result and update the formula accordingly if you use it. Once it's finished, you'll see a corresponding result message:
Add more sheets to the existing data – result message.