Excel TRANSPOSE Function

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Vandana Kataria

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

What Does Transpose Function Do in Excel?

The TRANSPOSE function in excel helps rotate (switch) the values from rows to columns and vice versa. Being a part of the Excel lookup and reference functions, its purpose is to organize the data in the desired format. To execute the formula, the exact size of the range to be transposed is selected and the CSE key (“Control+Shift+Enter”) is pressed.

The Syntax of the TRANSPOSE Function

The syntax of the function is stated as follows:

Transpose Formula in Excel

The function accepts the following mandatory argument:

Array: This is the range of cells that are to be transposed.

Table of contents

How to use the TRANSPOSE Function in Excel?

Example #1

The following table consists of data points in the cells A3:B8. The cells D3:I4 are transposed by copy-pasting the original data.

We want to transpose the given range (A3:B8) with the help of the TRANSPOSE excel function.

transpose example 1

The steps to transpose the range A3:B8 are listed as follows:

  1. Select the range D6:I7 where the transposed values should appear.


    transpose example 1.1

  2. Enter the following TRANSPOSE excel formula in the selected region (shown in the succeeding image).


    “=TRANSPOSE (A3:B8)”

    transpose example 1-2

  3. Press “Ctrl+Shift+Enter” (“Command+Shift+Enter” in Mac).
    The transposed output in D6:I7 is shown in the following image. As soon as the CSE key is pressed, the TRANSPOSE formula appears within the curly braces.

    “{=TRANSPOSE (A3:B8)}”

    The curly braces {} indicate that an array formula has been entered.

    Excel transpose function example 1-3

The Transposed Output

The range D3:I4 is transposed by the copy-paste method, while D6:I7 is transposed by the TRANSPOSE function. The two ranges are shown in the succeeding image.

If a value of the original data is changed, the output transposed by the TRANSPOSE function updates automatically. However, the output transposed by the copy-paste method remains the same.

Note: The copy-paste option creates duplicates.

excel transpose function example 1-6

Example #2

Let us use the TRANSPOSE function in combination with the IF function.

The following table shows the average marks of seven students in a school. For the students who did not appear in the examination, the “marks” field has been left blank.

In addition to transposing the given data, we want to enter “absent” in the empty cell.

Excel TRANSPOSE Function Example 2

Enter the following formula to use the TRANSPOSE and the IF functions together.

“=TRANSPOSE(IF(B3:D10="","ABSENT",B3:D10))”

The two functions check whether a cell is blank or not. If a cell in the range B3:D10 is empty, the word “ABSENT” is returned. Otherwise, the formula is supplied a value to transpose.

TRANSPOSE Example 2-1

Example #3

Working on the data of example #2, let us modify the criteria.

A student is considered to have failed if either of the following is true:

  • The average marks are less than 70.
  • He/she has not appeared in the examination.

To meet the preceding criteria, enter the following formula.

“=TRANSPOSE(IF(B3:D10<70,"FAIL",B3:D10)”

The output is shown in the following image.

Excel TRANSPOSE Function Example 2-2

Example #4

There is a list of IDs in the range B4:B7. We want to prefix the acronym ID with the help of the TRANSPOSE function.

TRANSPOSE Example 3

Enter the following formula.

“=TRANSPOSE(“ID”&B4:B7)”

TRANSPOSE Function Example 3-2

The range is transposed and the prefix is added to every cell, as shown in the following image. Likewise, a suffix can also be added by using the TRANSPOSE formula.

TRANSPOSE Example 3-3

Example #5

The TRANSPOSE and CONCATENATE functions are used together to combine the words of different rows in a single cell. The steps are listed as follows:

Step 1: Type the formula “=CONCATENATE(TRANSPOSE(B4:B7&”,”))”.

Step 1 - Type the formula

Step 2: Press the F9 key. The CONCATENATE function with IDs appears (shown in the following image).

Example 3-5

Step 3: Remove the curly braces {} from the formula.

Example 3-6

Step 4: Press the "Enter" key. The numbers of the range B4:B7 are combined in the single cell D5. The output is shown in the following image.

Example 3-7

Example #6

The following table shows the books on different subjects in a library. Every book is placed on a shelf denoted by the location.

We want to retrieve the location of the books (second subsequent image) using the data in the range B4:E6.

Example 4
Example 4-1

We use the following formula.

“=INDEX($B$4:$B$6,MATCH(1,MMULT(--($C$4:$E$6=<Subject>),TRANSPOSE(COLUMN($C$4:$E$6)^0)),0))”

The Explanation of the Preceding Formula

In the following pointers, a part of the formula is displayed, followed by an explanation.

  • “--($C$4:$E$6=<Subject>)”

The field <subject> corresponds to the cell H3. So, the formula becomes “--($C$4:$E$6=H3).”

This creates an array of “1” and “0” which indicates the presence or absence of a value. For instance, the book “biology” shown in H3 is represented as {1,0,0;0,0,0;0,0,0}.

  • “TRANSPOSE(COLUMN($C$4:$E$6)^0))”

