In Google Sheets, if you ever need to flip the columns and rows of an array or a data range, TRANSPOSE Google Sheets function is the one to go with. It swaps — or transposes — the rows and columns, changing the orientation of your spreadsheet data from horizontal to vertical or vice versa.
This can be useful for presentation purposes or to make your table easier to read, for example if your dataset has grown to a large size and the number of columns means it’s very wide, or very long. TRANSPOSE is a handy alternative to copy-pasting, allowing you to switch rows and columns in Google Sheets instantly without errors.
Syntax
TRANSPOSE(array_or_range)
array_or_range – the array or the reference to the range of columns and rows you want to swap or transpose.
How to use TRANSPOSE Google Sheets function
This is one of the simplest formulas in Google Sheets. Take a look at the following examples:
My sample data fills cells A1 to C7. At present, my list of Employee ID numbers (Emp ID#) runs vertically along the left hand side of spreadsheet, in a column. Instead, I want to see my Employee ID list horizontally, along the top of my spreadsheet.
To do this, I enter a TRANSPOSE formula into cell D1:
You can see that my original data range has been inverted. The rows and colums from the input array_or_range have been swapped and the new (flipped) dataset starts in cell D1.
Remember to leave enough space in your spreadsheet for the new table of data. Otherwise, the formula returns #REF! error:
Another option is to enter an array instead. Here’s an example:
When entering the array, use semi-colons to separate each individual row value in the input range. For individual column values, use commas. As before, you need to ensure that there is enough empty space in your spreadsheet for the new, transposed table of data.
How to replace the old table
If you want to delete the original table of data and replace it with the transposed data
- Copy the new table (the data you have transposed).
- Go to Edit > Paste Special > Paste values only.
- Paste directly over the original table.
Transpose and keep formatting
You might notice that your formatting is lost when you transpose the data in Google Sheets. In this case, use the copy-paste method:
- Select the cells you want to transpose.
- Copy the cells.
- Click inside the cell where you want the new table of inverted data to start.
- Go to Edit > Paste special > Paste transposed.
Work more efficiently in Google Sheets
That’s how to quickly switch your data from vertical to horizontal, or rows to columns. Looking for more time-saving tips and tricks in Google Sheets? Learn how to filter Google Sheets without affecting other users or disover how to connect Google Sheets automatically.
