30 Excel Functions in 30 Days: 13 – TRANSPOSE

Icon30DayYesterday, in the 30XL30D challenge, we counted columns with the COLUMNS function, and now it’s time for something a bit more demanding.

For day 13 in the challenge, we’ll examine the TRANSPOSE function. With this function, you can flip things around, changing vertical ranges to horizontal ones, or vice versa. Is that something you need to do? Could you use Paste Special instead? Could other functions do the same thing?

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

So, let’s take a look at the TRANSPOSE information and examples, and if you have other tips or examples, please share them in the comments.

Function 13: TRANSPOSE

The TRANSPOSE function returns a horizontal range of cells as a vertical range, or vice versa.

Transpose00

How Could You Use TRANSPOSE?

The TRANSPOSE function can change the orientation of data, or be used with other functions:

  • change horizontal data to vertical
  • show total salary over best consecutive years

To change data orientation, without links,

  • use Paste Special > Transpose.

TRANSPOSE Syntax

The TRANSPOSE function has the following syntax:

  • TRANSPOSE(array)
    • array  is an array or a range of cells to be transposed

TRANSPOSE Traps

  • TRANSPOSE must be entered as an array formula, by pressing Ctrl+Shift+Enter.
  • The TRANSPOSE destination range must have the same number of rows and columns, respectively, as the array has columns and rows.

Example 1: Change Horizontal Data to Vertical

If data is arranged horizontally in a worksheet, you can use the TRANSPOSE function to list the data vertically, in a different location. For example, in a summary sheet, or dashboard, a vertical layout might be best. With the TRANSPOSE function, you could link to your original horizontal data, without changing its layout.

To transpose a 2 row x 4 column horizontal range to a 4 row x 2 column vertical range:

  1. Select the 8 cells where you want to display the data vertically  — cells B4:C7 in this example.
  2. Type this formula, then enter it as an array formula, by pressing Ctrl+Shift+Enter.

=TRANSPOSE(B1:E2)

Curly brackets will be automatically added at the start and end of the formula, to show that it is array entered.

Transpose01

Instead of using TRANSPOSE, you could use another formula to display the data, such as this INDEX formula. It does not require array entry, and you don’t have to select all the destination cells when creating the formula.

=INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1)

Transpose02

Example 2: Change Orientation Without Links

If you just want to change the orientation of your data, without keeping a link to the original data, you can use Paste Special:

  1. Select the original data and copy it
  2. Select the top left cell of the destination range
  3. On the Ribbon’s Home tab, click the Paste drop down arrow
  4. Click Transpose
  5. (optional) Delete the original data.

Transpose03

Example 3: Total Salary For Best Consecutive Years

The TRANSPOSE function can be used with other functions, as in this eye-popping formula. It was posted by Harlan Grove, in the Excel newsgroups, in a discussion on calculating the total salary for best 5 consecutive years.

=MAX(MMULT(A8:J8,–(ABS(TRANSPOSE(COLUMN(A8:J8))
-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number+1))
-(Number-1)/2)<Number/2)))

Transpose04

As you can see by the curly brackets in the formula bar, this formula is array entered. Cell A5 is named Number, and I’ve entered 4, for the number of years this example.

The formula tests the ranges to see if there are enough consecutive COLUMNS. The results of those test (1 or 0) are multiplied by the cell values, to get the total salaries.

To check the results, in the rows below the salaries, the total salaries for each starting cell are shown, with the maximum value highlighted in yellow. This is a long way to accomplish what the previous formula did in one cell!

Transpose05

Download the TRANSPOSE Function File

To see the formulas used in today’s examples, you can download the TRANSPOSE function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the TRANSPOSE Video

To see a demonstration of the examples in the TRANSPOSE function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Change Horizontal Data to Vertical – Excel TRANSPOSE Function

_____________

You may also like...

8 Responses

  1. ikkeman says:

    Not to mention that for some matrix or vector calculations it is necessary to transpose a column vector to a row vector or vice versa. matrix multiplication (MMult) is one.

    Or that multiplying a row vector (a,b,c) with a row vector (1,2,3) results in a row of results (a*1,b*2,c*3) but multiplying a row vector (a,b,c) with a column vector (1;2;3) results in a matrix of results (a*1,b*1,c*1;a*2,b*2,c*2;a*3,b*3,c*3)

    we’re not all financial modelers.

  2. Luke Wisbey says:

    Though we’re dealing exclusively with Transpose as a Worksheet Function it’s worth mentioning also that Transpose is handy working with VBA Arrays

    See below for quick (much simplified example) of how to concatenate a vertical vector into a single delimited string – no loops etc…

    Sub Example()
    MsgBox Join([TRANSPOSE(A1:A10)],”,”])
    End Sub

    Transpose here converts the 2d Array (1 by n, 1 to 1) to a 1d Array (1 by n) allowing us to utilise Join.
    (we need not utilise Transpose via Evaluate but in the context of the Worksheet Function it makes sense)

    Apologies if this is way off track…

  3. Eric says:

    @ikkeman

    Here here!

    If you understand matrix math you can do quite a lot with Excel. I use it to define names using formulas and create graphs. It really minimizes the size of the spreadsheet – 20 to 30 kb instead of 20 to 30 Mb.

  4. Bob Ryan says:

    The last two times I needed to rearrange a significant amount of data, I used the INDIRECT function.

  5. Art Babb says:

    I worked through this formula from Example01: =INDEX($B$2:$E$2,,ROW()-ROW(C$4)+1) That works well. I expanded the array to include the years in Row 1, so $B:$1:$E:2. Then I included 1>4 in the row argument, teaching myself also the index and row functions as well. Good and thanks.

  6. Art Babb says:

    Re Example02… I did something similar recently at work. I did COPY…PASTE SPECIAL…PASTE FORMULAS…TRANSPOSE…OK. This took my table I’d already built and transposed it which I then used as source data for chart. Worked great.

  7. Art Babb says:

    Re Ex01, the fact you have to select your destination range before you enter your array formula can be a deal-killer on large ranges. Agree?

Leave a Reply to ikkeman Cancel reply

Your email address will not be published. Required fields are marked *