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:
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.
The syntax of TRANSPOSE is straightforward:
=TRANSPOSE(range)
Where range is the range of data that you want to transpose.
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:
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:
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.
