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 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:
#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.
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.
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.
Example #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
- 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.
Recommended Articles
This has been a guide to TRANSPOSE Function in Excel. Here we discuss how to TRANSPOSE in excel using 1) Paste Special and 2) TRANSPOSE Formula along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- Copy Paste in Excel VBA
- Excel VBA IsEmpty Function
- Paste Special in Excel VBA | Examples | Formula
- Why SWITCH Function in Excel?
- “Not Equal to” Formula in Excel
- VBA in Arrays Excel
- Remove (Delete) Duplicates in Excel
- COMBIN in Excel
- OFFSET in Excel
- FIND in Excel
- Row in Excel
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
