Google Spreadsheets Tip: Show Data from All Sheets in One

I’ve been working with spreadsheets a lot lately, and while anything Excel related is well documented and I’m more familiar with, Google spreadsheets does things differently. Today’s post is just a quick tip really, but thought I’d document because it took a long time for me to find the solution, plus I had to play around with it a bit.

Show Data from All Other Sheets on A Single Sheet

There are actually a couple of ways to do this, but many suggest using ArrayFormula.

Unfortunately, this solution only seems to work with basic text and numbers. Many have asked about formulae, links, etc., but with the solution below, it either seems to strip it or convert it to plain text. If anyone finds a better solution, please let me know and I’ll update the post.

VMerge Replacement

For some reason, Google Spreadsheet used to have a vmerge function and no longer has it anymore. So, if you just want to display all the columns from your sheets, use this:

`=ARRAYFORMULA({Sheetname!<first column>:<last column>,Sheetname2!<first column>:<last column>})`

Separate each sheet name with column range with a comma and repeat as necessary. For example, If on Sheet1, I want to display, first 3 columns of Sheet2 followed by first 2 columns of Sheet 3:

`=ARRAYFORMULA({Sheet2!A:C,Sheet3!A:B})`

Horizontal Merge

First off, you want to make sure that all your sheets have the same columns (name, order, possibly size).

In my case, I also had two other conditions I wanted to fulfill:
* key data isn’t blank
* sort by key data

As a result, I used filter and ISBLANK with NOT (not sure it’s the most efficient way, but it works). Assuming that the important ‘key data’ I have is in the first column, I would use this:

`=ArrayFormula(query({filter(‘Sheet 1’!A2:<last column letter>,NOT(ISBLANK(‘Sheet 1’!A2:A)));filter(‘Sheet 2’!A2:<last column letter>))},”order by <column name>”))`

You’ll notice everything starts at A2. That’s because your first row consists of column headers (which again, should be the same for all your sheets, including your compilation sheet).

For example, if I have two sheets, one called “BC” and another called “AB”, and there are 3 columns of data, then I might use this:

`=ArrayFormula(query({filter(BC!A2:C,NOT(ISBLANK(BC!A2:A)));filter(AB!A2:C,NOT(ISBLANK(AB!A2:A)))},”order by Col1″))`

Here’s a quick demo.

If you have more sheets, just keep adding the filter portion separated with a semicolon.

Hope that makes sense.

Pull Data from Multiple Google Sheets Files

Someone asked me whether I’ve ever tried pulling data from multiple Google Spreadsheets and I have not, but there is a useful Google Solutions tutorial that tells you how to do it. Props to the person who contacted me because they tried it and reported back that it works. The only thing that is inaccurate about the tutorial is that it has an outdated way to grabbing the key, which is now after the /d/ in the URL.

