How can Google Sheets take a range of multiple columns and rows and stack or consolidate all the non-empty cells into one column? For this case, I want non-unique cells. Also, each cell in the range is a string that has commas and spaces so if needed, a special char will need to be as a delimiter.

There are several questions already that are about concatenating columns, but they are all concatenating the values of the columns and not the actual columns - something such as this sudo SQL statement such as SELECT * AS single_column or if the range was not dynamic an equivalent SQL could use union such as:

SELECT col_a AS single_colum FROM foo
SELECT col_b AS single_colum FROM foo
SELECT col_b  AS single_colum FROM foo
  • Please add a brief description of your search/research efforts as is suggested on How to Ask. Also add more details like if all the values are of the same type, if you want to use a value separator like a blank space or comma. It's also highly recommended to create a demo/sample spreadsheet, share it withe anyone with the link and include the link in the question. Aug 22, 2019 at 15:09
  • share a copy of your sheet with examplle of desired output
    – user0
    Aug 30, 2019 at 19:26

2 Answers 2


If you really need to handle dynamic references having a lot of columns, then consider to use Google Apps Script because while it could be possible to build formula to achieve the desired results it's very likely that the resulting formula will be very convoluted, hard to maintain an error prone. To learn about using Google Apps Script to extend Google Sheets please read https://developers.google.com/apps-script/guides/sheets


If you are able to handle the references in a case by case basis, try the array handling feature of Google Sheets. The following formula returns one column having the values of Columns A, B and C from Sheet1.

Example. Assuming that your values are on sheet called Sheet1, on another sheet add the following formula:


Tip: Remove the bottom blank rows from Sheet1, otherwise you will have a lot of blank rows between the last non blank and first blank values and the sheet having the above formula will have three times the number of rows of Sheet1.

In case that you can't to remove the blank rows you could filter them. Assuming that Column A, B an C have values of the same type you could use QUERY

=QUERY({Sheet1!A:A;Sheet1!B:B;Sheet1!C:C},"select Col1 where Col1 is not null")



  • Thank you Ruben, but it is a dynamic range, so I can not 'hard code' the columns.
    – Tom
    Aug 22, 2019 at 17:25
  • @Tom What do you mean by "dynamic range"? Aug 22, 2019 at 18:06
  • I will not know if the range is A:C or A:AZ etc - the size is dynamic.
    – Tom
    Aug 22, 2019 at 19:23
  • @Tom I edited my answer. Aug 22, 2019 at 22:40
  • to consolidate columns into one single column:


  • to consolidate rows into one single row:



  • to create truly dynamic range:

    =INDIRECT("Sheet2!A1:"&ADDRESS(ROWS(Sheet2!A:A), COLUMNS(Sheet2!1:1), 4))

  • combined:

     INDIRECT("Sheet1!A1:"&ADDRESS(ROWS(Sheet1!A:A), COLUMNS(Sheet1!1:1), 4))),,999^99))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.