Excel TRANSPOSE Function: Flip Rows or Columns of Data

01
of 01

Transpose Data in Excel using Paste Special

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

TRANSPOSE Function Overview

The TRANSPOSE function in Excel is one option for changing the way data is laid out or orientated in a worksheet. The function will flip 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 data as shown in the image above.

In addition to the TRANSPOSE function, Excel also has a transpose option as part of its Paste Special feature that makes it easy to re-orientate data without the use of formulas.

TRANSPOSE Function's 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 )}

Array - (required) 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 as seen in the image above.
  • If a multi column array is transposed, the first column of the array will become the first row of the new array, the second column of the array will become 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 - see the step by step example below for details.

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.

Transposing Rows to Columns Example

The steps below cover how to enter the TRANSPOSE array formula located in cell C1 to G1 in the image above. The same steps were 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 just type the complete function manually, many people find it easier to use the dialog box as it takes care of entering the function's syntax - such as brackets and comma separators between arguments.

Note however, 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 Ctrl, Shift, and Enter keys.

The steps below cover entering the TRANSPOSE function into cells C1 to G1 using the function's dialog box.

Opening the TRANSPOSE 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;
  4. The data in cells A1 to A5 should appear in cells C1 to G1;
  5. 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.
Format
mla apa chicago
Your Citation
French, Ted. "Excel TRANSPOSE Function: Flip Rows or Columns of Data." ThoughtCo, Aug. 23, 2016, thoughtco.com/excel-transpose-function-4029297. French, Ted. (2016, August 23). Excel TRANSPOSE Function: Flip Rows or Columns of Data. Retrieved from https://www.thoughtco.com/excel-transpose-function-4029297 French, Ted. "Excel TRANSPOSE Function: Flip Rows or Columns of Data." ThoughtCo. https://www.thoughtco.com/excel-transpose-function-4029297 (accessed January 17, 2018).