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
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.
- Save time with scenarios Save your frequently used options as scenarios and run them instantly without repeating the steps.
- 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.
- Share saved scenarios Export saved scenarios to share with your team or make backups, and import them to use preconfigured settings instantly.
How to consolidate data in Google Sheets
Consolidate multiple sheets
in 4 different ways
Ablebits Docs
Have a look at online help pagesLicense benefits
Our customers get
30-day money-back guarantee
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:
- Select sheets you want to merge and calculate.
- Import more files from Drive if necessary.
- Pick the function to calculate numbers.
- Decide to summarize data by labels or position.
- Choose where to place the result.
- See if you want to have the result as a formula.
- Click Consolidate.
See a more detailed description here.
Can I automate your add-on to avoid going over the steps again?
Absolutely! Use scenarios to save your preferred settings, so you can run them with just one click. Scenarios eliminate repetitive steps, making the process fast and efficient.
If you prefer, you can also generate a Google Sheets formula for the result. This keeps the summary connected to the source sheets, updating automatically whenever the original data changes.
Can I share my saved scenarios?
Yes, you can share your saved scenarios with teammates or between accounts. Export your scenarios to a file and send it to others or save it as a backup. Once imported, the scenarios can be run 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.
What if my sheets don’t have headers, and the first column is the key?
Simply choose the option to Use left column label on Step 2. :)
Can I consider both headers and the first column simultaneously?
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.
What if my sheets don’t have headers or labels at all?
No worries. If labels don't matter, the Consolidate by position option will save the day.
System requirements
Operating Systems- Windows: Windows 10 and up
- Mac: macOS Catalina 10.15.7 and up
- Chrome OS™
Browsers- Chrome™
- Firefox
- Safari (Mac only)
