Sometimes you’ll have multiple separate ranges or columns or even entire sheets that you’ll want to “stack” on top of each other (i.e. append one or more to another) so that you end up with a single huge master range. Here’s how you achieve this.
Believe it or not, appending one or more ranges to another range is actually ridiculously easy to do.
Before we get into this though, there’s one caveat. Because you’re basically taking multiple tables and turning them into a single table of data (one where each appended table just adds rows to this new “master” table), you’ll generally want all of your ranges to have exactly the same structure. In other words, the ranges should all have the same number of columns, all the columns in one range should contain the same type of data as the corresponding columns in all other ranges, and the columns should be in exactly the same order.
| id | first_name | last_name |
| 1 | John | Smith |
So, if one range looks like the above, you’d want all other ranges to have the same columns in the same order with the same type of data.
Now, without further ado, here’s how you get this done.
={Sheet1!A:C;Sheet2!A:C}One thing you’ll notice…
If you have any empty rows in Sheet1, you end up with a bunch of empty rows between each appended range.
I like to solve this problem with the QUERY function.
=QUERY({Sheet1!A:C;Sheet2!A:C}, "SELECT * WHERE NOT Col2 = ''")The QUERY function uses SQL-like statements to effectively query a range of data in Google Sheets as though it were a table in a database. The query above gets all columns where the second column isn’t empty.
This gets rid of all the empty rows, but…
The header row from each range has been appended in addition to the data. So, you’ve got the header row multiple times. Obviously, you don’t want that.
To get rid of all those pesky additional header rows, just change all but the first range in your formula from A:C to A2:C.
Now we’re in business. Outstanding!
Here’s an example of this in action.
Your example is very helpful!! Thank you for posting. Is there a way to modify this query to include multiple selections on the same sheet (ie A:C, J:L, P:R) and have each group append to previous to have one long list?
Yes, this is absolutely possible. Although my examples used multiple sheets, the method applies equally to ranges on the same sheet.
={A:C; J:L; P;R}Hi.
When I use Col2 in the query, it says that there is no column called Col2, how to fix that please?
Happy to help you out. Can you share your sheet?
Thanks for the simple explanation Dave. I used a formula similar to this – =QUERY({Sheet1!A:C;Sheet2!A:C}, “SELECT * WHERE NOT Col2 = ””) in which 5 large arrays are combined.
The problem is that some of these arrays are empty some times. When they are empty, I get an error “In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.”
Is there something that can be done in such cases?
Himanshu, I’m happy to help you out. Can you share an example sheet showing the error so I can see how you’ve got it set up?
Thank you Dave! Sure, I can share sample data. Here’s the example sheet – https://docs.google.com/spreadsheets/d/1YudaiTz1CuwCyA8mHC1-CrJxvHUMU9UoT4zXvdqjZpY/edit?usp=sharing
The error can be seen in Sheet1.
I have also prepared a ‘Notes’ tab explaining the problem background.
Got it. One “solution” (i.e. cheat) would be to create a “virtual” row of empty cells by creating an array in case of an error.
Notice that I’ve wrapped each outer QUERY in an IFERROR function. If a query returns an error (e.g. in the case of no rows of data), we replace the error with an array of empty values, with the number of empty values corresponding to the number of columns that would otherwise have been output by the query, in this case seven:
Not the most elegant solution. But give it a try and see if it gets the job done.
This is cool Dave. Thanks a lot for your help! I’ll use this.
In fact, I had come close to this solution before I gave up; i hadn’t used the curly brackets.
If there a way to place the multi-range target (using the curly braces) in a cell and use it indirectly? INDIRECT does not seem to work, nor does using a named range.
Happy to help you out, Eric. Can you share the sheet you’re working on or some sample data, the formula you’re working on, and the end result you’d like?
Can you please help me! I have a very wide data set of form responses. The form consist of 8 sections, not all of which are completed depending on answers. Every section has the same questions so I would like to stack each range on top of each other in a different sheet.
I’ve successfully been able to stack all of the data using multiple querries, but I cannot for the life of me figure out how to append the date to each one. Please please please look at my attached spreadsheet. All of the data is in the form responses tab — Each section that I would like stacked on top of eachother is colored. The desired outcome sheet shows what I’m trying to accomplish.
I might need to do Unfilter formulas but have no idea.
Any help I could get would be amazing. Thank you!!
https://docs.google.com/spreadsheets/d/1iSbzRQGjTsknMoTl1AQNHWCjY2PppVqJEYeZOBWi3wI/edit?usp=sharing
Basically Im trying to get all of these ranges stacked, where they arent empty
{’Form Responses 1’! F:K;
’Form Responses 1’! M:R;
’Form Responses 1’! T:Y;
’Form Responses 1’! AA:FF;
’Form Responses 1’! AH:AM;
’Form Responses 1’! AO:AT;
’Form Responses 1’! AV:BA;
’Form Responses 1’! BC:BH}
You can filter each range within your array. Because some of your ranges are empty, you’ll need to use the trick I talked about here. Because that trick inserts a row of blank cells whenever your range is empty, you’ll want to wrap the whole thing in a QUERY that returns all rows where the first column isn’t empty.
I’m a little confused by your dates. Did all records in a given row occur on the same date?
If it’s the case that each date in column C applies to each record in the same row, then this may be what you’re looking for:
Correct, each date in column C applies to each record in the same row. The Timestamp in column A is automatically generated by the Google Form when responses are submitted, so I’m not taking that into account. When I use this formula it gives me “formula parse error” . I will look closely to see if I can find a typo on my end. Thank you so much for the fast replies!
It works! Thank you Dave, you’re a life saver.
Most welcome
Is it possible to include Column B (employee Name) and append that to the returned data set?
If understand you correctly, you’re looking to add column B (employee name) to each row just as column C (date) was added?
Correct
The formula in my last comment should get the job done
Thanks again!
You’re welcome
Thank you Dave!
I had used QUERY() before, but not the {list of ranges} syntax. So I began with a QUERY(single-range, “select …”) and converted it to a list of ranges by wrapping the range in curly brackets.
I eventually figured out something that you showed clearly in your example, but it seemed non-intuitive.
When the “data” parameter is a single range, the column IDs in the QUERY are the column references: A, B, C, … Z, AA, AB, etc.
When “data” parameter is a list of ranges in curlies, the column IDs in the QUERY are Col1, Col2, Col3, etc.
I had assumed that your column IDs “Col1” etc. were just examples, but they are the literal column IDs that you need to use.
You bring up a good point, Don. I should probably write a post clearly explaining that nuance. Glad you were able to figure it out.
You’re welcome Dave. I noticed one more nuance.
If the QUERY has an ORDER BY clause, the results will be sorted within each range, not all rows sorted in one sort. That is, it shows the first range, sorted; then the second range, sorted; and so on.
I’m using ORDER BY to spot-check for duplicates. Also, I’m validating a separate column using the data in this QUERY, with the option to show a drop-down list in each cell. I was hoping for the drop-down to be in alpha order, like a SQL UNION … ORDER BY. But c’est la vie; not a big deal for me.
Don, that’s odd. QUERY should sort the entire array when using ORDER BY. Here’s an example. I’d be happy to take a look at your formula to see if we can figure out why it’s not sorting the entire array for you.
Don, Dave,
A year late to the party here, but I had exactly the same problem.
I was trying to sort by numbers in Col1 across data from 9 different sheets. The issue was that the data had first been exported from another tool, then uploaded sheet-by-sheet into Google Sheets.
In short, the numbers in Col1 were not being recognised as numbers. They were like… text numbers or something. This prevented the query from viewing every cell in column 1 as the same data type and aggregating all the data into a single array.
I just selected the first column for each sheet, deselected the title cell A1, and used Ctrl+Shift+1 to force the format to Number.
The aggregated query array updated itself accordingly.
Hope this helps anyone else stuck with this – if your data has been imported (and not entered manually), make sure you’re working with a native Sheets datatype that can be sorted and not plain text!
I would love help with my formula! It too is returning an Array_Literal error. Sample Data https://docs.google.com/spreadsheets/d/1qzgxV_qOnBuPYV957JZikkxSOjXfZrMpCtHIy7_TGQg/edit?usp=sharing
Formula is in Master List A4: =query({‘Pre-K3′!A1:G;’Pre-K4′!A2:G;JK!A2:G;K!A2:G;’Grade 01′!A2:G;’Grade 02′!A2:G;’Grade 03′!A2:G;’Grade 04′!A2:G;’Grade 05′!A2:G;’Grade 06′!A2:G;’Grade 07′!A2:G;’Grade 08′!A2:G;’Grade 09′!A2:G;’Grade 10′!A2:G;’Grade 11’!A2:G},”Select * Where 1=1 “&IF(A2=”All Grades”,””,” And D = “””&A2&””” “)&IF(B2=”All Rankings”,””,” AND B = “””&B2&””” “),1)
Hey Adam. This is a pretty simple fix. In this particular example, you’ve got ranges that don’t actually exist (e.g. ‘Grade 01’!A2:G). When creating arrays using the curly brackets method, each range must have values. (I’ve created a copy of your “Master List” sheet. In that sheet, I’ve removed the references to the sheets that don’t exist.)
A second thing to watch out for is the number of columns in each range within your array. Sheet Pre-K3 has a column “G”, but the other sheets in your array don’t. All ranges in your array must have the same number of columns. (I’ve added the necessary column G to each of the respective sheets.)
The last thing I see is your query. When using an array that you’ve created using the curly brackets method, you’ll need to reference the columns in your array by column number (e.g. Col2) rather than column letter (e.g. B). This is because your array doesn’t actually exist in any sheet. It sort of exists “virtually” inside your formula. (In the copy of “Master List”, I’ve replaced the column references with the respective column numbers in your query.)
Hi Dave great content as usual!
I have a problem with this formula. When I try combine two column:
column 1
A B C
2020-11-03 John 105
2020-11-04 Mary 104
2020-11-05 Greg 105
column 2
E F G
2020-11-03 Susy 99
2020-11-04 Smith 123
2020-11-05 Jose 104
Using this formula:
=QUERY({A2:C;E2:G}, “select * where Col1 is not null”)
The result are below:
A B C
2020-11-03 John 105
2020-11-04 Mary 104
2020-11-05 Greg 105
Susy 99
Smith 123
Jose 104
Why the formula remove the same date? any solution for this will be helpful
Fatan, I’d be happy to help you out. It’s possible you’ve got two different data types in your date columns or something along those lines. QUERY is very particular about data types. For example, if in the first range the dates are “text” and in the second they’re a date value, QUERY will return null for the rows in the second range. In other words, QUERY expects all rows in a given column to be of the same data type. If possible, please share a copy of your sheet so I can take a look.
Yes I think that was the issue. Just resolved it yesterday when I try using ={FILTER(A2:C,A2:A “”);FILTER(E2:G,E2:E “”)} function instead. Anyway thank you for your response. Cheers!
I’m trying count multiple ranges of columns. I have tried both of the formulas below…each time they return “2” …not “4”. Any help on how to resolve this?
=COLUMNS({Testing!A2:B2;Testing!D2:E2})
=COLUMNS({A2:B2;D2:E2})
Hi Michael. Sorry I missed your comment. It sounds to me like you’re wanting your two sets of ranges to be laid out horizontally as columns. So, the combination of “Testing!A2:B2” (2 columns) plus “Testing!D2:E2” (2 columns) would being combined to create a four-column array.
The issue here is that there are two different ways that an array can be formed. Using the semi-colon (;) stacks the two ranges vertically, adding rows to the array. What you’re probably looking for is the comma (,) which combines the two ranges horizontally, adding columns to the array.
In other words, this is probably what you’re looking for:
=COLUMNS({Testing!A2:B2,Testing!D2:E2})Is there a way to “automate” this to continue adding columns? Or some such similar feature?
Ex: =MAX(COUNTIF(A1:A15, “*Gummi*”), COUNTIF(B1:B15, “*Gummi*”)) continues in the next column into
=MAX(COUNTIF(A1:A15, “*Gummi*”), COUNTIF(B1:B15, “*Gummi*”), COUNTIF(C1:C15, “*Gummi*”))
Ranges will be replaced with dynamic ranges, not sure if that would affect it
=INDIRECT(“B” & MATCH(FALSE,INDEX(F3:F=””),0) + 1)
HELP PLEASE! I am trying t combine data from three sheets and put them into one big Master. For the life of me I cannot figure out how I do it.
Sheet#1 I want to grab the data from A2:I300 if D2:D300=”8150-5800-8110″
Sheet#2 I want to grab the data from A2:I300 if D2:D300=”8150-5800-8110″
Sheet#3 I want to grab the data from A2:I300 if D2:D300=”8150-5800-8110″
Any Help that you can offer would be GREATLY Appreciated!
Hi Kelly. This post shows how to combine the data from those sheets into one big master sheet.
={Sheet1!A1:I300;Sheet2!A2:I300;Sheet3!A2:I300}It also shows how, using QUERY, you can reduce the data to just that which matches certain criteria.
You want to retrieve those rows where the value of column D is 8150-5800-8110. So, you want your SELECT statement to express this.
SELECT * WHERE Col4 = '8150-5800-8110'This SQL statement says, “Get all rows and columns in the range where column 4 (i.e. column D) is equal to 8150-5800-8110.
Your final formula would look like this:
=QUERY({Sheet1!A1:I300;Sheet2!A2:I300;Sheet3!A2:I300}, "SELECT * WHERE Col4 = '8150-5800-8110'", 1)Did this functionality get removed in an update? I am using curly brackets to combine data from multiple tabs within the same sheet, but it only shows the data from the first range.
I have ensured that all of the data is within the same columns on each tab, and each column contains the same type of data. I have approx. 10-15 tabs that need to be combined into a master list, but I started by trying to combine 2 of them in an attempt to avoid potential syntax errors.
My current formula:
={SHEET1!Q:U;SHEET2!Q:U}
Most likely, the data is all there. There’s probably just a lot of empty rows between the data from the first sheet and the data from the second.
Arrays do not automatically filter out empty rows.
={SHEET1!Q:U;SHEET2!Q:U}This formula retrieves the entire requested range from each of the specified ranges. In other words, “SHEET1!Q:U” retrieves the entire range of rows and columns corresponding to SHEET1!Q:U, whether there’s data there or not.
So, your formula is saying, “Grab all rows and columns from SHEET1!Q:U (including empty ones). Then append all rows and columns from SHEET2!Q:U (including empty ones).”
To retrieve only rows that contain data, use the FILTER function.
={FILTER(SHEET1!Q:U, SHEET1!Q:Q<>"");FILTER(SHEET2!Q:U, SHEET2!Q:Q<>"")}Thank you so much! This was the perfect solution to my problem.
Glad to hear it!
Hi Dave,
This was very helpful. I got it working and wanted to know if there is a way to append a column and insert the name of the *source sheet* respectively *for every row*.
This is my sheet.
https://docs.google.com/spreadsheets/d/1lgrKRHIJqYQXvW0dCU4G2XOqp2tY9VCD_T47Qg2pCPE/edit?usp=sharing
I’ve added ArrayFormula manually to each sheet. This works as a workaround. was wondering if there is a better solution. As this sheet is updated programmatically via a script
Glad you found the solution helpful.
Regarding the dates in column F on the “Main” sheet…
If you’re looking to have the date in column F reflect the date that’s represented in column A, you’ll probably want to modify slightly your formula in column F.
Try this instead:
={"date";ARRAYFORMULA(DATE(REGEXEXTRACT(TO_TEXT(FILTER(A2:A, A2:A<>"")),"[0-9]{4}$"), REGEXEXTRACT(TO_TEXT(FILTER(A2:A, A2:A<>"")),"^[^-/]{1,2}[-/]([^-/]{1,2})"), REGEXEXTRACT(TO_TEXT(FILTER(A2:A, A2:A<>"")),"^[^-/]{1,2}")))}