Consolidate data in Google Sheets

This page will guide you through 3 simple steps of the Consolidate Sheets add-on. By the end, you'll have all your tables not only merged but also calculated into one Google Sheets summary report. And you will have that report either as values or as a formula that will automatically reflect all future changes made in original sheets.
Consolidate Google sheets into one.

Video: How to consolidate data from multiple Google sheets

Before you start

When selecting sheets to join, remember that a spreadsheet with the result should not exceed the Google Sheets limit for 10 million cells. If it does, the add-on will let you know and won't consolidate your data.

How to use the Consolidate Sheets add-on

Start Consolidate Sheets

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

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

Step 1: Select sheets to consolidate

This step asks you to define the sheets you'd like to merge and calculate:
Select sheets 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. Just enter the part of the file name into the Search field of the Import files from Drive window and hit Find. Consolidate Sheets will check your entire Drive and show you the list of all partial and full name matches.

    If you're not sure where the found spreadsheets are located, simply hover your mouse cursor over them and you'll see a tip with a full path:
    Check the location of each file in your Drive.
    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 Add:
    Select multiple files to consolidate in Google Sheets.
    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:
      Identify the range to consolidate in Google Sheets.

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:
Tweak the options to consolidate Google sheets.

  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:
        Google Sheets: summarize data in columns.
      • To add up records based on headers in the first column, tick off Use left column label:
        Summarize data in rows.
      • If the top row is as important as the left column, you can select both options to consolidate by both columns and rows:
        Google Sheets: summarize data by 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:
      Summarize by position.

Click Next to choose the location for the consolidated data.

Tip. Or press Back to return to the first step and select other sheets or ranges.

Step 3: Select additional options

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

Choose how to transfer the data from the sheets

Use a formula to consolidate sheets. This one will pull data using a specially created formula. The formula will auto-update your summary table whenever records change in source sheets and if data appears or gets removed from the original tables.

Note. At the moment, the formula can be created only if on step 2 you chose to sum or count data. Other aggregate functions are not supported.
Note. There are some peculiarities in the way the formula consolidates data. We strongly suggest you go through them to understand what exactly you can expect from the formula.

Decide where to put the result

  • The New spreadsheet option will merge and calculate the data in a completely new file.
  • Choose New sheet and have the summary table placed to a new tab in the currently opened file.
  • Or pick Custom location and specify a top left cell for the result. You can type it from your keyboard or click the Select range icon and locate the cell using the add-on.

Press Consolidate to merge and add up cells in Google Sheets.

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 sheets result.

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:
    The resulting message with the instructions on how to connect the 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:
    Allow IMPORTRANGE access to all spreadsheets you consolidate.

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

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

Limitations of the 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. At the moment, the formula can be created only if on step 2 you chose to sum (SUM) or count (COUNT) data. Other aggregate functions are not supported.
  2. The formula always checks if the columns you consolidate from all sheets contain numeric values in the first two rows (excluding the header row). If there are no numeric records in the first two rows of any table at all, the formula will be built but it won't return anything to the result.
  3. If you place the formula to a new spreadsheet, our custom functions (CONSIDER_HEADERS_CONS, AGGREGATE_CONS, COLUMN_LIST_CONS) won't start working until you run Consolidate Sheets once. Since that file is newly created, this way you enable our custom functions there.

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

  4. 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.

Responses

Is it possible to reorder based on filters after consolidating data? Every time I try to order descending by column B, it adds a ton of empty rows and remains ordered column A alphabetical.

Lauren Hammond says:
February 26, 2022 at 4:21 pm

Is there a way that I can keep this sheet refreshing as I continue to add data to the source file without having to re-run the Add-on everytime?

Does this addin have the option of linking cells on the target consolidation range back to the source cells, and then sum all target cells by row & column labels? This is what MS Excel is able to do and I need that same functionality.

Hello Rick,

Thank you for your question.

To consolidate your data by both column and row labels, please make sure to tick off both options on Step2: 'Use header label' and 'Use column label'.

As for links to source data like in our Combine Sheets Wizard for Excel, I'm afraid it's impossible to do the same in Consolidate Sheets. Since the latter calculates all numbers rather than pulls data, the result is the calculated summary of all sheets and there's simply nothing to refer to. However, this feature is already on the developers' list for Combine Sheets add-on for Google Sheets.

Hello Rick,

I just wanted to let you know that we've just released an updated version of our Combine Sheets add-on with a new way to combine sheets – using a formula. Since the formula is connected to your original files, all changes made in the source sheets will be automatically reflected in the resulting table.

Please visit the help page for more details and feel free to try the tool out. If you have any questions/difficulties/suggestions, please reach us at support@ablebits.com. We'll be glad to hear back from you :)

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.