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.
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.
Run the tool from the Google Sheets menu: Extensions > Consolidate Sheets > Start:
This step asks you to define the sheets you'd like to merge and calculate:
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:
When the required sheets are selected and the ranges are defined, hit Next.
Here you are to decide what consolidation options will suit you best:
Click Next to choose the location for the consolidated data.
The add-on lets you personalize the way it's going to consolidate data:
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.
Press Consolidate to merge and add up cells in Google Sheets.
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.
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:
If you place the result to the current file, the Connect files sheet will become active automatically.
Just hover your mouse over the errors in the Access status column and press each Allow access button that will appear:
Once all spreadsheets are connected, go to the Consolidated data tab and wait a bit till the result is loaded.
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:
Do that before granting the IMPORTRANGE access and you won't have to reload the spreadsheet as well.
Don't have option of consolidate sheet in extensions menu
Thank you for your comment.
Please specify if you refer to Consolidate Sheets as a single add-on or to the one from Power Tools. Also, please send us the screenshot of the Extensions drop-down menu and of the Extensions > Add-ons > Manage add-ons window at firstname.lastname@example.org. Thank you.
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.
Please provide more details on the issue:
1 Do you paste result as a formula or a value?
2 In step 1, do you select specified ranges or use 'All data'?
3 How exactly do you sort your data, via sort sheet menu, sort range menu or via filter settings?
I kindly ask you to send this info to email@example.com directly. Thank you.
how to make date wise consolidate transpose value paste ?
Thank you for your question. Unfortunately, your task is not quite clear. Please email us at firstname.lastname@example.org with some screenshots showing your source data and the expected result. We'll be happy to help.
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?
Thank you for your question. Please try consolidate your files with a formula. It will auto-update your summary table whenever records change in source sheets and if data appears or gets removed from the original tables. Hope this will do the job for you.
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.
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.
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 email@example.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!