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:
=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
TRANSPOSEThe 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:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Quarter | Product A | Product B | Product C | Formula |
| 2 | Q1 | 100 | 200 | 300 | =TRANSPOSE(A2:D4) |
| 3 | Q2 | 150 | 250 | 350 | |
| 4 | Q3 | 200 | 300 | 400 |
Result Table:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Quarter | Q1 | Q2 | Q3 |
| 2 | Product A | 100 | 150 | 200 |
| 3 | Product B | 200 | 250 | 300 |
| 4 | Product C | 300 | 350 | 400 |
🔑 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:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Project | Status | Expected Completion | Actual Completion | Formula |
| 2 | Project A | Completed | 01/01/2023 | 31/12/2022 | =TRANSPOSE(A2:D4) |
| 3 | Project B | Ongoing | 01/07/2023 | – | |
| 4 | Project C | Not Started | 01/01/2024 | – |
Result Table:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Project | Project A | Project B | Project C |
| 2 | Status | Completed | Ongoing | Not Started |
| 3 | Expected Completion | 01/01/2023 | 01/07/2023 | 01/01/2024 |
| 4 | Actual Completion | 31/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:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Feedback | Rating | Date | Formula |
| 2 | Customer A | Good | 5 | 01/01/2023 | =TRANSPOSE(A2:D4) |
| 3 | Customer B | Average | 3 | 01/02/2023 | |
| 4 | Customer C | Excellent | 5 | 01/03/2023 |
Result Table:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Customer A | Customer B | Customer C |
| 2 | Feedback | Good | Average | Excellent |
| 3 | Rating | 5 | 3 | 5 |
| 4 | Date | 01/01/2023 | 01/02/2023 | 01/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:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Score | Status |
| 2 | John | 85 | =IF(B2>=60,"Pass","Fail") |
| 3 | Alice | 59 | =IF(B3>=60,"Pass","Fail") |
| 4 | Bob | 65 | =IF(B4>=60,"Pass","Fail") |
Result Table:
Select a 3×4 range, type the formula =TRANSPOSE(A1:C4) and press Ctrl+Shift+Enter.
| 1 | 2 | 3 | 4 |
|---|---|---|---|
| A | Name | John | Alice |
| B | Score | 85 | 59 |
| C | Status | Pass | Fail |
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:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Q1 | Q2 | Q3 | Total Sales |
| 2 | Apple | 100 | 120 | 130 | =SUM(B2:D2) |
| 3 | Banana | 80 | 90 | 110 | =SUM(B3:D3) |
| 4 | Cherry | 50 | 70 | 80 | =SUM(B4:D4) |
Result Table:
Select a 5×4 range, type the formula =TRANSPOSE(A1:E4) and press Ctrl+Shift+Enter.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Apple | Banana | Cherry |
| 2 | Q1 | 100 | 80 | 50 |
| 3 | Q2 | 120 | 90 | 70 |
| 4 | Q3 | 130 | 110 | 80 |
| 5 | Total Sales | 350 | 280 | 200 |
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:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Age | Height |
| 2 | John | 25 | 180 |
| 3 | Alice | 30 | 170 |
| 4 | Bob | 35 | 175 |
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.
| 1 | Alice |
|---|---|
| 2 | 30 |
| 3 | 170 |
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 | |
|---|---|
| 1 | Name |
| 2 | John |
| 3 | Alice |
| 4 | Bob |
| 5 | |
| 6 | Tom |
| 7 | =COUNTA(A1:A6) |
Result Table:
Select a 1×2 range, type the formula =TRANSPOSE(A7) and press Ctrl+Shift+Enter.
| 1 | 4 |
|---|
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:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Q1 | Q2 | Q3 |
| 2 | Apple | 100 | 120 | 130 |
| 3 | Banana | 80 | 90 | 110 |
| 4 | Cherry | 50 | 70 | 80 |
| 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.
| 1 | 100 | 120 | 130 |
|---|
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:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Test1 | Test2 | Test3 |
| 2 | John | 80 | 85 | 90 |
| 3 | Alice | 75 | 80 | 85 |
| 4 | Bob | 70 | 75 | 80 |
| 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.
| 1 | 70 | 75 | 80 |
|---|
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:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Test1 | Test2 | Test3 | Average Score |
| 2 | John | 80 | 85 | 90 | =AVERAGE(B2:D2) |
| 3 | Alice | 75 | 80 | 85 | =AVERAGE(B3:D3) |
| 4 | Bob | 70 | 75 | 80 | =AVERAGE(B4:D4) |
Result Table:
Select a 1×4 range, type the formula =TRANSPOSE(E2:E4) and press Ctrl+Shift+Enter.
| 1 | 85 | 80 | 75 |
|---|
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
TRANSPOSEFunction, 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
TRANSPOSEfunction. - 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+ENTERafter typing the formula instead of justENTER.