What Does Transpose Function Do in Excel?
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 TRANSPOSE Function
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.
How to use the TRANSPOSE Function in Excel?
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 steps to transpose the range A3:B8 are listed as follows:
- Select the range D6:I7 where the transposed values should appear.
- Enter the following TRANSPOSE excel formula in the selected region (shown in the succeeding image).
“=TRANSPOSE (A3:B8)”
- Press “Ctrl+Shift+Enter” (“Command+Shift+Enter” in Mac).
The transposed output in D6:I7 is shown in the following image. As soon as the CSE key is pressed, the TRANSPOSE formula appears within the curly braces.
“{=TRANSPOSE (A3:B8)}”
The curly braces {} indicate that an array formula has been entered.
The Transposed Output
The range D3:I4 is transposed by the copy-pasteTransposed By The Copy-pasteTranspose is the process of swapping columns with rows in Excel. Because this function is linked to cells, any changes done are reflected in transposed data.read more 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 functionsIF FunctionsIF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is “true” or “false”. It is a conditional function of Excel, which returns the result based on the fulfillment or non-fulfillment of the given criteria. read more 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 functionsCONCATENATE FunctionsThe CONCATENATE function in Excel helps the user concatenate or join two or more cell values which may be in the form of characters, strings or numbers.read more 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 MMULTMMULTMMULT is an in-built Math & Trigonometry function in Excel that performs matrix multiplication of 2 arrays where the columns of Array 1 are equivalent to the rows for Array 2. read more 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 functionMATCH FunctionThe MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula “MATCH(8,A11:A15,0)” returns 3. This is because the number 8 is at the third position. read more. 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 Characteristics of the TRANSPOSE Function
The features of the function are listed as follows:
- It links the data to the source.
- It returns the #VALUE error#VALUE Error#VALUE! Error in Excel represents that the reference cell the user has either entered an incorrect formula or used a wrong data type (mostly numerical data). Sometimes, it is difficult to identify the kind of mistake behind this error.read more 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.
Frequently Asked Questions
The TRANSPOSE function in excel flips the orientation of a range from horizontal to vertical and vice versa. The syntax is stated as follows:
“=TRANSPOSE(array)”
With the TRANSPOSE function, the first row of the array (data source) becomes the first column of the new array (output). Likewise, the second row of the old array becomes the second column of the new array and so on. A change in the data source and the output transposed by the function automatically updates.
Note: The TRANSPOSE function does not copy cell formatting. It only copies the cell values.
The rows and columns of Excel can be transposed in the following ways:
– TRANSPOSE function–In this method, select a blank range containing the exact number of rows as the columns of the original table. Likewise, the number of empty columns must match the rows of the data source.
– INDIRECT Function and ADDRESS functions –In this method, rotate the range with a non-array formula which is “=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1))).” The condition is that the rows and columns begin with cell A1.
3. “Paste special” option–In this method, select “paste special” from the context menu and choose “transpose.” The new data table is not linked with the source.
4. “Paste link” option–In this method, select the “paste link” option from the “paste special” dialog box. The resulting table is linked with the source.
The steps to use the function are listed as follows:
– Count the number of rows and columns of the original data source.
– Select an empty range with the same number of rows as the columns of the old array and vice versa.
– Type the TRANSPOSE formula and press “Ctrl+Shift+Enter.”
The transposed output appears in the selected range.
Key Takeaways
- The TRANSPOSE function helps rotate the values from rows to columns and vice versa.
- The TRANSPOSE function accepts one mandatory argument–“array,” representing the range to be transposed.
- Prior to executing the TRANSPOSE formula, the exact size of the range to be transposed is selected.
- Being an array function, the TRANSPOSE formula is entered by pressing “Ctrl+Shift+Enter.”
- A range can be transposed by the TRANSPOSE function, the INDIRECT and ADDRESS functions, the “paste special” option, and so on.
- The TRANSPOSE function is used in combination with IF, CONCATENATE, INDEX, MATCH, etc., to get the desired output.
Recommended Articles
This has been a guide to the TRANSPOSE function in Excel. Here we discuss how to use TRANSPOSE formula in Excel with example and downloadable templates. You may also look at these useful functions in Excel –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
58.6K subscribers
Leave a Reply