Excel TRANSPOSE Function

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 function is stated as follows:

The function accepts the following mandatory argument:

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

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.

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.

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.

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.

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.

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.

Enter the following formula.

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

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.

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 2: Press the F9 key. The CONCATENATE function with IDs appears (shown in the following image).

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

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 #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.

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.

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 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.