Function: TRANSPOSE

by | May 9, 2019 | Excel Formulas

transpose excel

The TRANSPOSE Excel function is a Lookup & Reference formula that rotates a given range or array. Since the output of this formula is an array, you need to press the Ctrl + Shift + Enter (CSE) key instead of just pressing the Enter key when entering this function. In this guide, we’re going to show you how to use TRANSPOSE Excel function and also go over some tips and error handling methods. 

Note: In September 2018, Microsoft has introduced the concept of dynamic arrays and “spill” behavior to make it easier to deal with arrays. With these new features, Excel can no populate cells dynamically without the need for the CSE button combination. Please note that these features are currently only available to select users. When it’s ready, the feature is planned for release for Office 365 users.

Supported versions

  • All Excel versions

TRANSPOSE Excel Function Syntax

TRANSPOSE(array)

Arguments

array

An array or range of cells that you want to rotate.


Example

The TRANSPOSE function requires a single argument. All you need to do is enter the reference of the range or an array.

=TRANSPOSE(MyTable)

Unlike regular functions, you also need to select all cells you want to populate, before entering the formula. Select a range that can fit the transposed version of your array. For example, if your array has 7 rows and 11 columns; you need to select 11 rows and 7 columns to place the values.

After selecting a suitable range, enter the formula and apply the CSE key combination (press Enter while holding Ctrl and Shift. Excel automatically wraps your formula with curly braces ({}) to show that this is an array function.

Download Workbook

Summary and Tips

  • A video demonstrating how to use this function can be found here: How to transpose the rows and columns of a data table
  • Make sure that your range selection before entering function can fit the output data.
  • Press the CSE key combination instead of just pressing the Enter key.
  • Alternatively, you can use Copy and Paste Special > Transpose However, this is one-time rotation. The TRANSPOSE function will update the result area automatically when there are changes in your workbook, just like any function.

Issues

  • If the return range is larger than the source range/array to be rotated, you will get #N/A errors in the extra cells.