We are pretty familiar to transpose multiple rows into columns, but transposing to one column is a bit different and difficult. We will show 3 easy methods to transpose multiple rows into one column using ** ****FLATTEN****, ****INDEX****, ****TRANSPOSE**, and other functions in Google Sheets with clear images and steps.

**A Sample of Practice Spreadsheet**

You can download Google Sheets from here and practice very quickly.

**3 Quick Tricks to Transpose Multiple Rows into One Column in ****Google Sheets**

First, let’s get introduced to our dataset. Here we can see the names and scores of some people. Now we will transpose multiple rows into one column by using different functions.

**1. Apply FLATTEN Function in Google Sheets to Transpose Multiple Rows into One Column**

The first method uses the **FLATTEN** function to transpose two rows into one single column. This method is very easy because it uses only a simple function.

**Steps:**

- Type the following formula in
**Cell B8**–

`=FLATTEN(C4:G5)`

- Hit
**Enter**button to get the output into a single column.

**2. Transpose Multiple Rows into One Column Using Combination of INDEX, INT, ROW, COLUMNS, and MOD Functions**

In this second method, we will use a combination of different functions named **INDEX**, **INT**, **ROW**, **COLUMNS**, and **MOD** Functions. This method is lengthy because of using so many functions but is useful in some particular cases.

**Steps:**

- Write the following formula in
**Cell B8**–

`= INDEX($C$4:$G$5,1+INT((ROW(A1)-1)/COLUMNS($C$4:$G$5)),MOD(ROW(A1)-1+ COLUMNS($C$4:$G$5),COLUMNS($C$4:$G$5))+1)`

- Press
**Enter**to get the result.

**Formula Breakdown:**

**COLUMNS($C$4:$G$5), COLUMNS($C$4:$G$5), COLUMNS($C$4:$G$5)**

Firstly, it gives the no of columns in the dataset. Here in our dataset, there are total 5 columns and this formula includes them in the calculation process.

**ROW(A1)**

Secondly, it gives the row number of **Cell A1** in our dataset.

**MOD(ROW(A1)-1+COLUMNS($C$4:$G$5),COLUMNS($C$4:$G$5))**

Afterward, the above formula calculates the 5 columns and 2 rows of our dataset.

**INT((ROW(A1)-1)/COLUMNS($C$4:$G$5)),MOD(ROW(A1)-1+COLUMNS($C$4:$G$5), COLUMNS($C$4:$G$5))**

Then this formula gives the nearest integer value of the numbers present in our sheets.

**INDEX($C$4:$G$5,1+INT((ROW(A1)-1)/COLUMNS($C$4:$G$5)),MOD(ROW(A1)-1+COLUMNS($C$4:$G$5),COLUMNS($C$4:$G$5))+1)**

Finally, the **INDEX** function will return the first value and put that in **Cell B8**.

- Then apply the
**Fill****Handle**tool to use the formula in all columns.

- You will get the following output.

**3. Combine TRANSPOSE, SPLIT, TEXTJOIN, FILTER, ISEVEN, ISODD, and ROW Functions to Get Results in Single Column**

Sometimes we need results for only even rows or odd rows, not all rows. Then we can apply this method. In this method, we will assign the combination of **TRANSPOSE**, **SPLIT**, **TEXTJOIN**, **FILTER**, **ISEVEN**, **ISODD**, and **ROW** functions to obtain results. The only difference is that we will use the **ISEVEN **function to get output for even rows and the **ISODD **function for odd rows. So we’ll have to apply the formula two times.

**Steps:**

- Type the following formula in
**Cell B8**–

`=TRANSPOSE(SPLIT(TEXTJOIN(",", 1, FILTER($C$4:$G$5, ISEVEN(ROW($C$4:$G$5)))),","))`

- Push
**Enter**button to get the result for even rows.

- Again, type the following formula in
**Cell B13**–

`=TRANSPOSE(SPLIT(TEXTJOIN(",", 1, FILTER($C$4:$G$5, ISEVEN(ROW($C$4:$G$5)))),","))`