This creates an array of three rows in a column. The “0” of the formula ensures that the numbers are converted to “1.” So, the output of this function is {1,1,1}.

  • “MMULT(--($C$4:$E$6=<Subject>),TRANSPOSE(COLUMN($C$4:$E$6)^0))”

The MMULT multiplies the output of A and B. So, “MMULT({1,0,0;0,0,0;0,0,0}, {1,1,1})” gives the output {1;0;0}.

  • “MATCH(1,MMULT(<..>),0)”

This matches the output of column C with 1. “MATCH(1,{1;0;0},0)” returns the position 1.

  • “INDEX($B$4:$B$6,MATCH(<…>,0))”

This identifies the cell value for which the position is specified by the MATCH function. So, “INDEX($B$4:$B$6,1)” returns A1.

Example 4-2

The Output for the Book “Geography”

Similar to the book “Biology,” the output for the book “Geography” is listed as follows:

  • “--($C$4:$E$6=D6)” returns {0,0,0;0,0,0;0,1,0}.
  • “TRANSPOSE(COLUMN($C$4:$E$6)^0))” returns {1,1,1}.
  • “MMULT({0,0,0;0,0,0;0,1,0},{1,1,1})” returns {0;0;1}.
  • “MATCH(1,{0;0;1},0)” returns 3.
  • “INDEX($B$4:$B$6,3)” returns A3.

The Characteristics of the TRANSPOSE Function

The features of the function are listed as follows:

  • It links the data to the source.
  • It returns the #VALUE error if the number of rows and columns selected are not equal to the columns and rows of the source data.
  • Once entered, any individual cell which is a part of this function cannot be changed.

Frequently Asked Questions (FAQs)

1. Define the Excel TRANSPOSE function.

The TRANSPOSE function in excel flips the orientation of a range from horizontal to vertical and vice versa. The syntax is stated as follows:

“=TRANSPOSE(array)”

With the TRANSPOSE function, the first row of the array (data source) becomes the first column of the new array (output). Likewise, the second row of the old array becomes the second column of the new array and so on. A change in the data source and the output transposed by the function automatically updates.

Note: The TRANSPOSE function does not copy cell formatting. It only copies the cell values.

2. How to transpose the rows and columns in Excel?

The rows and columns of Excel can be transposed in the following ways:
- TRANSPOSE function–In this method, select a blank range containing the exact number of rows as the columns of the original table. Likewise, the number of empty columns must match the rows of the data source.
- INDIRECT Function and ADDRESS functions –In this method, rotate the range with a non-array formula which is “=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1))).” The condition is that the rows and columns begin with cell A1.

3. “Paste special” option–In this method, select “paste special” from the context menu and choose “transpose.” The new data table is not linked with the source.

4. “Paste link” option–In this method, select the “paste link” option from the “paste special” dialog box. The resulting table is linked with the source.

3. How is the TRANSPOSE excel function used?

The steps to use the function are listed as follows:

- Count the number of rows and columns of the original data source.
- Select an empty range with the same number of rows as the columns of the old array and vice versa.
- Type the TRANSPOSE formula and press “Ctrl+Shift+Enter.”
The transposed output appears in the selected range.

  • The TRANSPOSE function helps rotate the values from rows to columns and vice versa.
  • The TRANSPOSE function accepts one mandatory argument–“array,” representing the range to be transposed.
  • Prior to executing the TRANSPOSE formula, the exact size of the range to be transposed is selected.
  • Being an array function, the TRANSPOSE formula is entered by pressing “Ctrl+Shift+Enter.”
  • A range can be transposed by the TRANSPOSE function, the INDIRECT and ADDRESS functions, the “paste special” option, and so on.
  • The TRANSPOSE function is used in combination with IF, CONCATENATE, INDEX, MATCH, etc., to get the desired output.