Use Excel's TRANSPOSE Function to 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 TRANSPOSE function can be used to transpose a single row or column of data or a multiple row or column array.

of 02

TRANSPOSE Function Syntax and Arguments

Screenshot of Excel showing the Formula Builder

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 )}

You will notice that the syntax contains an array; this 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 Formulas

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

Screenshot of Excel showing TRANSPOSE results

This article's example shows 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.

Creating the TRANSPOSE Function

Options for entering the function and its arguments include either typing the complete function or creating the function and its arguments using the Formula Builder.

= TRANSPOSE ( A1 : A5 ) into cells C1 : G1

Although it is possible to type the complete function manually, many people find it easier to use the Formula Builder 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.

If you are using an older version of Excel, the Formula Builder may be replaced by a Function Arguments dialog box; simply insert the data in the same way presented in this article.

  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.
  4. Click on TRANSPOSE in the list.

Finishing the TRANSPOSE Array Formula

  1. Click into the Array line in the Formula Builder.
  2. Highlight cells A1 to A5 on the worksheet to enter this range as the Array argument.
  3. Press the Done button.
  4. Next, Double-click Cell C1.
  5. Place your mouse cursor into the formula bar.
  6. Press and hold down the Ctrl and Shift keys on the keyboard.
  7. Press and release the Enter key on the keyboard to enter the function as an array formula in all five cells.

The data in cells A1 to A5 should now appear in cells C1 to G1. When you click on any of the cells in the range C1 to G1, the complete function appears in the formula bar above the worksheet.