How do I transpose a column into a row in Excel

Question

I have the following Excel worksheet:

   A
1 foo
2 bar
3 baz
4 bam

(In reality, the column is much, much longer making transponing by hand not an option). How can I transform this sheet into

   A   B   C   D
1 foo bar baz bam

I've tried the PivotTable function but am unable to get the output I want. I could also write a (php) script to read and transform the Excel, but I cannot imagine this is something that is not easily done in Excel. However, I have been unable to find the answer to this problem. Can you help?

Answer 1

There are two easy, non-programmatic ways:

1. Copy the data and at the target, use Paste Special->Transpose
2. Apply the TRANSPOSE worksheet function: select the range of the transposed size (in your example 1 row x 4 columns and enter =TRANSPOSE(A1:A4). You need to enter this as an array formula, i.e. press Ctrl-Shift-Enter

If for some reason you need to do this via code, this VBA code will do:

Sub CopyTransposed(rngSource As Range, rngTargetCell As Range)
    rngTargetCell.Resize(rngSource.Columns.Count, rngSource.Rows.Count).Value = _
        Application.WorksheetFunction.Transpose(rngSource)
End Sub

You can simply call it like this:

CopyTransposed [A1:A4], [C1] 

or to be more explicit

CopyTransposed Sheets("SourceSheet").Range("A1:A4"),Sheets("TargetSheet").Range("C1")