Simple but useful function to transform a multi-column array into a single column
Listing updated:September 27, 2024
Works with:
10K+
Overview
Until now combining many columns into a single one was a cumbersome process. 
Here is a formula seen in the web to stack columns with native Google Sheets™ function:
=QUERY(ArrayFormula(SPLIT(TRANSPOSE(SPLIT(textjoin("-",TRUE,TRANSPOSE((A1:H1&","&A2:H5))),"-")),",")),"Select * where Col2<>''")

Not very comprehensive, isn't it?

Use =𝗦𝗧𝗔𝗖𝗞𝗔𝗥𝗥𝗔𝗬(𝘆𝗼𝘂𝗿_𝗮𝗿𝗿𝗮𝘆) and stack your columns or rows instantly wherever you need it in your spreadsheet. Without altering the initial array.


▶︎ 𝗖𝗛𝗘𝗖𝗞 𝗢𝗨𝗧 𝗢𝗨𝗥 𝗗𝗘𝗠𝗢
https://docs.google.com/spreadsheets/d/1YBMUfJPbePUffEddtpzL2nMstkHzCcf_tjbKu6LR5kI/

𝗠𝗢𝗥𝗘 𝗢𝗣𝗧𝗜𝗢𝗡𝗦...

✓ Use =STACKARRAY(your_array, TRUE) in order to remove all blank cells

✓ Stack row by row instead of column by column =STACKARRAY(ref_to_your_array, , TRUE)

✓ To merge an array with unique values only, use =UNIQUE(STACKARRAY(your_array))

✓ To sort the resulting column, use =SORT(STACKARRAY(your_array))


Find out more...
https://nodatanobusiness.com/add-ons/stackarray
Additional information
PricingFree of charge
Privacy policy
Terms of service
StackArray for Google Sheets™ will ask for the permissions shown below. Learn more
StackArray for Google Sheets™ will need access to your Google account
This will allow StackArray for Google Sheets™ to :
Display and run third-party web content in prompts and sidebars inside Google applications
See your primary Google Account email address
See your personal info, including any personal info you've made publicly available
Reviews
Language:
Sort by:
Google doesn't verify reviews or ratings. Learn more about reviews
Profile Picture
Jon
November 18, 2020
Spent over an hour trying to to this with native sheets functions. I couldn't find a solution that included blank cells, which I needed. This plugin saved me hours of trying to figure it out on my own. exactly what I needed, thank you.
Is this review helpful?
Yes
No
Profile Picture
Dr. Jamil Ahmed
May 11, 2021
Hello Matthieu, this is a great one. Can you provide something that is just opposite of this? I need something that can transform a lot of data from a single column into multiple columns. Thanks.
Is this review helpful?
Yes
No
Profile Picture
Nicholas DiMino
March 7, 2021
It worked great! Is there a way for the function return an empty cell between the vertically stacked data sets? I tried including the empty cells in the array, but the function deleted them.
Is this review helpful?
Yes
No
Profile Picture
Tricia DC
March 31, 2021
Thanks for the great creation it really is a shortcut to compile several columns to one. Question though, Is this applicable for separate columns or just columns beside each other?
Is this review helpful?
Yes
No
Profile Picture
Roman K (seeker1983)
March 18, 2021
Work really great splitting into multiple columns, what built-in FLATTEN doesn't support yet. Be sure to read Add-on Help is you see #NAME error.
Is this review helpful?
Yes
No
Profile Picture
A User of StackArray for Google Sheets™
April 23, 2020
Didn;t work at all - says there is no such function as "STACKARRAY()" when typing into the spreadsheet even after multiple refreshs (and the STACKARRAY is visible in the Add Ons menu)
Is this review helpful?
Yes
No
Profile Picture
Josie Chaney
October 24, 2020
amazingly easy and useful. one example of how google keeps constantly improving their tools!!!
Is this review helpful?
Yes
No
Profile Picture
Roberto Popolizio
November 20, 2020
Another Scam. You download it and nothing appears anywhere. Infant, Gogole Sheets can't even recognise the stackarray formula.
Is this review helpful?
Yes
No
Profile Picture
A User of StackArray for Google Sheets™
May 20, 2020
Superb Addon... Work as described, really helpful
Is this review helpful?
Yes
No
Profile Picture
Forrest Thiessen
April 13, 2020
Does *exactly* what I need, thanks! I sometimes need to combine multiple horizontal columns into a single vertical column, and STACKARRAY lets me do this formerly labor-intensive task in seconds.
Is this review helpful?
Yes
No
1-10 of 10
Search
Clear search
Close search
Google apps
Main menu