Consolidate data in Google Sheets

Start Consolidate Sheets

Run the tool from the Google Sheets menu: Extensions > Consolidate Sheets > Start:

Tip. The add-on can also be found in the Merge & Combine group of Power Tools:

Step 1: Select sheets to consolidate

This step asks you to define the sheets you'd like to merge and calculate:

1. Tick off the Sheets checkbox to select all sheets from the tree-view at once and add up them all.
2. To merge even more Google sheets, click Add files from Drive.

   Tip. There's a quick search available to find the files swiftly.
   
   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 just enter the part of the file name into the Search field and hit Search.
   

   Consolidate Sheets will check the selected Drive and show you the list of all partial and full name matches.

   Tip. The add-on is clever enough to let you open multiple files from Drive at once. Keep your Ctrl key pressed and click each file that you need. Once they are selected (you'll see them turn to light-blue), press Select:
   

   To cancel the selection when choosing the files, click the spreadsheet one more time.

3. In case you change your mind about any of the spreadsheets, select it and click the Exclude button. This will remove the file from the consolidation list.
4. For each sheet that you choose, you can specify the range to add up. Click All data to the right of the sheet name (in the Range column) and do one of these:
   • Enter the range from your keyboard.
   • Click the Select range icon and highlight the necessary cells on the sheet manually.
   • Click the Select range icon, pick any cell within the table you need, and press Auto select. The tool will identify the used range — cells with data till the first blank column & row — automatically:
     

When the required sheets are selected and the ranges are defined, hit Next.

Step 2: Select consolidation options

Here you are to decide what consolidation options will suit you best:

1. Choose one of 11 commonly known functions from the drop-down menu to merge and calculate your tables: SUM, COUNT, AVERAGE, MAX, MIN, PRODUCT, COUNT NUMBERS, STDEV, STDEVP, VAR, VARP.
2. Pick the way to summarize your data:
   • Consolidate by label is the option to go when your tables have the same headers arranged in a different order:
     • To consider headers in the top row, check Use header label:
       
     • To add up records based on headers in the first column, tick off Use left column label:
       
     • If the top row is as important as the left column, you can select both options to consolidate by both columns and rows:
       
     Note. Keep in mind that the add-on ignores empty cells. If you choose to consolidate by labels, the columns or/and rows with blank labels will be ignored. If all headers are missing, the add-on will return one empty cell as a result.
   • Use the Consolidate by position option if your tables are structured identically and the labels are don't really matter:
     

Click Next to choose the location for the consolidated data.

Step 3: Select additional options

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

Get the result

Once the add-on summarizes your data, it will prompt you how many sheets have been consolidated. You will find the result in a new place calculated just as you instructed in the options.

Tip. If you chose to put the result to a new spreadsheet on step 3, the add-on will provide you with a link to open the new file right away:

Consolidate several files with a formula

When you consolidate 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 consolidated summary.

Our add-on suggests the way of doing that correctly:

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:
   
2. For your convenience, the add-on creates two sheets:
   • Consolidated 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 Consolidate Sheets first to enable our custom functions, 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 Consolidate Sheets (which is unnecessary unless you put the result to a new spreadsheet where our own custom functions 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:
   

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

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

What is a scenario?

A scenario is a saved set of settings you choose during data consolidation.

Once you've consolidated your sheets, you can save these settings for future use. The next time, you just run the scenario, and the add-on will consolidate your sheets automatically using the saved preferences.

Save the scenario

To save the extension settings, click Save scenario when you see the final message after the add-on finishes processing your data:

You'll see an outline of your scenario with the settings you used. There, you can customize some details as needed:

1. Name the scenario so you can quickly identify it later.
2. Select sheets to consolidate every time you run the scenario:
   • Use [Selected sheet] to handle the active sheet when the scenario runs.
   • Specify other sheets to consolidate.
3. Define ranges for consolidation:
   • Select [All data] for entire used ranges.
   • Use [Selected range] to handle cells you manually select before running the scenario.
   • Enter exact cell ranges if needed.

1. Confirm that other settings are correct.

When everything looks good, click Save to create the scenario.

Run your scenario

To use a saved scenario, go to Extensions > Consolidate Sheets > Scenarios, select the desired scenario, and click Start.

The add-on will immediately consolidate your sheets using the saved settings.

At the end, you'll see a message summarizing which scenario was used, how many sheets were consolidated, and where to find the result:

Edit or delete scenarios

To modify a scenario, go to Extensions > Consolidate Sheets > Scenarios, select the scenario, and click Edit:

You'll see the scenario details, where you can update scenario name, selected sheets and data ranges:

Click Save to keep the changes, Run to apply them immediately, or Delete to remove the scenario permanently.

Share scenarios

Scenarios can be shared with teammates or used across multiple accounts. You can share all scenarios or specific ones.