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

=TRANSPOSE(array_or_range)

Example

Here’s a row of data:

ABCD
1A1B1C1D1

Rearranging the data using transpose:

=TRANSPOSE(A1:D1)

Outputs:

A
1A1
2B1
3C1
4D1

Here’s a column of data:

A
1A1
2A2
3A3
4A4

Rearranging the data using transpose:

=TRANSPOSE(A1:A4)

Outputs:

ABCD
1A1A2A3A4

Here’s a table of data:

ABC
1A1B1C1
2A2B2C2
3A3B3C3

Rearranging the data using transpose:

=TRANSPOSE(A1:C3)

Outputs:

ABC
1A1A2A3
2B1B2B3
3C1C2C3

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.

Rearranging Data Exercises
Enter a formula for each question in the formula bar for the spreadsheet below to access the next lesson.
1. Table Transpose
In cell B5, enter a formula that flips all of the data from the first table to fit the labels in cells A5:A7.
2. Column To Row
In cell A10, enter a formula that changes the first column of the newly created table back into a single row of data.
ABC
1FirstLastSales
2DwightSchrute10,000
3JimHalpert8,000
4
5First
6Last
7Sales
8
9FirstLastSales
10
11
Next Lesson

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.