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:
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
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:
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.
Recommended articles
More resources