- Hit
**Enter**button to get the result for odd rows.

**Formula Breakdown:**

**ISEVEN(ROW($C$4:$G$5))**

At first, this formula collects the information from even rows. Like in this case we have Names in **Row 4** which is even.

**ISODD(ROW($C$4:$G$5))**

This formula works like the above-mentioned formula instead it works for odd rows. We have Score in **Row 5** which is odd.

**FILTER($C$4:$G$5, ISEVEN(ROW($C$4:$G$5)))**

Then this formula filters out the even or odd rows based on given conditions. Here it will only filter the even row which is **Row 4**.

**TEXTJOIN(“,”, 1, FILTER($C$4:$G$5, ISEVEN(ROW($C$4:$G$5))))**

Next, this formula will join the values of the specified rows likewise here is **Row 4**.

**SPLIT(TEXTJOIN(“,”, 1, FILTER($C$4:$G$5, ISEVEN(ROW($C$4:$G$5)))),”,”)**

Afterward, the above formula will divide each joining text of **Row 4** to put them into separate cells of one single column, **Column B**.

**TRANSPOSE(SPLIT(TEXTJOIN(“,”, 1, FILTER($C$4:$G$5, ISEVEN(ROW($C$4:$G$5)))),”,”))**

Finally, it transposes the given rows, **Row 4** and **Row 5** into one single column, **Column B**.

**How to Transpose Every n Row in Google Sheets**

Now we will look into a different case where we need results for every n row in Google Sheets. We will apply a combination of **INDEX**, **ROW,** and **COLUMN** functions to get the desired output.

Here is our dataset where we have names in **Column B** and scores in **Column C.** We want to get the names in **Column B** of every **2nd** row.

**Steps:**

- Write the following formula in
**Cell B12**–

`=INDEX($B$4:$B$9,ROW(B1)*2-2+COLUMN(B1))`

- Press
**Enter**to get the result.

**Formula Breakdown: **

**ROW(B1)**

Firstly the formula gives the row number of **Cell B1**.

**COLUMN(B1)**

Secondly, it offers the column number of **Cell B1**.

**INDEX($B$4:$B$9,ROW(B1)*2-2+COLUMN(B1))**

Finally, the above formula shows the desired value of every **2nd** row of **Column** **B**.

- Then apply the
**Fill****Handle**tool to apply the formula in all columns.

- You will get the following result. Moreover, you will see that we get the names from every
**2nd**row of**Column B.**

**How to Transpose Row to Column in Google Sheets**

We can also transpose row to column in google sheets very quickly by **Paste Special** menu and **TRANSPOSE** function. This method is useful when we have to get the output quickly and we do not need results in just one column.

**1. Use Paste Special**

**Paste special** is a very distinguished feature in Google Sheets. We can easily transpose any row to column or vice versa by this method.

**Steps:**

- Select all the cells you want to copy and press
**Ctrl+C.** **Right-click**on the selected cells and go to the**Paste special**menu and select**Transposed.**

- You will get the following result.

**2. Apply the TRANSPOSE Function**

We can also use the **TRANSPOSE** function to transpose any row to column. This function is very easy to remember.

**Steps:**

- Type the following formula in
**Cell B8**–

`=TRANSPOSE(C4:G5)`

- Push
**Enter**button to get the result.

**What to Do When Paste Transpose Is Not Working in Google Sheets**

**TRANSPOSE** function does not work when there is no space where the value would take place. Like in the following case we have a value in **Cell B11.**

Applying the **TRANSPOSE** function in **Cell B8** gives an error message. From the message we can see that it is telling us, we have a value in **Cell B11**.

When we remove the value from **Cell B11 **then the **TRANSPOSE** function will work smoothly.

**Conclusion**

That’s all for now. Thank you for reading this article. In this article, I try to discuss different ways of transposing in Google sheets but emphasize transposing multiple rows into a single column. If you have any queries please leave a comment in the comment section. You will also find different articles related to google sheets on our **officewheel.com****.** Visit the site and explore more.