Use Excel's TRANSPOSE Function: Flip Rows or Columns

Change the way data is laid out on your worksheet

Industrial Designer's Pen and Handwritten Spreadsheet
Bob Rowan / Getty Images

The TRANSPOSE function in Excel is one option for changing the way data is laid out or orientated in a worksheet. The function flips data located in rows to columns or from columns to rows. The function can be used to transpose a single row or column of data or a multiple row or column array.

of 02

TRANSPOSE Function's Syntax and Arguments

Flipping Data from Columns to Rows with the TRANSPOSE Function in Excel
Flipping Data from Columns to Rows with the TRANSPOSE Function. © Ted French

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the TRANSPOSE function is:

{ = TRANSPOSE ( Array )}

An array is the range of cells to be copied from a row into a column or from a column into a row.

  • The size of the original and new arrays must match. If the original array contains five cells of data in a column, the new array must contain five cells of data in a row.
  • If a multi-column array is transposed, the first column of the array becomes the first row of the new array, the second column of the array becomes the second row of the new array, and so on. The same occurs if a multi-row array is transposed.

CSE Formula

The curly braces { }  surrounding the function indicate that it is an  array formula. An array formula is created by pressing the Ctrl, Shift, and Enter keys on the keyboard at the same time when entering the formula.

An array formula must be used because the TRANSPOSE function needs to be entered into a range of cells at the same time for the data to be flipped successfully.

Because array formulas are created using the Ctrl, Shift, and Enter keys, they are often referred to as CSE formulas.

of 02

Transposing Rows to Columns Example

This example covers how to enter the TRANSPOSE array formula located in cell C1 to G1 of the image that accompanies this article. The same steps are also used to enter the second TRANSPOSE array formula located in cells E7 to G9.

Entering the TRANSPOSE Function

Options for entering the function and its arguments include:

  1. Typing the complete function: = TRANSPOSE ( A1 : A5 ) into cells  C1 : G1
  2. Selecting the function and its arguments using the TRANSPOSE function dialog box

Although it is possible to type the complete function manually, many people find it easier to use the dialog box because it takes care of entering the function's syntax such as brackets and comma separators between arguments.

No matter which method is used to enter the formula, the final step – that of turning it into an array formula – must be done manually with the CtrlShift, and Enter keys.

Opening the TRANSPOSE Dialog Box

To enter the TRANSPOSE function into cells C1 to G1 using the function's dialog box:

  1. Highlight cells C1 to G1 in the worksheet;
  2. Click on the Formulas tab of the ribbon;
  3. Click on the Lookup and Reference icon to open the function drop-down list;
  4. Click on TRANSPOSE in the list to open the function's dialog box.

Entering the Array Argument and Creating the Array Formula

  1. Highlight cells A1 to A5 on the worksheet to enter this range as the Array argument.
  2. Press and hold down the Ctrl and Shift keys on the keyboard.
  3. Press and release the Enter key on the keyboard to enter the TRANSPOSE function as an array formula in all five cells.

The data in cells A1 to A5 should appear in cells C1 to G1.

When you click on any of the cells in the range C1 to G1, the complete function { = TRANSPOSE (A1: A5 )} appears in the formula bar above the worksheet.