Consolidate sheets in Google Sheets

Add up columns and rows from several sheets into one

Consolidate Sheets merges numerous sheets from different files and calculates numeric records at the same time. There's no need to open each spreadsheet — a quick search and a simple tree view let you handle all tables in one window. Consolidate Google sheets by rows and columns, or summarize data based on its position in tables. 11 built-in functions will aid with different summary reports that you can place wherever you prefer.

Try it for 30 days for free
Consolidate Sheets add-on for Google Sheets

With Consolidate Sheets you will

  • Consolidate multiple Google Sheets into one Add up columns, rows, and cells from multiple sheets and even files in one go.
  • Summarize data by headers Consolidate data by rows or columns only, or consider both header labels at once.
  • Benefit from 11 ways to merge and calculate SUM, COUNT, COUNT NUMBERS, AVERAGE, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR, VARP.
  • Add more files from Drive without opening them Choose all necessary files using the quick Drive search offered by the add-on.
  • Consolidate data by position Merge Google sheets and add up cells based on their position in the tables.
  • Have your summary report built by a formula So the result always shows the up-to-date info whenever anything changes in the original sheets.

How to consolidate data in Google Sheets

Add files from your Drive to merge more Google sheets
Add files from your Drive to merge more Google sheets
Select sheets and identify your data ranges
Select sheets and identify your data ranges
Pick a function and required labels to consolidate sheets in Google Sheets
Pick a function and required labels to consolidate sheets in Google Sheets
Choose where to add up your columns in Google Sheets
Choose where to add up your columns in Google Sheets
Check how many sheets have been consolidated in your Google Sheets
Check how many sheets have been consolidated in your Google Sheets
You can calculate ranges with a Google Sheets formula so records change in sync with the source tables
You can calculate ranges with a Google Sheets formula so records change in sync with the source tables
In this case, don't forget to connect all files so IMPORTRANGE does its job for you
In this case, don't forget to connect all files so IMPORTRANGE does its job for you

Consolidate multiple sheets

in 4 different ways

Add up records in columns based on headers in the top row
Add up records in columns based on headers in the top row
Merge and calculate by labels in the first column
Merge and calculate by labels in the first column
Consolidate cells and calculate numbers at the intersection
Consolidate cells and calculate numbers at the intersection
Summarize data based on the position of cells
Summarize data based on the position of cells

License benefits

Our customers get

Unconditional
30-day money-back guarantee
Free support
Volume discounts: the more you buy, the less you pay

These companies choose Ablebits products

Frequently asked questions

What is Consolidate Sheets and what do I need it for?

The tool is designed to merge multiple Google sheets and calculate ranges at the same time. Depending on the structure of your tables, you can add up columns by headers, labels in the left column, or the position of cells in the tables.

Why is Consolidate Sheets better than standard ways to summarize data?

The standard ways to consolidate data in Google Sheets feature Pivot table and the QUERY function. To get a summary report in the Pivot table, you will still have to copy all records to one place manually or using VLOOKUP. And sadly, both VLOOKUP and QUERY have proven to be intricate to learn and time-consuming to build.

Consolidate Sheets is a formula-free solution to add up columns and rows from multiple Google sheets. You won't have to bother with functions and waste your time copy-pasting values anymore. The intuitive and clear options make your experience with the add-on swift and easy.

How to add up columns and rows in Google Sheets from numerous files with your tool?

Go to the Extensions menu to start the tool and do the following:

  1. Select sheets you want to merge and calculate.
  2. Import more files from Drive if necessary.
  3. Pick the function to calculate numbers.
  4. Decide to summarize data by labels or position.
  5. Choose where to place the result.
  6. See if you want to have the result as a formula.
  7. Click Consolidate.

See a more detailed description here.

Can I automate your add-on to avoid going over the steps again?

In a way, yes :) If you choose to add up ranges with a Google Sheets formula, your result will always be connected to the original sheets. As soon as something changes there, your summary table will display the changes immediately.

How many sheets can your add-on summarize?

As many as you need while you don't exceed the Google Sheets limit for 10 million cells in a spreadsheet.

Should I open each spreadsheet I'd like to consolidate?

Forget about it. :) To merge several files, use the add-on to scan your Drive and add spreadsheets to a tree view. The tree view lets you manage all files and ranges within one window.

In case you still want to open the spreadsheet, the tool provides you with a link to do so for every file.

What functions will help me merge and calculate the data? Should I learn them?

The tool calculates your numbers with 11 consolidate functions: SUM, COUNT, AVERAGE, MAX, MIN, PRODUCT, COUNT NUMBERS, STDEV, STDEVP, VAR, VARP.

But there's no need to learn how to build formulas though. The utility takes this burden off your shoulders and does all the math for you.

The columns in my tables are the same but are in a different order. How will your tool manage that?

For cases like these, we introduced different consolidation options. To consolidate data from columns with the same headers, go with the option called Use header labels.

There are no headers in my sheets. The items listed in the first column are more important. Will your add-on help?

Of course. Simply choose the option to Use left column label on Step 2. :)

What if I need to consider both header rows and records from the first column?

Pick the setting to Consolidate by label and tick off both checkboxes: Use header label and Use left column. Thus, the tool will summarize data at the intersection.

And what if there are no headers in my sheets whatsoever?

No worries. If labels don't matter, the Consolidate by position option will save the day.

Choose your plan

12-month

  • 12-month access with all updates
$15.00
$12.00
Buy now

Lifetime

  • Get all new features and updates for free ever!
$39.95
Buy now

System requirements

Operating Systems

  • Microsoft: Windows 7 and up
  • Mac: High Sierra 10.13 and up
  • Chrome OS™

Browsers

  • Chrome™
  • Firefox
  • Safari (Mac only)