How To Transpose In Google Sheets
Sometimes when you’re working in Google Sheets, you’ll find you have data stored in a column when you need it to be in a row, or vice versa.
Manually reorganizing the data would be time-consuming and error-prone, but fortunately Google Sheets has several built-in features that allow you to convert columns into rows, or rows into columns.
This process is called transposing and can be easily done in two ways in Google Sheets.
Transposing Using the Paste Special
If you only need to transpose your data once and don’t need to keep the data in the original orientation, you can use the Paste Special option
Highlight the range you want to transpose and hit Ctrl + C on Windows or Command + C on Mac to copy the range
Click on the cell you want to paste the transposed data into
Open the edit menu, hover over the Paste Special submenu to expand it, and click on the last option: Paste Transpose
Your transposed data will appear, starting in the selected cell
Transposing Using Formulas
If you’re going to be frequently updating the data in your spreadsheet from an external source and don’t want to have to repeat the above process every time, you can also use the TRANSPOSE() formula to create a transposed copy of your data that will update whenever the original column/row updates.
Double click on the first cell you want the transposed data to appear in to enter edit mode and type the following formula: “=TRANSPOSE(A2:A)”, where the reference A2:A is the reference to the range you want to transpose
Hit enter to finish the formula, and your transposed data will appear starting in the cell you typed the formula in.
Note that TRANSPOSE() is an array formula, so it will expand to fill as many cells as needed to properly display your data.
If any of the required cells already contain data, the TRANSPOSE() formula will give an error, so be sure you have enough empty cells if you’re using this method
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to transpose in Google Sheets. Want more? Check out all the Google Sheets Tutorials.