Combine data from multiple sheets into one

Start Combine Sheets

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

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

Step 1: Select the sheets to combine

On this step, you choose the spreadsheets you want to combine:

1. Tick off the Sheets checkbox if you want to combine all spreadsheets from the tree view together.
2. Use this triangle next to the file name to expand/collapse the list of all sheets from this spreadsheet. Select the sheets you want to combine.

   Tip. If you plan to combine using a formula and want to select some blank sheets where the data may appear later, make sure these sheets have the same header row as in other sheets. Otherwise, the formula won't be able to include such sheets in the result.
   
   As for the range for such sheets, don't specify it (like A1:F1). Instead, keep it as All data, and the add-on will do the rest.

3. 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 window instead.
   
   Before the search, feel free to stay on the Recent tab to look through all recently opened spreadsheets, or switch to a specific tab to search the specific place: My Drive, Shared drives, Shared with me, or Starred.
   

   Then simply enter the word your spreadsheet name may contain and press Search.
   

   The add-on will scan the selected Drive folder for both exact and partial name matches.

   Tip. You can add a few files at a time. Keep the Ctrl button pressed and click the sheets names to see them highlighted in light-blue. Click Select to work with these files or click on the names once again to cancel the choice:
   
4. 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.
5. 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:
     

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

Step 2: Select the columns to combine

Decide which columns from your tables should be combined at this stage:

1. 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.
2. Select the top checkbox to include all columns in the result, or check the boxes next to specific columns to combine only them.
3. Sheets with column indicates how many sheets you chose to combine contain each column. This way, you'll know if any sheet lacks this or that column.
4. If the column list is too long, you can double-check the number of the selected columns here.

Step 3: Tweak extra options to combine spreadsheets into one

The add-on lets you personalize the way it's going to combine data:

1. Choose how to copy the data from the sheets
   • Add sheet names in a new column to the right will insert a new column with the names of the original sheets next to the combined data. This will help you easily identify which sheet each row of data came from.
   • 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.
   • 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, will add either an empty row or the name of the source sheet between the tables so you could easily find where one range ends and the next one begins.
2. Optionally sort the result by one or several columns either A>Z or Z>A. Click that plus icon to add more columns to sort by.
3. Decide where to put the result:
   • Select New spreadsheet to get the resulting table in a new file. Combine Sheets will create it automatically in your Drive. Click Browse to change the location and pick a destination folder for a new file on your Drive.

     Note. In Combine Sheets within Power Tools, you can also manually enter the path to the destination folder.

   • Use the New sheet option to place your output table to a new tab within the opened file.
   • Next is the possibility to add more sheets to the existing result. Select that resulting sheet in this drop-down (assuming the add-on is opened from the spreadsheet with the result). All sheets selected on Step 1 will then be added to this resulting table.
     Tip. Look through the detailed instructions on how to add sheets to the existing result below.
4. In case you want to put the combined data to a particular place in a current spreadsheet, go with Custom location and enter or select the leftmost cell of the future joined table.

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. 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 IMPORTRANGE, the instructions will be placed on the Connect files tab along with the info about the formula.

Add more sheets to the existing result

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

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:
   
3. On Step 3, select Add to the existing result and pick the sheet with your previously combined data from the drop-down:
   
   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:

Change sorting without restarting the add-on (for formula-based results)

If you combined sheets using a formula but need to adjust the sorting, there's no need to restart the add-on. You can update the sorting order or change the columns used for sorting directly within the formula.

1. Click on a cell with the resulting formula, and expand the formula bar so it's easier to read and edit.
2. Find the sorting section at the end of the formula:
   {selected_columns; {"author", "", ""}; {"desc", "", ""}}
   
   • The first part {"author", "", ""} lists the columns used for sorting.
   • The second part {"desc", "", ""} defines the sorting order for each column.
3. To change or add columns for sorting, enter their names:
   {"year","author","title"}

   Note. You can only use column names that you selected to combine on Step 2. Any column not included in the original selection won't work.

4. Adjust the sorting order for each column. Use "asc" for A>Z (ascending) and "desc" for Z>A (descending):
   {"desc","asc","asc"}
5. Press Enter, and your result will update instantly.

What is a scenario?

A scenario is a saved set of the options you select on each step of the add-on.

You save the settings when the add-on processes your data and shows the resulting message. Then you just run this scenario, and the tool start to combine the info from multiple sheets immediately.

Save the scenario

To create your scenario, click Save scenario in the resulting message once the add-on is done with your data:

You will see your future scenario outline with the options you've just used. You can adjust a few of them to your convenience:

1. Name your scenario in a way to understand what it does and find it among other scenarios quickly
2. Select the sheets that will be combined each time you run the scenario:
   • One of them can be [Selected sheet] — the sheet where you stand when running the scenario.
   • Specify other sheets to always combine all data from them.
3. Identify the exact ranges to combine the data from:
   • Keep [All data] so the add-on automatically takes the entire used ranges for work.
   • Go for [Selected range] to work with the cells you manually select before running the scenario.
   • For the tool to handle the exact cells, simply enter the desired ranges of cells.
4. Check if all other options you've just used are correct.

   Note. You cannot change these options here. To modify them, please restart the add-on to pick the correct settings on steps 1-3.

Once everything's set, click Save and the scenario will be created.

Run your scenario

To start the scenario, go to Extensions > Combine Sheet > Scenarios, find the necessary name and click Start.

This will run the tool and start processing your data right away per all the settings.

In the end, you will get the resulting message saying what scenario has been used, how many sheets have been combined and where to look for the result:

Edit or delete scenarios

To edit your existing scenario, go to Extensions > Combine Sheet > Scenarios, pick the scenario and click Edit:

You will see the familiar scenario outline. The editable fields are the same: name, sheets, and data ranges:

Press Delete to remove the scenario forever, Save to keep the changes, or Run to start it right away with the new settings.

Share scenarios

You can share all or only certain scenarios with your teammates or between your different Google accounts.