How to use the TRANSPOSE Function (WS)

This Excel tutorial explains how to use the Excel TRANSPOSE function with syntax and examples.

SUMMARY:

The Microsoft Excel TRANSPOSE function returns a transposed range of cells. The TRANSPOSE function converts a vertical range to a horizontal range, or a horizontal range to a vertical range. You must enter the TRANSPOSE function as an array formula.

Sometimes you need to switch or rotate cells. You can do this by copying, pasting, and using the Transpose option. But doing that creates duplicated data. If you don't want that, you can type a formula instead using the TRANSPOSE function.

For example,

• A horizontal range of cells is returned if a vertical range is entered as a parameter. Or a vertical range of cells is returned if a horizontal range of cells is entered as a parameter.

PURPOSE:

To Flip the orientation of a range of cells or To returns a transposed range of cells.

RETURN VALUE:

An range of cells(array) in a new orientation.

REMARKS:

• The TRANSPOSE function returns a vertical range of cells as a horizontal range, or vice versa. The TRANSPOSE function must be entered as an array formula in a range that has the same number of rows and columns, respectively, as the source range has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array or range on a worksheet.
• You don't have to type the range by hand. After typing =TRANSPOSE( you can use your mouse to select the range. Just click and drag from the beginning of the range to the end. But remember: press CTRL+SHIFT+ENTER when you are done, not ENTER by itself.
• Need text and cell formatting to be transposed as well? Try copying, pasting, and using the Transpose option. But keep in mind that this creates duplicates. So if your original cells change, the copies will not get updated.

TRANSPOSE Function example:

Diffrent way to use the TRANSPOSE function...

Step 1: Data table or range
The data table or range for which we want to apply the TRANSPOSE function...

Step 2: 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 8 cells here that are arranged vertically:

Step 3: Type =TRANSPOSE(
With those blank cells still selected, type: =TRANSPOSE(
Excel will look similar to this, Also Notice that the eight cells are still selected even though we have started typing a formula.

Step 4: Type the range of the original cells.
Now type the range of the cells you want to transpose. In this example, we want to transpose cells from B2 to C5. So the formula for this example would be: =TRANSPOSE(B2:C5) -- but don't press ENTER yet! Just stop typing, and go to the next step.
Excel will look similar to this:

Step 5: 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 2 (you did, didn't you?), the formula will get applied to more than one cell. Here's the result after pressing CTRL+SHIFT+ENTER:
Excel will look similar to this:

Another Ways to Transpose Excel Data Using Copy/Paste (static)

It easier to use the Paste Special transpose option to do the same
See the below steps to explore how to apply the TRANSPOSE using the shortcuts key:
Step 1: Select the data (B2:D8) and press CTRL-C or the Copy button.
Step 2: Select the cell where you want to paste the copied data and then press CTRL + ALT + V
Step 3: A paste Special window opens, you need to Check the box labeled Transpose and click OK.

Step 4: Output: The Transpose data table after clicking OK.

---

---

Download Example Sample Excel File

transpose-function-example.xlsx

Useful Links: Link 1… Link 2… Link 3… Link 4…