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
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.
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.
Go to the Extensions menu to start the tool and do the following:
See a more detailed description here.
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.
As many as you need while you don't exceed the Google Sheets limit for 10 million cells in a spreadsheet.
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.
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.
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.
Of course. Simply choose the option to Use left column label on Step 2. :)
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.
No worries. If labels don't matter, the Consolidate by position option will save the day.