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 changes 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 transposes a single row or column of data, or a multiple row or column array.

Instructions in this article apply to Excel 2019, 2016, 2013, 2010, 2007; Excel for Office 365, Excel Online, Excel for Mac, Excel for iPad, Excel for iPhone, and Excel for Android.

TRANSPOSE Function Syntax and Arguments

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

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.

A screenshot showing the TRANSPOSE function in Excel

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.

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.

Transpose Rows to Columns

This tutorial shows how to enter the TRANSPOSE array formula located in cells C1 to G1 of the example that accompanies this article. Options for entering the function and its arguments include either typing the complete function or creating the function and its arguments using Excel's build-in formulas.

=TRANSPOSE(A1:A5)

Although it is possible to type the complete function manually, it's easier to use the built-in function 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 (turning it into an array formula) must be done manually with the Ctrl, Shift, and Enter keys.

To follow along with this tutorial, open a blank Excel worksheet and enter the data found in cells A1 to A5 from the image below.

A screenshot of data used for the TRANSPOSE function in Excel

Then, follow these steps:

  1. Highlight cells C1 to G1 in the worksheet.

  2. Select the Formulas tab of the ribbon.

  3. Select Lookup & Reference.

  4. Select TRANSPOSE to open the Function Arguments dialog box. In Excel for Mac, the Formula Builder opens.

  5. Place the cursor in the Array text box.

  6. Highlight cells A1 to A5 on the worksheet to enter this range as the Array argument.

    A screenshot showing how to use the TRANSPOSE function in Excel
  7. Select OK. Except in Excel for Mac where you select Done.

  8. Place the cursor after the formula found in the formula bar.

  9. Press and hold the Ctrl and Shift keys.

  10. Press the Enter key.

  11. Release the Ctrl and Shift keys to enter the function as an array formula in all five cells.

    A screenshot showing the result of the TRANSPOSE function in Excel

The data in cells A1 to A5 appears in cells C1 to G1. When you select a cell in the range C1 to G1, the complete function appears in the formula bar above the worksheet.