Data presented in rows may be better understood in columns.
Similarly, data presented in columns may be better understood in rows.
TRANSPOSE makes reorganising data easy.
TRANSPOSE
The TRANSPOSE function transfers data from one position to another. It changes existing rows into columns and/or existing columns into rows.
Syntax
- array_or_range = you guessed it: data in an array or range reference
Example
Here’s a row of data:
| A | B | C | D | |
| 1 | A1 | B1 | C1 | D1 |
Rearranging the data using transpose:
Outputs:
| A | |
| 1 | A1 |
| 2 | B1 |
| 3 | C1 |
| 4 | D1 |
Here’s a column of data:
| A | |
| 1 | A1 |
| 2 | A2 |
| 3 | A3 |
| 4 | A4 |
Rearranging the data using transpose:
Outputs:
| A | B | C | D | |
| 1 | A1 | A2 | A3 | A4 |
Here’s a table of data:
| A | B | C | |
| 1 | A1 | B1 | C1 |
| 2 | A2 | B2 | C2 |
| 3 | A3 | B3 | C3 |
Rearranging the data using transpose:
Outputs:
| A | B | C | |
| 1 | A1 | A2 | A3 |
| 2 | B1 | B2 | B3 |
| 3 | C1 | C2 | C3 |
If a piece of data is originally in row 2 of column 3. Transposing that data will move it to row 3 of column 2 of the output.
The TRANSPOSE function switches the row and column positions.
| A | B | C | |
| 1 | First | Last | Sales |
| 2 | Dwight | Schrute | 10,000 |
| 3 | Jim | Halpert | 8,000 |
| 4 | |||
| 5 | First | ||
| 6 | Last | ||
| 7 | Sales | ||
| 8 | |||
| 9 | First | Last | Sales |
| 10 | |||
| 11 |
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.