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:= TRANSPOSE (array)
The TRANSPOSE function syntax has the following arguments:
The array argument is a range of cells. The transposition of an array is created by using the first row of the array as the first column of the new array, the second row of the array as the second column of the new array, and so on.
To input an array formula in Excel, we need to:
- Highlight the range of cells for the function result;
- Type the function in the first cell of the range, and press CTRL-SHIFT-Enter.
Example: Let’s look at some Excel TRANSPOSE function examples and explore how to use the TRANSPOSE function as a worksheet function in Microsoft Excel:
Step 1: Select blank cells
First select some blank cells. But make sure to select the same number of cells as the original set of cells, but in the other direction. For example, there are 17 cells here that are arranged vertically. So, we need to select 17 horizontal cells, like this:
This is where the new, transposed cells will end up.
Step 2: Type =TRANSPOSE(
With those blank cells still selected, type: =TRANSPOSE(
Excel will look similar to this:
Notice that the 17 cells are still selected even though we have started typing a formula.
Step 3: Type the range of the original cells.
The formula for this example would be: =TRANSPOSE(A1:D17) — but don’t press ENTER yet! Just stop typing, and go to the next step.
Step 4: Finally, press CTRL+SHIFT+ENTER
Now press CTRL+SHIFT+ENTER. Why? Because the TRANSPOSE function is only used in array formulas, and that’s how you finish an array formula. An array formula, in short, is a formula that gets applied to more than one cell. Because you selected more than one cell in step 1, the formula will get applied to more than one cell. Here’s the result after pressing CTRL+SHIFT+ENTER:
Syntax: {=TRANSPOSE(A1:D17)}
Result:
Note:
The TRANSPOSE function converts a vertical range of cells to a horizontal range of cells, or a horizontal range of cells to a vertical range of cells. Use it to “flip” a range of cells from one orientation to another.
When array is transposed, the first row of array is used as the first column of the new array, the second row of array is used as the second column of the new array, the third row of array is used as the third column of the new array, and so on.
You must enter the TRANSPOSE function as an array formula that contains same number of cells as array, using Control + Shift + Enter.
The new array must occupy the same number of rows as the source array has columns, and the same number of columns as the source array has rows.
For a one-off conversion, you can use paste special > transpose.
