TRANSPOSE

 The TRANSPOSE function in Excel is used to switch the orientation of a range of cells from rows to columns or vice versa. Below are 10 examples of how to use the TRANSPOSE function, including examples with data and formulas.

---

1. Basic TRANSPOSE with Data

If you have data in a row and want to transpose it into a column:

• Original Data (Row): A1:C1 → 1, 2, 3

• Formula: =TRANSPOSE(A1:C1)

• Result (Column):

  Copy

  1
  2
  3

---

2. TRANSPOSE a Column into a Row

If you have data in a column and want to transpose it into a row:

• Original Data (Column): A1:A3 → 1, 2, 3

• Formula: =TRANSPOSE(A1:A3)

• Result (Row): 1, 2, 3

---

3. TRANSPOSE a Table

If you have a 2D table and want to switch rows and columns:

• Original Data:

  Copy

  A1:B2 → 1, 2
           3, 4

• Formula: =TRANSPOSE(A1:B2)

• Result:

  Copy

  1, 3
  2, 4

---

4. TRANSPOSE with Formulas

If your original data contains formulas, the transposed data will retain the calculated values:

• Original Data: A1:C1 → =1+1, =2+2, =3+3

• Formula: =TRANSPOSE(A1:C1)

• Result:

  Copy

  2
  4
  6

---

5. TRANSPOSE Dynamic Arrays (Excel 365/2021)

In Excel 365 or Excel 2021, you can use dynamic arrays to automatically spill the transposed data:

• Original Data: A1:C1 → 1, 2, 3

• Formula: =TRANSPOSE(A1:C1)

• Result: The transposed data will automatically fill the required cells.

---

6. TRANSPOSE with Mixed Data Types

If your data contains numbers, text, and dates:

• Original Data: A1:C1 → 1, "Apple", 01/01/2023

• Formula: =TRANSPOSE(A1:C1)

• Result:

  Copy

  1
  Apple
  01/01/2023

---

7. TRANSPOSE with Blank Cells

If your data contains blank cells:

• Original Data: A1:C1 → 1, , 3

• Formula: =TRANSPOSE(A1:C1)

• Result:

  Copy

  1
  (Blank)
  3

---

8. TRANSPOSE with Conditional Formatting

If your original data has conditional formatting, the formatting will not carry over to the transposed data. You’ll need to reapply it manually.

---

9. TRANSPOSE with Array Formulas (Legacy Excel)

In older versions of Excel (pre-365), you need to use Ctrl+Shift+Enter to create an array formula:

• Original Data: A1:C1 → 1, 2, 3

• Formula: =TRANSPOSE(A1:C1) (press Ctrl+Shift+Enter)

• Result:

  Copy

  1
  2
  3

---

10. TRANSPOSE with Linked Data

If you want to transpose data that updates dynamically when the source changes:

• Original Data: A1:C1 → 1, 2, 3

• Formula: =TRANSPOSE(A1:C1)

• If you change A1 to 10, the transposed data will automatically update to:

  Copy

  10
  2
  3

---

Key Notes:

• The TRANSPOSE function works best with static data or dynamic arrays (Excel 365/2021).

• If you’re using an older version of Excel, remember to use Ctrl+Shift+Enter for array formulas.

• Transposed data does not retain formatting or conditional formatting from the original range.

• Ensure the destination range has enough space to accommodate the transposed data.