How to merge several Google sheets into one without copy-pasting

Every once in a while each Google Sheets user faces the inevitable: combining several sheets into one. Copy-pasting is tedious and time-consuming, so there has to be another way. And you're right - there are several ways, in fact. So get your tables ready and follow the steps from this article.

All the ways I describe can be used to process large tables. But to keep this guide as clear as possible, I'll keep my tables short and am going to cut down to a couple of sheets.

Reference cells in Google Sheets to pull data from another tab

The easiest way comes first. You can pull entire tables to one file by referencing cells with data from other sheets.

Note. This will do if you need to merge two or more sheets within one Google spreadsheet. To merge multiple Google spreadsheets into one, jump right to the next method.

So, my data is scattered all over different sheets: June, July, August. I'd like to pull data from July and August into June to have one table as a result:

  1. Find the first blank cell right after your table (the June sheet for me) and place the cursor there.
  2. Enter your first cell reference. The first table I want to retrieve starts from A2 in the July sheet. So I put:

    =July!A2

    Note. If there are spaces in your sheet name, you must wrap it in single quotes like this:

    ='July 2019'!A2

    This immediately replicates whatever lies in that cell:

    Enter your first cell reference below the table.

    Note. Use relative cell reference so it changes itself when copied to other cells. Otherwise, it will return incorrect data.
  3. Make sure the cell with the reference is selected and click on that little blue square at its bottom right corner. The mouse cursor will turn into a big black plus sign. Keep the mouse pressed and drag the cursor to as many columns to the right as you need to fill them with new records:

    Copy cell references over other columns.

  4. Select this entire new row, click that little blue square once again, hold and drag your mouse down - this time to fill entire rows with cell references and bring new data from another sheet:

    Bring data from another sheet with cell references.

Though this is probably the first way you may think of to pull data from another tab, it's not the most elegant and quick. Luckily, Google prepared other instruments specially for this purpose.

Copy the tabs into one spreadsheet

One of the standard ways is to copy the tabs of interest into the destination spreadsheet:

  1. Open the file that contains the sheet(s) you want to transfer.
  2. Right-click the first tab you need to export and choose Copy to > Existing spreadsheet:

    Copy the tab into an existing spreadsheet or a new spreadsheet.

  3. Next thing you'll see is the pop-up window inviting you to select the spreadsheet. Browse for it, click on it to highlight, and press Select when you're ready:

    Locate the spreadsheet to import data into.

  4. Once the sheet is copied, you'll get a corresponding confirmation message:

    A message confirming the sheet has been exported.

  5. You can either hit OK and continue with the current sheet or follow the link called Open spreadsheet. It will instantly get you to another spreadsheet with the first sheet already there:

    The tab is copied to another spreadsheet.

Export/import sheets

Another way to import data from multiple Google Sheets is to export each sheet first, and then import them all to a necessary file:

  1. Open the spreadsheet that contains the sheet you'd like to pull the data from.
  2. Make the sheet of interest active by selecting it.
  3. Go to File > Download > Comma-separated values (.csv, current sheet):

    Download the sheet as .csv

    The file will be downloaded to your computer.

  4. Then open another spreadsheet - the one you'd like to add the sheet to.
  5. This time, pick File > Import from the menu and go to the Upload tab in the Import file window:

    Upload the sheet you downloaded earlier.

    Hit Select a file from your device and find the sheet you've downloaded just now.

  6. Once the file is uploaded, you'll see a window with additional options for importing the sheet. To add the contents of that another sheet after your existing table, pick Append to current sheet:

    Adjust the options to import sheet.

    Tip. Among other settings, feel free to specify the separator and convert text to numbers, dates, and formulas.
  7. As a result, you'll get two sheets merged - one table under another:

    The second table has been appended to the first one.

    But since it is a .csv file you need to import, the second table remains formatted in a standard way. You will have to spend some time formatting it as you need.

Google Sheets functions to combine data from multiple spreadsheets

Of course, it wouldn't be Google if it didn't have functions to merge data in Google Sheets.

IMPORTRANGE to import data from multiple Google sheets

As the name of the function suggests, IMPORTRANGE imports data from multiple Google spreadsheets into one sheet.

Tip. The function helps Google Sheets pull data from another document as well as from other tabs from the same file.

Here's what the function requires:

=IMPORTRANGE(spreadsheet_url, range_string)
  • spreadsheet_url is nothing else than the link to the spreadsheet from where you need to pull the data. It must always be put between double-quotes.
  • range_string stands for those cells specifically that you need to bring to your current sheet.

