Google Sheets: Convert Multiple Rows into One Column

Other Questions Google Sheets
1

Hey folks, I’m trying to overhaul my Google Sheets setup and need some advice. I’m looking to build a custom function that transforms several rows—potentially thousands—into one continuous column. For example, if I have data structured as:

A1 | B1 | C1
A2 | B2 | C2
A3 | B3 | C3

I want to convert it into:

A1
B1
C1
A2
B2
C2
A3
B3
C3

I’m a bit stumped on how to achieve this using a script or a formula. Any ideas on how to make this work would be much appreciated!

2

While the custom function approach is solid, there’s actually a simpler way to achieve this using built-in Google Sheets formulas. You can use a combination of TRANSPOSE and FLATTEN functions to accomplish the same result without any scripting.

Here’s the formula you can use:

=FLATTEN(TRANSPOSE(A1:C1000))

This formula first transposes your data, effectively turning rows into columns, and then flattens the result into a single column. It’s incredibly efficient and works seamlessly with large datasets.

I’ve used this method extensively in my work with financial reports, where I often need to restructure data for analysis. It’s particularly useful when you’re dealing with varying numbers of columns or when you need to quickly pivot your data structure for different reporting needs.

3

I’ve actually tackled a similar challenge in my work with inventory management spreadsheets. Here’s a custom function that should do the trick:

function rowsToColumn(range) {
  var output = [];
  for (var i = 0; i < range.length; i++) {
    for (var j = 0; j < range[0].length; j++) {
      output.push([range[i][j]]);
    }
  }
  return output;
}

To use it, just enter ‘=rowsToColumn(A1:C1000)’ (adjust the range as needed) in the first cell of your output column. This function loops through each row and column of your input range, pushing each value into a new array as a single-cell row. It’s been a lifesaver for me when dealing with large datasets that need restructuring. Hope this helps!

4

hey there! i’ve dealt with this before. try using the QUERY function:

=QUERY(A1:C1000,“select * order by rownum”)

this’ll grab everything and stack it vertically. super easy and works like a charm for big data sets. no fancy scripts needed, just plug n play!