TRANSPOSE switches rows and columns in Google Sheets. A four-row, two-column table becomes a two-row, four-column result that stays linked to its source.
You enter the formula once in an empty area. The remaining result cells fill automatically, so you do not need to copy the formula across.
TRANSPOSE function syntax
=TRANSPOSE(array_or_range)- array_or_range: a cell, row, column, or rectangular range whose orientation you want to switch.
The Google TRANSPOSE reference describes the function arguments.
Example data
Enter this dataset starting in A1. Leave cells marked “leave empty” empty; type any displayed formula as a formula. Keep a separate blank area for the results.
| Day | Orders |
|---|---|
| Mon | 10 |
| Tue | 15 |
| Wed | 12 |
| Thu | 18 |
The examples below use this dataset unless the formula supplies its own values. Array results need enough empty cells to expand. The formulas use commas as argument separators. Your spreadsheet locale may require semicolons.
Transpose a two-column table
Enter this formula in E2. The days appear across the first output row, and their order counts appear directly underneath. The original A2:B5 data stays in place.
=TRANSPOSE(A2:B5)| Mon | Tue | Wed | Thu |
| 10 | 15 | 12 | 18 |
Turn a column into a row
Selecting only A2:A5 produces a single horizontal row of day names. The four source rows become four result columns.
=TRANSPOSE(A2:A5)| Mon | Tue | Wed | Thu |
Turn a row into a column
The two headings in A1:B1 become a vertical list. The width of the source determines the number of output rows.
=TRANSPOSE(A1:B1)| Day |
| Orders |
Sort before transposing
SORT first orders the original rows by the second column, from smallest count to largest. TRANSPOSE then switches that sorted table’s orientation.
=TRANSPOSE(SORT(A2:B5,2,TRUE))| Mon | Wed | Tue | Thu |
| 10 | 12 | 15 | 18 |
Choose whether to include headers
Include row 1 when you want the original headings in the transposed output. The resulting table has two rows and five columns.
=TRANSPOSE(A1:B5)| Day | Mon | Tue | Wed | Thu |
| Orders | 10 | 15 | 12 | 18 |
Other Google Sheets articles you may also like