And here's the pattern I follow to import data from multiple Google Sheets using IMPORTRANGE:

  1. Open the spreadsheet from which you want to pull the data.
    Note. Make sure you have at least viewing access to that file.
  2. Click the browser URL bar and copy the link to this file right till the hash sign (#):

    Copy the link to the spreadsheet of interest.

    Note. You'll need this URL even if you're going to combine sheets from the same file.
  3. Return to the spreadsheet where you want to add the info to, enter the IMPORTRANGE to where the borrowed table should appear, and insert the link as the first argument. Then separate it from the next part with a comma:

    Paste the URL into the formula.

    Note. Remember, the link should be surrounded by double quotes.
    Tip. Though Google says the function requires the whole URL, you can easily get by with a key - a part of the URL between /d/ and /edit:

    ...google.com/spreadsheets/d/1pNqFucLhKIf3PYEp5JhBsYb7jDdf6h1Tca9ar4Hynks/edit

  4. For the second part of the formula, type in the name of the sheet and the exact range that you want to pull. Confirm by pressing Enter.
    Note. Wrap the second argument in double quotes as well:

    =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1pNqFucLhKIf5PYEp2JhBsYb5jDdf6h1Tca9ar4Hynks/edit","May!A2:D5")

  5. Though the formula looks ready now, it will return the #REF error from the start. That's because the first time you're trying to pull data from some spreadsheet, IMPORTRANGE will ask for access to it. Once the permission is granted, you will easily import records from other sheets of that file.

    Click the cell with the error and press that blue Allow access prompt:

    Connect the sheets to combine them.

    Note. By allowing access, you let the Sheets know you don't mind any existing or potential collaborators on this spreadsheet accessing data from another file.
  6. Once the formula connects to that other sheet, it will import data from there:

    Import data from multiple Google Sheets with IMPORTRANGE.

    Tip. If the tables are rather big, just allow some time for the formula to pull all records.
    Note. The records returned by the function will be updated automatically if you change them in the original file.

Google Sheets QUERY to import ranges from multiple sheets

And thus, without haste, we've come to the QUERY function once again. :) It is so versatile that can be used in Google spreadsheets to combine data from multiple sheets as well.

So, I want to merge three different Google sheets: Winter 2019, Spring 2019, and Summer 2019. They contain the names of all employees who became best in their jobs in different months.

I go to the first sheet - Winter 2019 - and add my QUERY right under the existing table:

=QUERY({'Spring 2019'!A2:D7;'Summer 2019'!A2:D7},"select * where Col1 <>''")

Let's see what it all means:

  • {'Spring 2019'!A2:D7;'Summer 2019'!A2:D7} - are all the sheets and ranges I need to import.
    Note. The sheets should be written in between the curly brackets. If their names contain spaces, use single quotes to list the names.
    Tip. Separate the ranges with a semicolon to pull data from different tabs one under another. Use commas instead to have them imported side by side.
    Tip. Feel free to use such infinite ranges as A2:D.
  • select * where Col1 <>'' - I tell the formula to import all records (select *) only if cells in the first column of the tables (where Col1) are not blank (<>''). I use a pair of single quotes to indicate the non-blanks.

As a result, two tables from other sheets have been consolidated into one sheet one under another:

Google Sheets QUERY - import ranges from multiple sheets.

Tip. If you'd like to use Google Sheets QUERY to import ranges from multiple spreadsheets, you will have to implement IMPORTRANGE. Here's a formula to pull your data from other documents:

=QUERY({IMPORTRANGE("1pNqFucLhKIf5PYEp2JhBsYb5jDdf6h1Tca9ar4Hynks","Mar-Apr-May!A2:D6");IMPORTRANGE("1ie8-uTlIAzahJZHSlhMGLSW_xA6ZBqNmt1I0ADo4N4M","Jun-Jul-Aug!A2:D4")},"select * where Col1<>''")

Tip. I use the keys from URLs rather than entire links in this long-enough formula. If you're not sure what that is, please read here.

2 quickest ways to merge multiple Google sheets

If standard ways of Google spreadsheets to combine data from multiple sheets seem dull, and the functions scare you off, there's an easier approach.

Combine Sheets add-on

This first special add-on - Combine Sheets - was designed with a single purpose: import data from multiple Google sheets. It's clever enough to recognize the same columns in different sheets and bring data together accordingly if you need.

Combine Sheets working scheme.

All you are to do is:

  1. Select sheets or entire spreadsheets to merge and specify the ranges if necessary. The possibility to do a quick search in Drive makes this even faster.
  2. Choose how to pull the data: whether to join records from the same columns into one column; whether to keep the formatting; and if you need an extra line between different ranges to notice them right away.
  3. Decide where to place the merged table: new spreadsheet, new sheet, or in a location of your choice.

Here's a quick demonstration of how I combined my three small tables with the add-on:

Import data from multiple Google sheets into one with Combine Sheets.

Of course, your tables can be much bigger and you can merge lots of different sheets as long as the resulting spreadsheet doesn't exceed the 5M limit for cells.

Feel free to check out the help page for Combine Sheets.

Consolidate Sheets add-on

Consolidate Sheets is a relatively new addition to our add-ons. Its main difference from the aforementioned tool is the ability to add up data in columns in Google Sheets (or rows, or single cells, for that matter).

Consolidate Sheets also recognizes common headers in all the Google sheets to join, even if they are in the leftmost column and/or the first row. There's always an option to merge Google sheets and calculate cells based on their place in the tables.

Let me break it down into steps for you as well:

  1. Select sheets to consolidate. Import more files from Drive if necessary straight from the add-on.
  2. Pick the function to consolidate in Google Sheets.
  3. Choose the way to add up cells in Google Sheets: by labels (header labels, left column labels, or both) or position.
  4. Decide where to place the consolidated data: new spreadsheet, new sheet, or any specific location within the opened file.

Here's how the process looks:

Google Sheets: pull data from another document and calcualte at the same time.

I encourage you to not only visit the instructional page for Consolidate Sheets to get to know it better but also try it on your data. You'll see for yourself how much extra time you'll have after incorporating this tool to your daily work.

On this note, I'm going to finish this article. Hope these ways of pulling data from multiple different sheets into one will be of use. As always, looking forward to your comments!

You may also be interested in:

-->

19 Responses to "How to merge several Google sheets into one without copy-pasting"

  1. Jerry says:

    Hi,
    this seems pretty useful, but I'm looking for something slightly different. Perhaps you can provide info on how to do this?

    I want to have several spreadsheets, all accessible to ONE person to edit them. Then, I want one spreadsheet which combines all the data from the other spreadsheets. The trick is, that the amount of rows per spreadsheet can vary. So the ranges would be dynamic.

    Is it possible to do this, while getting a read-only table which contains ALL info, without white spaces?

    So if spreadsheet 1 gets more rows, they go in seamlessly into the read only, with the info from spreadsheet 2 going down a few lines?

    • Hi Jerry,

      I'm afraid there's no single option to get all of these at once.

      Q: I want to have several spreadsheets, all accessible to ONE person to edit them.
      A: You can share those spreadsheets and set the permissions - decide who can view, comment, or edit each file.
      What's more, you can protect separate sheets and ranges and make them read-only for certain collaborators.

      Q: Then, I want one spreadsheet which combines all the data from the other spreadsheets. The trick is, that the amount of rows per spreadsheet can vary. So the ranges would be dynamic.
      A: The IMPORTRANGE function will help you pull all current and future data from one file to another.
      Our tool also brings everything together but it doesn't work automatically. You'll need to run it each time you need to have combined data.

      If this is not exactly what you need, please try to describe the task in more detail.

  2. Alfredo says:

    This doesn't work
    Tip: Separate the ranges with a semicolon to pull data from different tabs one under another. Use commas instead to have them imported side by side.

    How should be the syntax to put a range on the side of the other? Replacing ";" for "," does not work.
    Could you give an example?

    • Hello Alfredo,

      {'Spring 2019'!A2:D7;'Summer 2019'!A2:D7} - here I used a semicolon to put ranges one under another.
      To put them one next to each other, it should be {'Spring 2019'!A2:D7,'Summer 2019'!A2:D7}

      If it's still doesn't work for you, perhaps, your locale requires different separators.

  3. Alfredo says:

    This works perfectly with a total of about 1700 records retrieved from the 4 data sources.
    But if I include a data source with a range of 12,000 rows, it returns # N / A
    Error
    The query has been completed with an empty result.

    I do not think such a large number of rows, especially because it is only 8 columns wide.
    Data sources are imports of other spreadsheets made using IMPORTRANGE (), since only the relevant columns are imported. Could that be why it is difficult to work with that many records?

    =QUERY({'Destinatarios Importados 1'!A2:H;'Destinatarios Importados 2'!A2:H;'Destinatarios Importados 3'!A2:H;'Destinatarios Importados 4'!A2:H};"select * where Col1 ''")

    • Alfredo,

      yes, IMPORTRANGE can take some time returning data, especially when you refer to 4 different ranges in one formula at the same time. If ranges don't load up, try to change the condition to the one below:
      "select * where (Col1 is not null)"

      If you're still unable to make it work, please consider sharing your file (with support@4-bits.com) along with your source data and the formula that doesn't work.
      Note. That email is for file sharing only. Please do not email there. Once you share the file, just reply to this comment.

      I'll look into it and do my best to help.

  4. Michelle says:

    Hi Natalia,
    Your instructions have been very helpful - so thank you in advance for being clear about how to do things. However, I'm stuck :) I'm pulling 3 unique workbooks into 1 master worksheet using the Query Importrange. Everything is working as it should but I have 4 questions:
    1. In the original file, if a cell is a merge (for example A1:A5) and each row has unique content, it doesn't pull this through to the new spreadsheet. All I get is the content in A1. The content in A2:A5 doesn't show up. Is there a work around?
    2. I need to add rows to each workbook but each time I do this, it throws off the new spreadsheet. I added 'dummy' rows to the original files and this works, but if the owner needs to add a new row, it's going to mess everything up. Is there a workaround to be able to add more rows without messing up the new spreadsheet?
    3. Formatting is a nightmare. I've done a lot of investigating and it seems there is no easy way around it. Can you please confirm or let me know if there's a trick to keep the original formatting? Also, when I add a row (as in question 2) and I can make it work, it pushes all of the content down but the formatting stays in place, so I have to reformat the whole thing again. Any work arounds?
    4. If a cell in the original file is blank, it doesn't show up in the new file. I added a space and then it shows up. I read your example above but I'm not following it. Can you please clarify? (select * where Col1 '' - I tell the formula to import all records (select *) only if cells in the first column of the tables (where Col1) are not blank (''). I use a pair of single quotes to indicate the non-blanks.)
    Thanks!

    • Hi Michelle,

      Thank you so much for your questions!

      1. I'm really sorry but I'm unable to reproduce this. QUERY IMPORTRANGE returns all merged cell with everything that lies in them. In case I misunderstood you, please provide a clear example of how your merged cell looks like before and after importing.
      2. If you need to add rows at the end of each table, try limiting the ranges in the formulas so that they do not include new rows.
        The other way would be to convert all formulas to values using a special tool. But in this case, any new info added to source sheets won't be added to your new spreadsheet.
      3. Unfortunately, there's no standard way to do that, I had to pre-format cells before pulling data with IMPORTRANGE. We provided this possibility in our Combine Sheets add-on though.
      4. In my example, I return non-blank cells only. In the formula, it's written like this <>''. <> is the operator for "not equal", and two single quotes '' mean "empty". Combine them together and you get not equal to empty, or equal to not blank. If you need all cells including blanks, just omit this. select * (meaning "select all") should be enough for you.

      You can look at the clauses used in QUERY closer in this recent blog post.

      Please let me know if some of the answers are still not clear. :)

  5. Nicole says:

    QUERY and IMPORTRANGE did what I was trying to do, except the data from the two sheets stays separate if I try to sort it. So if I want to sort "sheet1" and "sheet2" by "date", the data displays as dates for sheet1 in order and then dates for sheet2 in order. Is there a way to make them intermingle?

  6. Melanie says:

    How can I make sure that the notes or comments are also included if I'm using a =IMPORTANTRANGE formula?

  7. Karen says:

    Hi Natalia,
    I'm trying to use Importrange for the first time. I get the error "You don't have permissions to access that sheet." I have just created both sheets and own both of them. They are both shared with 1 person (as a result of having been created inside a folder I own and have shared with her). What am I missing? Thank you!
    =importrange("https://docs.google.com/spreadsheets/d/1QWnXBCOF9YKq5GoroET9kCCTy9_wnhKysY5c6R5AJ6g","List!A2:G")

  8. Kim says:

    Hi Natalia,
    Thanks for sharing this good tips.
    However, i would like to know if this is possible:
    I have one Workbook contains all the my clients' info. However there will be 5 different people in charge of the different clients according to the region. So this workbook has 6 different tabs: Master Sheet; P1; P2: P3; P4 ; P5.
    My question is:
    How can I auto-populate the data in Master Sheet tab from each sub tabs (tabs P1 - tabs P5)?
    I know how to work on the other way round (Master sheet into sub tabs).
    Can you help me with this please?
    Thank you! =)

    • Hi Kim,

      Sorry, I'm a bit confused by your question actually :) The ways of transferring data between sheets are the same, you just need to pick one based on your exact goal.
      Are you trying to paste all tables one under another? If so, I'm afraid you'll have to do that manually (even with the add-on you'll have to run it each time) since the ranges on single tabs may expand and overlap anything below when combined. If that's not what you mean, please try to be more specific, I'll do my best to suggest the solution.

      • Kim says:

        Hi Natalia,
        Sorry for the confusing you.
        I was trying to create a CRM via the google sheet.
        We have 5 people responsible for different region clients. I will name them P1 - P5.
        And in the google worksheet, we will have 1 Master Sheet tab (which has ALL clients information) and another 5 tabs for each person respectively.

        The question is:
        How do I auto-poupulate the data on the Master Sheet tab while I input in the individual tab?

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!