Combine data from multiple sheets into one
This tutorial shows how to use the Combine Sheets add-on to join tables from multiple sheets together. Learn how to save the column order and initial formatting of combined tables and put the result where you decide. Have the united data pasted as values or get it as a formula to reflect all future changes made in the original sheets.
Video: How to pull data from other Google sheets into one table
Before you start
Please bear in mind that Google Sheets limits the total number of possible cells in your file up to 10 million cells.
How to use the Combine Sheets add-on
Start Combine Sheets
Run the utility from the Google Sheets menu: Extensions > Combine Sheets > Start:
Step 1: Select the sheets to combine
On this step you choose the spreadsheets you want to combine:
- Tick off the Sheets checkbox if you want to combine all spreadsheets from the tree view together.
- Use this triangle next to the file name to expand/collapse the list of all sheets from this spreadsheet.
- Press Add files from Drive to import and merge more files.
Tip. If you don't remember the exact location of the file or simply don't want to browse from folder to folder, use the Search field at the top of the Import files from Drive window instead.
Simply enter the word your spreadsheet name may contain, press Find, and the add-on will scan all Drive folders, including Shared with me, for both exact and partial name matches. You will see the list of these spreadsheets in the Combine Sheets tree view.
Hover the mouse cursor over any spreadsheet, and you will see its location:
Tip. You can add a few files at a time. Just click their names to see them highlighted in light-blue. Click Add to work with these files or click on the names once again to cancel the choice:
- If you added the spreadsheet by mistake or simply no longer want to use it for merging, select it in the list and click the Exclude button.
- If some sheets have extra data, you can limit the range of interest. To do that, click All data next to the sheet name under the Ranges column and do one of the following:
- Type the range.
- Click the Select range icon and highlight the cells on the sheet manually.
- Click the Select range icon, select any cell within your table, and press Auto select. This will automatically pick the used range — till the first empty column & row:
When the sheets you want to work with are selected, click Next.
Step 2: Tweak extra options to combine spreadsheets into one
The add-on lets you personalize the way it's going to combine data:
Choose how to copy the data from the sheets
- If your tables contain similar columns arranged in different ways, pick Consider table headers. This option lets the add-on combine worksheets based on their headers.
Tip. This way you will avoid extra header rows before each table — only the data will be transferred.
- Use a formula to combine sheets. This one will pull data using a specially created formula. The formula will auto-update records whenever they change in source sheets or if the number of rows and/or columns changes in original tables.
Note. There are some peculiarities in the way formula combines data. We strongly suggest you go through them to understand what exactly you can expect from the formula.
- If you want to save the original look of each table, check the box called Preserve formatting.
- The last option, Separate the copied ranges by a blank row, will add an empty row between the tables so that you could easily find where one range ends and the next one begins.
Decide where to put the result
- Select New spreadsheet to review the resulting table in a new file. Combine Sheets will create it automatically.
- Use the New sheet option to place your output table to a new tab within the opened file.
- In case you want to put the combined data to a particular place in a spreadsheet, tick off Custom location and enter or select the leftmost cell of the future joined table.
- Last but not least is the possibility to add more sheets to the existing result. Just select that resulting sheet in this drop-down (assuming the add-on is opened from the spreadsheet with the result). All sheets selected on Step1 will then be added to this resulting table.
Tip. Look through the detailed instructions on how to add sheets to the existing result below.
If you want to select other sheets or add a few more, click Back.
Press Combine to merge the data.
Get the result
As the final confirmation, you will see the message saying how many sheets have been combined. Your data will be collected together in a new place according to the options you selected.
Tip. If you choose to put the results to a new spreadsheet on Step2, the add-on will offer you a link to open the new file right away.
Tip. You will also have the link for the instructions on how to quickly add more sheets to this result in future. When you combine sheets using a formula, the instructions will be placed on the Connect files tab along with the info about the formula.
Combine several files with a formula
When you combine 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 combined data.
Our add-on suggests the way of doing that correctly. See how to do that in the following video or read the instructions below:
- Whenever IMPORTRANGE is used in the outcome formula, you will see a resulting message telling you how to connect the function to other files:
- For your convenience, the add-on creates two sheets:
- Combined data with your outcome formula.
- Connect files to IMPORTRANGE where you are to grant all permissions.
Tip. If you place the result to a new spreadsheet, click the Open new spreadsheet link to jump right to the Connect files tab. Run Combine Sheets first to enable our custom formula, then proceed with the steps below.
If you place the result to the current file, the Connect files sheet will become active automatically. - The Connect files tab will always contain the following:
- the description of how to connect the sheets
- the reminder to run Combine Sheets (which is unnecessary unless you put the result to a new spreadsheet where our own custom function must be enabled as well)
- the list of all spreadsheets that must be accessed by the formula
- the access status
Just hover your mouse over the errors in the Access status column and press each Allow access button that will appear:
- As soon as you do that, the Access status will say Connected and the cells will turn green:
Once all spreadsheets are connected, go to the Combined data tab and wait a bit till the result is loaded.
Limitations of standard functions used in the formula
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:
- If you place the formula to a new spreadsheet, our custom functions (COLUMN_LIST_CS, CONSIDER_HEADERS_CS) won't start working until you run Combine Sheets once. Since that file is newly created, this way you enable our custom function there.
Do that before granting the IMPORTRANGE access and you won't have to reload the spreadsheet as well.
- Formulas won't work without our add-on. Whether you use the tool to create formulas or make copies of the shared files with formulas, you should have the add-on installed. Otherwise, there's just nothing to maintain our custom functions.
- The QUERY function from the outcome formula doesn't include completely blank rows to the result.
- If there are mixed data types (e.g. numbers and text) within a column, the QUERY function will return only the majority data type leaving the rest as empty cells.
- If some columns are missing from one or more tables, as a result, you would normally expect to see empty cells in such columns:
However, QUERY makes us place strings of spaces into these cells turning them into non-blanks. If you try and use COUNTA on such a column, the result will be misleading since COUNTA processes whitespaces as well. - For the same reason, if on Step2 you don't choose the option Consider table headers, QUERY will add an entire extra column to the right of the resulting table. The column will not be empty — its cells will be filled with whitespaces and you won't be able to edit them.
- If one of your tables has only 1 row and you select the option Consider table headers, the QUERY function will return an error. In cases like this, we recommend you to exclude such sheets from combining or avoid matching columns.
- Due to the way Google Sheets processes formulas, the number of columns in the outcome table is always fixed. It means that new columns added to your source tables won't appear in the result — Google Sheets will not adjust the ranges.
However, if you choose to put the formula to a new tab of the opened file rather than to a new spreadsheet, the result will include columns inserted in the middle of the original tables.
- Google Sheets functions cannot process and take any objects (e.g. images, charts, conditional formatting, etc.) but values to a resulting table.
Add more sheets to the existing result
Combine Sheet offers a way to quickly add a few more sheets to the previously combined data.
Tip. This is especially useful if your result consists of dozens of sheets and you don't want to combine them all over again.
- Open the spreadsheet that has your resulting table and run the add-on from there: Extensions > Combine Sheets > Start.
- On Step 1, select that resulting table along with all those sheets that you want to add to the result:
- On Step 2, select Add to the existing result and pick the sheet with your previously combined data from the drop-down:
Note. If you previously combined data using a formula, don't forget to tick off Use a formula to combine sheets as well. The add-on will then detect all sheets with the formula and offer them in the drop-down. - Click Combine.
The add-on will add additional selected sheets to the existing result and update the formula accordingly if you use it. Once it's finished, you'll see a corresponding result message:
Responses
Robert Bobstein says:
May 16, 2024 at 2:10 pm
Hello,
It would be amazing to keep the name of the sheets in an extra column in the resulting new sheet.
Example, say I have an inventory, and every city has its own sheet, with the name of the city as sheet name.
Let say theres's 5 column per sheet, and in the resulting new sheet when the merge is completed, a sixth column would be added with the name of the source sheet.
Is this possible?
Hello Robert,
Thank you for sharing your feedback. We greatly appreciate it! Your idea has been forwarded to our development team for consideration.
Thank you.
Leticia Nobre says:
February 21, 2024 at 2:32 pm
Hi,
I have combined 3 sheets into one and put them on a general sheet with all the 3 sheets together. However, when I try to filter them, for example, by date, it appears to filter the data per sheet, not from all the dates combined from the 3 sheets.
Here's an example of a column from the general sheet, containing 3 names from each sheet, resulting in 9 names combined:
NAME
Aild
Carla
Borges
Amanda
Leticia
Johnny
Pedro
Matheus
Jeferson
But when i try to filter them alphabetically, it separates the data like this:
NAME:
Aild
Borges
Carla
Amanda
Johnny
Leticia
Jeferson
Pedro
Matheus
And I would like to have them filtered together like this:
NAME:
Aild
Amanda
Borges
Carla
Jeferson
Johnny
Leticia
Pedro
Matheus
Hw can I do this?
Dear Leticia,
Thank you for your question.
Could you please contact us at support@ablebits.com and send us several screenshots showing the options you select on Steps 2 of the add-on and the result sheet BEFORE sorting? Also please let us know how you sort your data, via table filter or via Data > Sort sheet or Sort range. Thank you.
Zoe says:
August 26, 2022 at 8:09 pm
I combined my sheets for college due dates and have different pages for each class. When I combined them onto a master page, it's organizing them by class. Is there a way to organize them by due date, which I have in the first column?
Hello Zoe,
Thank you for your comment.
Please try to use the standard Sort and Filter options in Google Sheets to organize the data on your master sheet the way you need.
If any assistance is still needed, please email us with a more detailed description of your task and a few screenshots showing the data you have and the result you'd like to get. We'll try to help you further.
Lei Yu says:
April 7, 2022 at 10:37 am
I combined sheets with non-empty first columns and after execution, I found the first column in combined data sheet contains some empty entries, I want to correct my combination process and I am looking for some advice
Hello Lei Yu,
Thank you for your comment.
For us to understand what is causing the issue, please specify what options you choose on each step of the add-on and send us screenshots with the selected options at support@ablebits.com.
Also, if it's possible, please share an editable copy of your spreadsheet with us (support@apps4gs.com) containing a few sheets you combine. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by sending an email to support@ablebits.com.
These details will help us reproduce the issue on our side and find its cause. Thank you.
Carsen says:
June 28, 2022 at 7:36 pm
When I try Combine 6 Sheets it only works when I select new Sheet. I want the combined data to remain in the same sheet. Can you help with this?
Hello,
Thank you for your question. Please tick off Custom location in step 2 and enter or select the leftmost cell of the future joined table.
https://www.ablebits.com/docs/google-sheets-combine-multiple-sheets/#choose-action
Denis says:
March 24, 2022 at 4:43 pm
Hello there,
My "Combined data" sheet is combining about 9 sheets and I try to add a new one.
I'm trying to follow up your tip to add a new sheet:
1. Run Combine Sheets from the Combined data tab.
=> This is working well, I don't have a paid subscription yet because I'm still evaluating the extension
2. On Step 1, select Combined data along with the sheets you want to add.
=> OK
3. On Step 2, choose the option Add to the existing result and pick the resulting sheet from the drop-down.
=> The app is loading and then giving me this error message: "No sheets with the resulting formula have been selected from the current file on step 1"
I don't get it since I selected the "Combined data" sheet on step 1
Would you have any advise?
Hello Denis,
Thank you for choosing our product.
This error appears if you combine your first sheets without the formula, just as values. But when trying to add more sheets to the result, you select the option "Use a formula to combine sheets". So this message means that there are no results previously combined as a formula, hence, there's no formula to update.
In cases like this, just unselect the option to combine everything as a formula when adding more sheets. They will then be added as values to your previously combined data.
Please let me know if you need any further assistance.
MARY MAYBERRY says:
November 9, 2021 at 4:21 pm
Hello,
I am able to combine data beautifully until I try to use the formula function. It only asks for me to grant permission to the first sheet I add. And I get an error message on the combined pages, unless I combine without the formula. Is there a way to quickly update as new data comes in without using the formula? Or can you help me figure out why I cannot get the formula to work for me? The data I am combining is the same information across multiple individual sheets attached to google forms.
Hello Mary,
Thank you for contacting us. I'm afraid the only way to update the combined data automatically every time the source sheets are changed is to use the formula.
As for the error message you are getting, please send us its screenshot to support@ablebits.com so we could provide you with further assistance. Thank you.
Listyo says:
July 1, 2021 at 10:27 am
Is subscription is account can transferable to another account i need to give it to my other staff?
Hello Listyo,
Thank you for your question.
Yes, you can transfer your subscription to another account/user if necessary. Please find the detailed instructions on how to do this correctly here. In case you need help activating the add-on as well, please refer to this help page.
Don't hesitate to contact us again if you have any other questions.
Hashim J says:
September 24, 2020 at 2:17 pm
Hello,
Are you still developing the auto refresh? Will it refresh daily now ?
Hello Hashim,
Thank you for your question. We are about to release an updated version of Combine Sheets with a new way to combine data – using a formula that will be connected to your original files. Hence, all changes made in the source sheets will be automatically reflected in the resulting table.
The update about to come, I'll update you in this comments thread once it is published. Thank you.
Hashim,
I'm glad to tell you that the updated version of our Combine Sheets add-on is available. Now your joined sheets will be updated dynamically. We believe this will solve the problem of the add-on not being able to pull data automatically.
I hope this feature will be useful, otherwise please do not hesitate to email us at support@ablebits.com. We'll be glad to help.
Dmitrii says:
October 6, 2021 at 2:28 am
Hi. How to make dynamicaly updating?
Hello Dmitrii,
Please Use a formula to combine sheets in step 2, for more details please check this passage:
https://www.ablebits.com/docs/google-sheets-combine-multiple-sheets/#get-results-formula
wei li says:
September 17, 2020 at 3:19 pm
Hi, Thank for your help.
I couldn't find the "Combine data" in add-on. So do I need to add this function at first?
Hi Wei,
Thank you for your message. Yes, you'll need to install our add-on from the Store first: https://workspace.google.com/marketplace/app/combine_sheets/338552429820
Then you'll have 30 days of fully-functional trial to test the add-on and make sure it works for you.
Keri says:
July 21, 2020 at 8:06 pm
My sheets have 2 rows as headers. Is there a way to designate a 2 row header when combining my sheets? Right now I am getting my second header row appearing each time a new sheet is combined.
Hello Keri,
Thank you for your question. Yes, the current version of the add-on joins tables by a one-row header. However, I have forwarded your request to our development team. They will discuss this feature and we'll be sure to let you know if/when it is available. Thank you.
Mark Richards says:
May 19, 2020 at 5:01 pm
I would like to combine data from two different sheets, each having different columns. I do have one common field (column) in both of the sheets. I would like the data from the two sheets to be combined into one row (record) in the results sheet using the common field (column) as the relational key. Can this be done?
Hello Mark,
I believe our other tool, Merge Sheets, will do exactly what you need. It matches and merges data from two spreadsheets based on a key column(s). The single add-on can be installed directly through Google Sheets via Add-ons -> Get add-ons or you can find it within our Power Tools. Please test this tool out to see if it suits your needs.
If you have any other questions or need further assistance, feel free to contact us back.
Kerrie Townsend says:
April 14, 2020 at 7:27 pm
I am trying to combine serveral workbooks(150 different workbooks) each with 10 tabs in each workbook. I want to combine the -50 workbooks into one master workbook. The part I am having difficulty with is making sure each specific tab merged into the specific tab in the workbook.
Is this something I can do with combine sheets and if so how? Thanks for your help.
Hello Kerrie,
Thank you for your comment. You see, our Combine Sheets will simply join all the tables you select in one common sheet. So if you need to join only the particular tabs into one, check them all on the 1st step of the add-on and then repeat this procedure for the other tabs as well. Hope it will work for you.
Dave says:
April 10, 2020 at 1:22 pm
Is there a way to refresh? I have 31 sheets that get changed daily.
Thanks
Hello Dave,
Thank you for your comment. Sorry, but it is not possible to automatically refresh the resulting table in the current version of the add-on. You'll have to start the tool each time you update your sheet.
However, this feature is in our developers' roadmap and if the functionality is supported, we'll be sure to post a comment in this thread.
Hello Dave,
I have an update for you :) We've just released an updated version of our Combine Sheets that has a new way to combine data – 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.
I do hope you'll find this novelty helpful! If you have any questions/difficulties/suggestions, please share it with us by emailing at support@ablebits.com. Thank you!
Denzil says:
November 26, 2019 at 2:51 pm
Does it do a one time combine or over real time as data keeps getting aded to the source
Katerina Bespalaya (Ablebits Team) says:
November 26, 2019 at 7:16 pm
Hello Denzil,
I'm sorry, our add-on can't update the combined sheet automatically once the new data are added to the source sheets. You need to run the add-on each time you want to process the data. Please let us know if you have any other questions.
Hello Denzil,
I just wanted to let you know that the new version of Combine Sheets we have just released can create links to source data so that your resulting table gets updated dynamically. You'll just need to choose the "Use a formula to combine sheets" option and your sheets will be joined using a formula that'll help you keep them up-to-date.
For more details, please look through the updated manual above or reach us at support@ablebits.com. Thank you!
Paola Forero says:
March 5, 2019 at 4:40 am
Hi.
- When I merge several sheets, can they be organized by date?
- My sheets are connected to forms that continuously have new registrants, does the merge in only time or can it be permanent.
' I´d like to consolidate several sheets into one master sheet, and then complement the information in each row. Can that information be retrieved to the originals sheets?
Thank you!
Hi Paola,
Thank you for your questions.
- The add-on doesn't organize data by sheet dates; it considers only the order in which sheets appear in the add-on tree view. Thus, to solve the task, first, you need to rename and organize your sheets and then run the tool to combine them.
- When you run the add-on, it combines only the data currently presented on the sheets. Since the tool cannot be run automatically, you'll need to run it manually each time you want to have all new entries added as well.
- As for your last question, with "complementing the information in each row", I'm afraid it's not entirely clear. I kindly ask you to share an example spreadsheets with us (support@4-bits.com) with your source data and the result you expect to get. We'll look into the task and see if our software can help.
Thank you.
Jack says:
December 18, 2018 at 6:02 pm
Is there a way to refresh the data?
Thank you for your question, Jack.
I'm afraid there's no way to refresh the data automatically at the moment. You need to start the add-on each time you want to combine sheets.
However, since this is a common request, we are considering supporting triggers in the future.
I can't give you any timing yet but I can contact you back when/if the functionality is supported.
Please let me know if you have any other question.
Chau says:
August 27, 2019 at 4:01 am
Hi Natalia,
Is the automatic functions supported now?
Hi Chau,
I'm afraid we haven't come up with a way to automate the process for Combine Sheets yet.
Please accept my apologies for any inconvenience.
Hi Chau,
I'd like to update you on your request and tell you that we've released a new version of our Combine Sheets add-on. Now you may join your data using a formula that will be connected to your original files. Hence, if you apply changes to your source sheets, they will be automatically reflected in the resulting table.
Check out the updated instructions above, I believe you'll find them helpful.
Hello Jack,
I am happy to inform you that we've just released an updated version of our Combine Sheets add-on with a new way to combine data – using a formula. The formula is always connected to your original files. This way, all changes made in the source sheets will be automatically reflected in the resulting table. We believe this will solve the problem of the add-on not being able to pull data automatically.
BTW, we've updated the manual above, check it out ;)
khalid says:
December 14, 2018 at 10:16 am
Hi
i would like to know whether if i buy the Sheet Combine addon for my company will it be applied for my entire Company domain or one user
please update
Hi, Khalid,
our subscription is account-based.
If the product is going to be used under different accounts at the same time, you need to have several subscriptions, so that there is one for each account.
Please email us to support@ablebits.com if you have any other licensing questions.
khalid says:
December 21, 2018 at 10:17 am
Thank you for your reply.
Is it transferable to another account may be after 3 month later if i need to give it to my other staff.
please send me a reply onto my email.
Khalid, I answered your question in email as requested.
Please check your Spam folder if you don't see in your Inbox.
Thank you.
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!
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.
