Note: this is a multi-cell array formula and must be entered with Control + Shift + Enter.
How this formula works
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.
To dynamically transpose a table that contains blanks, you can use an array formula that uses the TRANSPOSE and IF functions. In the example shown, the multi-cell array formula in I9 is: =TRANSPOSE(IF(B5:F6="","",B5:F6)) Note: this is an array...
The Excel TRANSPOSE function "flips" the orientation of a given range or array. TRANSPOSE converts a vertical range to a horizontal range, or a horizontal range to a vertical range. You must enter the TRANSPOSE function as an array formula.
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.