Exceljet

Quick, clean, and to the point

Flip table rows to columns

Excel formula: Flip table rows to columns
Generic formula 
{=TRANSPOSE(range)}
Summary 

To flip a table in Excel from rows to columns (i.e. to change orientation from vertical to horizontal) you can use the TRANSPOSE function.

In the example shown the formula in E5:K6 is:

{=TRANSPOSE(B5:C11)}

Note: this is a multi-cell array formula and must be entered with Control + Shift + Enter.

Explanation 

The TRANSPOSE function is fully automatic and can transpose cells vertical to horizontal, and vice versa. The only requirement is that there be a one to one relationship between source and target cells.

In the example shown, we are transposing a table that is 2 columns by 7 rows (14 cells), to a table that is 7 columns by 2 rows (14 cells).

Note that this function creates a dynamic link between the source and target. Any change in to data in the source table will be reflected in the target table.

One-off conversion with Paste Special

If you simply need to do a one-time conversion, and don't need dynamic links, you can use Paste Special. Select the source data, copy, then use Paste Special > Transpose.

Author 
Dave Bruns
Related formulas 
Excel formula: Transpose table without zeros
Transpose table without zeros
The TRANSPOSE function automatically transposes values in a horizontal orientation to vertical orientation and vice versa. However, if a source cell is blank (empty) TRANSPOSE will output a zero. To fix that problem, this formula contains an IF...
Related functions 
Excel TRANSPOSE function
Excel TRANSPOSE Function

The Excel TRANSPOSE function "flips" the orientation of a given range or array: TRANSPOSE flips a vertical range to a horizontal range, and flips a horizontal range to a vertical range.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.