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

TRANSPOSE Function in Excel (Table of Contents)

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 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 this function, 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

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

It 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 the TRANSPOSE Function in Excel?

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

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.

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.

Popular Course in this category

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

Course Price

View Course

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.

It can also be used along with other functions to get the desired output. Let us see few examples for 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.

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 in excel as:

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

and you will get the final output as shown below.

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

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

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

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.

Excel

Financial Functions Excel

Lookup Functions Excel

Excel Charts

VBA

Others

Certification Courses

Excel VBA All in One Bundle

Excel Data Analysis Course

VBA Macros Course

Others