25 thoughts on “Google Spreadsheets Tip: Show Data from All Sheets in One”

    1. Sure, indirect would work to replace vmerge, but if I understand it correctly, you would have to do it for each sheet at the exact location. So if you have sheet1 at A and the range is A:C, then you would have the formula for sheet2 starting at D. If you ever change those ranges, it can be a hassle to keep track of where all those individual formulae should go (especially if you have 3+ sheets). Easier to use a single formula so you can easily edit and not worry about layout.

      If you find a better solution though, I’m all ears.

  2. Hi Cynthia, first off, thank you for this post. This is one of the only references to performing a horizontal merge I could find that isn’t extremely complicated.

    As your post is over a 8 months old now, I do not know if you are actively monitoring this, but worth a try to ask.

    I am using the following formula you recommended at the bottom of your first post:

    =ArrayFormula(query({filter(BC!A2:C,NOT(ISBLANK(BC!A2:A)));filter(AB!A2:C,NOT(ISBLANK(AB!A2:A)))},”order by Col1″))

    But “Order by Col1” is causing an unexpected outcome. For some reason the data being pulled and displayed in row 1 is actually a combination of 2 rows from sheet “BC” and “AB” combined to displayed in a single row (by 2 rows combining I mean the data in each individual cell for the entire 2 rows is combining). All other rows below row 1 in the new sheet are displayed (separately) and sorted correctly.

    I can’t seem to find the cause or a solution. My best guess is one of the formulas in one of the columns are throwing things off. Any insight is much appreciated.

  4. Hi Cynthia!

    Thanks a lot for this, it helped me a lot!

    Quick question for you, if you have the time… My issue is that the two sheets I want to merge contain texts and not only numbers…

    City Currency Phone
    London £ +44
    NYC $ +1

    I tried copying your document and changing the number (replacing them with text) but then everything goes blank…

    Do you have an idea how I can solve this? 🙂

    Thanks!

    1. I’m not sure what you mean by changing the number. Remember that the formula doesn’t change, just your data. Each data column should also be the same type of data. I’ve updated the demo so that it shows a mix of numbers and text with your examples mixed in as well. Hope that helps.

      Otherwise, it would really help if you can post a link to an example document to show exactly what’s going wrong.

      1. Cynthia, thank you so much for your help!! The new version of your document matches perfectly my need and I was able to present a complete document to my boss! Hope I’ll be able to help you back some day!

  5. Hi –
    I have made a copy of the spreadsheet from the link above, this is the most recent version of the ArrayFormula example but what I am experiencing is that if text is entered in the C column of either BC or AB and have either text or numbers in the A and B columns, the C column of Sheet3 returns blank; the array does not give text in the C column.
    Any thoughts on this? I realize that the ArrayFormula shold only return data but for some reason text is not showing up when I try to use this file.

    1. It seems like whatever type of data is processed first is what the arrayformula expects for the rest of that column of data, so it will make it blank if it considers it “invalid” data based on the type it expects. That’s my best guess anyway.

      Mostly likely it’s because, as you say, you shouldn’t be mixing text and numbers as data in the same column.

      The only solution I can find is to make the whole column “Plain text” so that the numbers are not formatted as numbers. Of course, that means you can’t pull the numbers for calculation purposes.

  6. Is there a way not to hardcode the name of the Sheets; say I have yet another sheet (let’s call it a reference sheet) with the sheet names (or maybe there is a way to access them via formulas) and I want to get the names from that sheet, say A2:A5 have the names hence A1 = Sheet1, A2=Sheet2 and so on, maybe using yet another ArrayFormula.

    I’m trying to find a way to sum the same cell value on every sheet without hardcoding the sheets.

  7. hi!

    would like to ask if the script below would work?
    =arrayformula({query(SheetName1!$C$2:$AM,”select C, D, E, J, M, X, AM”,1), query(SheetName2!$C$2:$AM,”select C, D, E, J, M, X, AM”,1), query(SheetName3!$C$2:$AM,”select C, D, E, J, M, X, AM”,1)})

    Thanks!

    bags

    1. why not try it and see? but I don’t think spreadsheets supports the use of $ in cell references. I’m also not sure what happens if you try to fit multiple queries in a single arrayformula. I’m no expert, so I’d be interested to hear what you’re trying to do and what works for you.

  8. Pingback: Google Spreadsheets Tip: Show Data from All She...

    1. Hi Sebastian, The formula does refer to specific sheets by name, so you need to make sure that you either change the name of your sheets to match or change the formula to match the name of your sheets. Hope that helps.

  10. Hello, thanks for this tutorial, it worked perfectly 😀 The only problem I ran into for my uses is that column “A” on my sub-sheets are all Hyperlinks, and on the “Merged” Sheet all the links have been stripped away leaving only the the link text, any thoughts?

    1. unfortunately, since the data goes through various functions, it may be that it will only understand “numbers” or “text” (as in plain text). I haven’t tried using it with links, so sadly no ideas =( Sorry I couldn’t help more. Do let me know if you find a solution, I’d be happy to add the info to the post.

  11. Good day Ma’am Cynthia.

    I really need your help with this project,,,I need to re-organize all Gsheet I made into one master sheet.

    the reason why I made it separately in every section because it is handled by different managers and accountants

    the sheets are:

    SALES
    PURCHASE
    CASH / BANK TRANSFER

    every sheets has SAME NUMBER OF COLUMN

    i need to gather it using importrange (since it is in different sheets. the problem is It gives me error if I made a double importrange in one same column)

    i use =IMPORTRANGE(“1kXl-mLmCWfom7HWERbf3FlSkilR3HBzAd-IPaoSfH1E”,”BANK FLOW!A2:F”)

    This formula works but I need to mix all data of (Sales,Purchase,Bank transfer) in same column..since it has same number of columns and data needed.

    the purpose of this is to make an statement of account for each account that use in sales,purchase,and bank transfer

    Here is my MASTER DATA SHEET (https://docs.google.com/spreadsheets/d/1vmQZ7lM-wjG7OptpdqK8wwSJrNY3XVV7g5xiaeSVtg8/edit#gid=41957163)

    Here is my SALES (https://docs.google.com/spreadsheets/d/1KDxCzEzsTEiF5RH1U9yDiaf9ux2TzWi-tUUeGqCeOhs/edit#gid=0)

    Here is my PURCHASE(https://docs.google.com/spreadsheets/d/1imH0dHFmOf6DkV7a6sbNaLVLccUdvjH3hB8rf0s7Y30/edit#gid=0)

    Here is my CASH BANK TRANSFER (https://docs.google.com/spreadsheets/d/1kXl-mLmCWfom7HWERbf3FlSkilR3HBzAd-IPaoSfH1E/edit#gid=0)

    Hope you can help me Thank You so much.

    1. Unfortunately, I’ve never even used the importrange function as I’ve never tried working with data from multiple spreadsheets. It’s generally difficult to do and not recommended. Maybe see if someone has further documentation on importrange? Sorry I can’t help you more, but I don’t use google spreadsheets all that often.

  12. Hello, is there any way for this to also copy cell formulas? It works good with copying text/numeric data but in some cells im using =IMAGE() function and the image is not being copied over to accumulated sheet.

    1. there might be a way using a different formula, but unfortunately the solution I’ve presented here seems to only understand basic values (numbers, text). If you do find a solution, please let me know and I’d be happy to add the information to the post.

Comments are closed.