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.
Consolidate Google sheets into one.

Before you start

When selecting sheets to join, remember that a spreadsheet with the result should not exceed the Google Sheets limit for 5 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: Add-ons > Consolidate Sheets > Start:
Run Consolidate Sheets.

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

  1. Access the group the same way from the spreadsheet menu: Add-ons > Power Tools > Merge & Combine:
    Run Power Tools.
  2. Click the Consolidate sheets icon on the Power Tools sidebar:
    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 on 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 (colored in 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 entire table 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.
    • 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: Choose where to place the result

Direct your Google Sheets summary report to one of the following locations:
Choose the location for 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.

Ask a question (posted publicly)

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.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.