In this article:

How to Transpose Data in Google Sheets (Best way!)

Transpose Data with Paste Special

Google has included advanced paste functions in Google Sheets that you can access through right-clicking on a cell in the target sheet. One of these functions include pasting data transposed to the target sheet. Here are the steps:

Step 1: Copy the data (you can right-click or use keyboard shortcut Ctrl+C)

Step 2: Right click on the sheet where you want to paste the data

Step 3: Select Paste special

Step 4: Select Transposed

You can check the example screencap below to see it in action:

GIF

Transpose with Google Sheets TRANSPOSE function

What if the sheet containing the target data in Google Sheets is regularly updated? Using the Paste special method will not let you automatically update the data; you have to manually copy the data from the source sheet and paste it through Paste special to the target sheet. 

If the selected data range is automatically updated, you can use the TRANSPOSE formula. 

If you use this formula, Google will automatically update the data whenever a change occurs in the source sheet. 

TRANSPOSE Function Syntax

The syntax of TRANSPOSE is straightforward:

=TRANSPOSE(range)

Where range is the range of data that you want to transpose. 

Using the TRANSPOSE function

Using the function

FAQs

Transpose Every n rows

The TRANSPOSE of Google Sheets does not have enough functionality to help you modify how the data is transposed. If you want to convert a long column to a set of rows with n cells each, you can use the following formula:

=INDEX(column_range,ROW(first_cell)*N-N+COLUMN(first_cell))

Where column_range specifies the column you want to convert, first_cell the first cell in the column, and N is the number of cells per row.  You should insert dollar signs to the column name in the column_range. You can see it in our example below, where we transpose column A every 3 rows:

=INDEX($A:$A,ROW(A1)*3-3+COLUMN(A1))

The example is shown below:

The array is not automatically generated; you have to drag the cell containing formula to occupy the adjacent cells and display the data:

GIF

Transpose from another sheet

If you want to transpose the data from another sheet, you just need to add the sheet name to the range. For example, if you want to transpose A5:E7 from Sheet2, then the range will be

Sheet2!A5:E7

You can see this in action in the example below:

Conclusion

Google Sheets has two ways to transpose the data: via the Paste special and the formula TRANSPOSE. The Paste special is portable and straightforward to use, while TRANSPOSE formula allows you to automatically update the data without manually copying and pasting the new data. If the data sheet is regularly updated, you can use the TRANSPOSE formula to get the data transposed when loaded to the target cells. 

We hope this article has helped you and given you a better understanding of how to transpose data in Google Sheets. You might also like our articles on how to use the Google Sheets ISBLANK function and how to concatenate in Google Sheets.

To optimize your workflow, we recommend reading our guide on how to copy our free contact list template for Google Sheets and trying our software to help you track renewals.

Automate everything you track in spreadsheets with Lido
Learn more

Level up your Google Sheets skills with our free Google Sheets automation guide

Wasting too much time doing things manually in spreadsheets? Want to spend more time doing what you love? Our 100% free, 27-page Google Sheets automation guide is full of new tips and tricks that will save you time and money!