< Go Back

Excel TRANSPOSE Function

What is the TRANSPOSE Function?

The Microsoft Excel TRANSPOSE function is a tool used in Excel to “flip” the orientation of a range of cells. This function would transform a horizontal range to a vertical range OR a vertical range to a horizontal range. It is categorized as a Reference/Lookup Function.

Formula

=TRANSPOSE(array)

Explanation

  • array – The array or range of cells to transpose.

The Return value of the TRANSPOSE function is an array in a new orientation (also known as the ‘flipped’ array)

Also, when an array is transposed, the 1st row of that array is used as the 1st column of the new array, the 2nd row of the old array is used as the 2nd column of the new array, and the 3rd row of array becomes 3rd column of the new array, and so on.

Example

How to use the Excel TRANSPOSE Function

In the example below, we are going to be flipping/transposing a “Words & Opposite” table.

The following steps should be taken;

  1. Note the range of the values to be transposed. In this example, the array is a “2 x 5vertical array and the range of values is B3:C7.

Figure 1. Array to transpose

  1. Next, Highlight cells in a “5 x 2horizontal pattern. This would serve as the position for the new horizontal/flipped orientation.

Figure 2. Highlight position of the new array

  1. Input the formula; =TRANSPOSE(B3:C7) into the formula bar above the spreadsheet.

Figure 3. Input TRANSPOSE formula

  1. Press Ctrl + Shift + Enter. This will place {} brackets around the formula, signifying that it is an array. The array has been flipped.

Figure 4. The flipped array

Notes

  • It is important to note that you must enter the TRANSPOSE function as an array formula. This means that you must highlight a set of cells where the new array would be located and then input the formula into the formula bar above (fx box). If you just input the TRANSPOSE(array) formula into a cell, there would be no flipping.
  • For step 4 – “Press Ctrl + Shift + Enter”; you have to hold “Ctrl”, then hold “Shift” and then press “Enter”. Anything otherwise leads to an error.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar