Excel TRANSPOSE Function

Excel TRANSPOSE formula allows you to rotate (swap) values from rows to columns and vice versa in Excel. Its goal, as a component of the Excel lookup and reference functions, is to arrange data in the appropriate manner. To run the formula, pick the precise size of the transposed range and hit the CSE key (“Control+Shift+Enter“).

Description


The TRANSPOSE function in Microsoft Excel returns a transposed range of cells. For instance, if a vertical range is specified as a parameter, a horizontal range of cells is returned. Alternatively, if a horizontal range of cells is specified as a parameter, a vertical range of cells is returned.

Excel TRANSPOSE formula is an Excel built-in function that is classified as a Lookup/Reference Function. It may be used in Excel as a worksheet function (WS). The TRANSPOSE function is a worksheet function that may be used as part of a formula in a worksheet cell.

Syntax


The Excel TRANSPOSE Function in Microsoft Excel has the following syntax:

=TRANSLATE (range)

Arguments or Parameters:

range – The range of cells to transpose.

Returns


TRANSPOSE returns a range of cells that have been transposed.

Note:

The TRANSPOSE function requires an array as the range value. Enter a value and then press Ctrl+Shift+Enter to create an array. This will enclose the formula in brackets, indicating that it is an array.

Applicable To


 Office 365

Notes on use:

  • The TRANSPOSE function transforms a vertical range of cells to a horizontal range of cells, or vice versa. TRANSPOSE, in other terms, “flips” the orientation of a specified range or array:
  • TRANSPOSE translates a vertical range to a horizontal range when provided one.
  • TRANSPOSE transforms a horizontal range to a vertical range when provided one.
  • When an array is transposed, the first row becomes the new array’s first column, the second row becomes the new array’s second column, the third row becomes the new array’s third column, and so on.
  • TRANSPOSE is a function that works with both ranges and arrays. Ranges that have been transposed are dynamic. If the data in the source range changes, TRANSPOSE updates the data in the target range instantly.

Excel TRANSPOSE Function


The TRANSPOSE Excel function, as the name implies, may be used to transpose data in Excel.

The syntax is as follows:

=TRANSPOSE(array)

The array denotes a range of cells to be transposed in this case.

Take the following basic steps:

Step1: Choose the range of cells in which you wish the output data to be transposed.

Step2: TRANSPOSE is an array formula; hence, the precise amount of cells must be specified.

Step3: If the range of your table is 6×2, i.e., 6 rows and 2 columns, you must pick a 2×6 range for the transposed data, i.e., 2rows and 6 columns.

excel transpose function1

Step4: Insert the formula =TRANSPOSE (A1:F5)

excel transpose function1

Step5: Avoid pressing Enter! Ctrl+Shift+Enter is required to perform array formula.

Step6: It’s worth noting that transpose is an array formula. An array formula must be input by hitting the Ctrl+Shift+Enter key combination, which causes the formula bar to insert a set of braces around the formula.

Step7: This instructs Excel to print an array of data rather than a single cell.

Step8: Transposition of the data is now complete.

excel transpose function1

Related Posts

Extract or Filter Horizontal Data

You might have been through this kind of situation where you need to filter out the horizontal data from the list having few columns. I am also pretty sure about it that you might have chosen to do it manually, ...

Filter And Transpose Data From Horizontal To Vertical

This post will show you how to use Filter function and in combination with Transpose function to filter data from horizontal and transpose data as vertical in Microsoft Excel. You can refer to the below general formula based on TRANSPOSE ...

How to Count Row That Contain Specific Value in Excel

This post will guide you how to count rows that contain a specific text or number in Excel 2013/2016 or Excel office 365. How do I count the number of rows that contain a particular value while working with your ...

How to Transpose Rows and Columns in Excel

This post will guide you how to convert row data to columns in Excel. How do I convert columns to rows with Paste feature in Excel 2013/2016. Convert Rows to Columns Using Paste Special Convert Rows to Columns Using Formula ...

Excel PV Function

This post will guide you how to use Excel PV function with syntax and examples in Microsoft excel. Description The Excel PV function returns the present value of a loan or investment based on constant payments and a constant interest ...

Sidebar