# TRANSPOSE Function in Excel

### Part 1: Introduction to TRANSPOSE Function

🔎 Definition:

The `TRANSPOSE` function in Excel is a lookup/reference function that transposes the rows and columns of an array or range of cells.

🎯 Purpose:

It’s used when you want to switch or rotate data from rows to columns, or vice versa, in your Excel spreadsheet.

🔣 Syntax & Arguments:

syntax
```=TRANSPOSE(array) ```

💡 Explanation of Arguments:

The `TRANSPOSE` Function only has one argument: `array`. This is the range of cells that you want to transpose.

📤 Return Value:

The `TRANSPOSE` The function will return a transposed range of cells – meaning the rows will be switched to columns, and the columns will be switched to rows.

⚠️ Remarks:

• The `TRANSPOSE` The function must be entered as an array formula in the final range of cells you want the transposed data to occupy.
• The range where the result is placed must be the same shape as the source data but transposed. For instance, if the source data has dimensions 3×4 (3 rows and 4 columns), the resulting array should be 4×3 (4 rows and 3 columns).

### Part 2: Examples

Let’s look at some examples to understand better how to use the `TRANSPOSE` function.

#### Example 1:

🎯 Purpose: Let’s transpose the sales data of a small company to analyze it better.

Data Table:

ABCDE
1QuarterProduct AProduct BProduct CFormula
2Q1100200300`=TRANSPOSE(A2:D4)`
3Q2150250350
4Q3200300400

Result Table:

ABCD
1QuarterQ1Q2Q3
2Product A100150200
3Product B200250300
4Product C300350400

🔑 How to use:

Select the cell E1, then type the formula `=TRANSPOSE(A2:D4)`. Press `CTRL+SHIFT+ENTER` to get the transposed table.

#### Example 2:

🎯 Purpose: Transpose of the project completion status table.

Data Table:

ABCDE
1ProjectStatusExpected CompletionActual CompletionFormula
2Project ACompleted01/01/202331/12/2022`=TRANSPOSE(A2:D4)`
3Project BOngoing01/07/2023
4Project CNot Started01/01/2024

Result Table:

ABCD
1ProjectProject AProject BProject C
2StatusCompletedOngoingNot Started
3Expected Completion01/01/202301/07/202301/01/2024
4Actual Completion31/12/2022

🔑 How to use:

Select the cell E1, then type the formula `=TRANSPOSE(A2:D4)`. Press `CTRL+SHIFT+ENTER` to get the transposed table.

#### Example 3:

🎯 Purpose: Transpose customer feedback data for analysis.

Data Table:

ABCDE
1CustomerFeedbackRatingDateFormula
2Customer AGood501/01/2023`=TRANSPOSE(A2:D4)`
3Customer BAverage301/02/2023
4Customer CExcellent501/03/2023

Result Table:

ABCD
1CustomerCustomer ACustomer BCustomer C
2FeedbackGoodAverageExcellent
3Rating535
4Date01/01/202301/02/202301/03/2023

🔑 How to use:

Select the cell E1, then type the formula `=TRANSPOSE(A2:D4)`. Press `CTRL+SHIFT+ENTER` to get the transposed table.

➡️ Example 4: Nested TRANSPOSE and IF Functions

Purpose: To display students’ names and corresponding statuses based on their scores. The data will be transposed from columns to rows.

Data Table and Formulas:

ABC
1NameScoreStatus
2John85`=IF(B2>=60,"Pass","Fail")`
3Alice59`=IF(B3>=60,"Pass","Fail")`
4Bob65`=IF(B4>=60,"Pass","Fail")`

Result Table:

Select a 3×4 range, type the formula `=TRANSPOSE(A1:C4)` and press Ctrl+Shift+Enter.

1234
ANameJohnAlice
BScore8559
CStatusPassFail

Explanation: The IF function is used here to determine the status of each student based on their score. If the score is 60 or above, the group is “Pass”. Otherwise, it’s “Fail”. Then, we used the TRANSPOSE function to change the orientation of the data.

➡️ Example 5: Nested TRANSPOSE and SUM Functions

Purpose: To calculate the total sales of each product and display the result in a row.

Data Table and Formulas:

