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.
Supported versions
- All Excel versions
TRANSPOSE Excel Function Syntax
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.
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.