LESSONS 1. Intro 2. Cell Referencing 3. Data Types 4. Operators 5. Functions 6. Intro To Logic 7. Simple Math 8. Math & Logic 9. Changing Text 10. Replacing Text 11. Splitting Text 12. Joining Text 13. Sorting Data 14. Rearranging Data 15. Looking Up Data

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)
• 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:

=TRANSPOSE(A1:D1)

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:

=TRANSPOSE(A1:A4)

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:

=TRANSPOSE(A1:C3)

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.

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.
 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
Next Lesson

FREE RESOURCE