Excel Transpose Function | How to use TRANSPOSE Formula?

What Does Transpose Function Do in Excel?

Transpose function in excel is used to rotate the values or switch the values from rows to columns and columns to rows, as this function takes an array of cells as an argument so to execute it we need to press CSE or Control + Shift + Enter, first select the exact size of range which needs to transposed and then use the transpose formula.

Syntax

Below is the TRANSPOSE Formula in excel.

Arguments

array: Required. A range of cells you want to transpose.

It is an array function.

How to use the TRANSPOSE Function in Excel? (with Examples)

You can download this TRANSPOSE Function Excel Template here – TRANSPOSE Function Excel Template

Example #1

The original data you want to transpose is in A3: B8.

Step 1: Select the range where you want your transposed value, here, D6: I7.

Step 2: Now, type the TRANSPOSE Formula in the selected region and press CTRL+SHIFT+ENTER (or COMMAND+SHIFT+ENTER in Mac).

=TRANSPOSE (A3: B7)

You will get the transposed output in D6: I7 as shown below.

In this, D3: I4 is the copy-paste transposed output and D6: I7 is by transpose output.

Notice that the Excel TRANSPOSE Function is

{=TRANSPOSE (A3: B7)}

The curly braces { } shown above indicate that the function has been input as an Array Formula.

Now, if you have to change any value in the original data, say B8 = SUM(B3: B7)the transposed output, where you have used the TRANSPOSE, will be automatically updated, but not in the case where you have used copy-paste option.

It can also be used along with other functions to get the desired output. Let us see few examples of this.

Example #2

Suppose you have a list of students and their marks as shown below. Some of the students did not give exams. Hence, the marks column is left blank for those students.

You want to transpose this data, and when the cell is empty, you want to put “Absent” in that cell.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

You can do so by using Transpose along with the IF function. You may use the following TRANSPOSE Formula in excel for doing so:

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

‘IF(B3: D10=” “, “ABSENT”’ means that if any cell in the range B3: D10 is empty, it will place “Absent” instead. It will transpose the given range.

Suppose in this list, if any student has scored less than 70 marks or has not appeared in the exam is considered FAIL, you may change the TRANSPOSE Formula as:

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

and you will get the final output as shown below.

Example #3

Sometimes there is a need to add some characters to the existing ones along with transpose. Suppose you have a list of IDs in B4: B7 as shown below.

You want to transpose the ids and add prefix “ID” to it. You can do so using the Formula in excel:

=TRANSPOSE(“ID”&B4:B7)

This will transpose the data and add the prefix to each of the cell content.

You may also add any value as suffix using the above TRANSPOSE Formula. This may be helpful when using the Transpose function to concatenate the words given in different rows into a single cell. To do so, you may follow the following steps:

Type the syntax: CONCATENATE(TRANSPOSE(B4:B7&”,”). Do not press Enter.
Select the excel transpose function as shown below.

Press F9 key.

Remove {} from the formula

Press Enter.

It will concatenate the cells in a single cell.

Example #4

Suppose you have a library data wherein the several subjects are arranged on a shelf, and there are different shelves as shown below.

You may want to retrieve the location of any of the subjects (given in H3:H11) using this data (B4:E6).

You can do so using the syntax

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

Let us see how this TRANSPOSE Formula in excel works.

–($C$4:$E$6=<Subject>)

When <Subject> is given in H3, it will be –($C$4:$E$6=H3).

This forms an array of 1 and 0 to indicate the presence or absence of the value. For Biology in H3, it will form the array as: {1,0,0;0,0,0;0,0,0}

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

It creates an array of 3 rows in a column, and a power of 0 makes sure that the numbers are converted to 1. The output of this function is {1,1,1}

MMULT(–($C$4:$E$6=<Subject>),TRANSPOSE(COLUMN($C$4:$E$6)^0))

The MMULT function multiplies the output of A and B.

MMULT({1,0,0;0,0,0;0,0,0}, {1,1,1}) gives the output {1;0;0}

MATCH(1,MMULT(<..>),0)

It will match the output of C with 1. MATCH(1, {1;0;0},0) returns the position 1.

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

It will identify the value of the cell for which the match function specified the position. INDEX($B$4:$B$6, 1) will return A1.

Similarly for “Geography” as subject, it will be:

–($C$4:$E$6 = D6) returns {0,0,0;0,0,0;0,1,0}
TRANSPOSE(COLUMN($C$4:$E$6)^0)) retuns {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.

Things to Remember

The copy-paste option creates duplicates.
This Function links the data to the source.
If the number of rows and columns selected are not equal to the columns and rows of the source data, it will give a #VALUE error
Once it has been entered, any individual cell which is a part of this function cannot be changed.
Press CTRL+SHIFT+ENTER to enter the Formula in excel.