Excel TRANSPOSE function

The TRANSPOSE function in Excel enables you to change the orientation of a specified cell range.

How to use the TRANSPOSE function

In order to utilise the transpose function, you must ensure that you have sufficient space to accommodate the range that you are targeting.  For example, in the attached example, we are looking to transpose a 5×1 cell range. We must therefore have a 1×5 cell range available to transpose the data into.

Once you have established you have an appropriate range, enter the formula =TRANSPOSE(B3:B7) in your starting cell.  Once done, hit enter.  You will notice that a #VALUE! Error is returned at first. Next, highlight the series of cells where you want to transpose the data, starting from the cell in which you entered the formula. In the attached example, these are cells D10 to H10. Once highlighted, click in the formula box where you should see the formula =TRANSPOSE(B3:B7). Next, before hitting enter on the formula, press Control – Shift – Enter on the keyboard at the same time. This should activate the array formula across all of the highlighted cells.

Syntax

=TRANSPOSE(array) – note, this will only work as an array formula.

Arguments

array – the range of cells you wish to transpose (i.e. change the orientation of a cell range from horizontal to vertical, or vice versa).

Function category

Lookup and reference

Returns

The TRANSPOSE function returns the contents of the input array in a different orientation (i.e. from horizontal to vertical, or vice versa).

Download Excel File

 Example of TRANSPOSE Formula

Comments (No)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.