Transpose in Excel

Excel Functions Tutorials

TRANSPOSE Function in Excel (Table of Contents)

TRANSPOSE in Excel
Transpose Formula in Excel
How to Use TRANSPOSE Function in Excel?

Sometimes there is a need to switch or rotate cells from rows to columns and vice versa. This can be quickly done in Excel in two ways:

Copy and paste
Using the Transpose function in excel.

#1 – Transpose using Paste Special

To simply transpose the cells, you need to copy the content (CTRL + C or ⌘ + C for Mac) you want to transpose. Then, go to the cell where you want the desired output, right click and then click on the Paste Special option as shown below:

Then, check transpose in excel and click OK.

This will paste the transposed content.

The copy-paste option for the transpose in excel creates duplicates, which means that the source and transposed data are not connected with each other. Suppose there is a change in the original data, you again have to copy and paste to get the corrected transposed values.

However, if you use the Transpose function in Excel, the cells (or arrays) are linked to each other, and any change in the original array will automatically update the transposed array.

#2 – Transpose using Transpose Formula

Below is the TRANSPOSE Formula in excel.

Arguments used for TRANSPOSE Formula in excel.

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

Transpose in excel is an array function.

Some of the key features of the TRANSPOSE function in excel are listed below:

Applying the TRANSPOSE in excel to a single cell has no meaning.
The key to getting an array function work is to press CTRL + SHIFT + ENTER after typing the formula instead of just ENTER as done in other functions.
You cannot change any individual cell after you have entered the array function.

How to use TRANSPOSE Function in Excel?

Let us try to use the Transpose excel function in the example above used for copy-paste illustration.

TRANSPOSE Function in Excel Example #1

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

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

Now, type the TRANSPOSE Formula in excel 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 function in excel output.

Notice that the TRANSPOSE Function in Excel 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 in excel, will be automatically updated, but not in the case where you have used copy-paste option.

The Transpose in excel can also be used along with other functions to get the desired output. Let us see few examples for this.

TRANSPOSE in Excel 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.

You can do so by using Transpose in excel along with 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. The Transpose function in excel 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 in excel as:

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

and you will get the final output as shown below.

TRANSPOSE in Excel #3

Sometimes there is a need to add some characters to the existing ones along with transpose in excel. 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 TRANSPOSE 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 in excel. This may be helpful when using the Transpose function in excel 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 transpose function in excel as shown below.

Press F9 key.

Remove {} from the formula

Press Enter.

It will concatenate the cells in a single cell.

TRANSPOSE in Excel 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 subject (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))

TRANSPOSE function in Excel 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 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 About TRANSPOSE Function in Excel

The copy paste option creates duplicates.
The TRANSPOSE function in Excel 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, TRANSPOSE in excel will give a #VALUE error
Once the TRANSPOSE Formula in excel has been entered, any individual cell which is a part of the Transpose function cannot be changed.
Press CTRL+SHIFT+ENTER to enter the TRANSPOSE Formula in excel.

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

TRANSPOSE Excel Function