ABCDE
1ProductQ1Q2Q3Total Sales
2Apple100120130`=SUM(B2:D2)`
3Banana8090110`=SUM(B3:D3)`
4Cherry507080`=SUM(B4:D4)`

Result Table:

Select a 5×4 range, type the formula `=TRANSPOSE(A1:E4)` and press Ctrl+Shift+Enter.

ABCD
1ProductAppleBananaCherry
2Q11008050
3Q21209070
4Q313011080
5Total Sales350280200

Explanation: The SUM function is used to add each quarter’s sales for each product. Then, we used the TRANSPOSE function to change the orientation of the data.

➡️ Example 6: Nested TRANSPOSE and VLOOKUP Functions

Purpose: To find specific information from a table and transpose the data.

Data Table and Formulas:

ABC
1NameAgeHeight
2John25180
3Alice30170
4Bob35175

On another cell, enter the formula `=VLOOKUP("Alice",A1:C4,2,FALSE)` to find Alice’s age.

Result Table:

Select a 1×3 range, type the formula `=TRANSPOSE(A1:C3)` and press Ctrl+Shift+Enter.

1Alice
230
3170

Explanation: The VLOOKUP function is used to find the data associated with ‘Alice’ in the table. Then, we used the TRANSPOSE function to change the orientation of the data.

➡️ Example 7: Nested TRANSPOSE and COUNTA Functions

Purpose: Count the number of non-empty cells in a range and display the count across columns.

Data Table and Formulas:

A
1Name
2John
3Alice
4Bob
5
6Tom
7`=COUNTA(A1:A6)`

Result Table:

Select a 1×2 range, type the formula `=TRANSPOSE(A7)` and press Ctrl+Shift+Enter.

14

Explanation: The COUNTA function counts the number of non-empty cells in the Name column. Then, the TRANSPOSE function changes the orientation of the single value.

➡️ Example 8: Nested TRANSPOSE and MAX Functions

Purpose: Find the maximum sales in a quarter and display it in a row.

Data Table and Formulas:

ABCD
1ProductQ1Q2Q3
2Apple100120130
3Banana8090110
4Cherry507080
5`=MAX(B2:B4)``=MAX(C2:C4)``=MAX(D2:D4)`

Result Table:

Select a 1×4 range, type the formula `=TRANSPOSE(A5:D5)` and press Ctrl+Shift+Enter.

1100120130

Explanation: The MAX function finds the maximum sales in each quarter. Then, the TRANSPOSE function displays these values across a row.

➡️ Example 9: Nested TRANSPOSE and MIN Functions

Purpose: Find the minimum score in a set and display it in a row.

Data Table and Formulas:

ABCD
1StudentTest1Test2Test3
2John808590
3Alice758085
4Bob707580
5`=MIN(B2:B4)``=MIN(C2:C4)``=MIN(D2:D4)`

Result Table:

Select a 1×4 range, type the formula `=TRANSPOSE(A5:D5)` and press Ctrl+Shift+Enter.

1707580

Explanation: The MIN function is used to find the minimum score in each test. Then, the TRANSPOSE function displays these values across a row.

➡️ Example 10: Nested TRANSPOSE and AVERAGE Functions

Purpose: Calculate each student’s average score and display the averages in a row.

Data Table and Formulas:

ABCDE
1StudentTest1Test2Test3Average Score
2John808590`=AVERAGE(B2:D2)`
3Alice758085`=AVERAGE(B3:D3)`
4Bob707580`=AVERAGE(B4:D4)`

Result Table:

Select a 1×4 range, type the formula `=TRANSPOSE(E2:E4)` and press Ctrl+Shift+Enter.

1858075

Explanation: The AVERAGE function is used to calculate the average score of each student. Then, the TRANSPOSE function displays these averages across a row.

### Part 3: Tips and Tricks

🎁 Tips and Tricks:

• When using the `TRANSPOSE` Function, select the correct range for the resulting transposed data. If you choose fewer or more cells than required, Excel will return an error.
• To update the transposed data when the original data changes, make sure to use absolute cell references in your `TRANSPOSE` function.
• If you wish to “lock” the transposed data (meaning it doesn’t change when the original data changes), copy it and paste it as values in a new location.
• Remember, you must use `CTRL+SHIFT+ENTER` after typing the formula instead of just `ENTER`.