How to use Excel TRANSPOSE Function

how to use excel transpose functionQuick Intro

Use Excel TRANSPOSE Function to change the orientation of a range. In simple words, by using transpose function in excel you can change the data from a row into a column and from a column into a row.

Syntax

TRANSPOSE (array)

  • array An array or a range you want to transpose.

More Information on Excel Transpose Function

  • You have to apply TRANSPOSE Function as an array formula using the same number of cells as you have in your source range by pressing Ctrl + Shift + Enter.
  • If you select cells less than source range, it will transpose data only for those cells. For example, In source range, you have 8 cells and in destination range, you have selected 6 cells then it will only transpose data from 6 cells.
  • You can also use paste special option to transpose data.

Examples

Please follow below steps to understand how does excel transpose function work to convert a row into a column or vice versa.

  • In the below example, we want to transpose data from range B2:D4 to range G2 to I4.

Table Data To Convert Using Excel TRANSPOSE Function

  • Go to cell G2 and select cell range up to I4.

Select Cell Range To Apply Excel TRANSPOSE Function

  • Enter below formula in cell G2 and press Ctrl+Shift+Enter

=TRANSPOSE(B2:D4)

Enter Formula Cell Range To Apply Excel TRANSPOSE Function

  • TRANSPOSE has converted data from the rows into columns. And, the formula which you have applied is an array formula, you cannot change any single cell from it.

Convert Row To Columns From Using Excel Transpose Function

Sample File

download sample file to learn more about this tips

What’s Next?

To learn more about Excel TRANSPOSE Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

Apart from this, I have a list of excel functions and some real life formulas examples.



  • Oz

    Array formulas!
    That’s when Excel gets serious! 🙂