Thursday, July 29, 2010

How do you transpose a column into a row with a formula in Excel?

It's easy to copy and paste data to turn vertical columns into horizontal rows. But sometimes you may want to automate this task. You can use array formulas to transpose lists of data.  Transposing a range means turning a row into a column, and turning a column into a row.  In other words, it rotates the orientation of the data by plus or minus 90 degrees. You can reverse the order or keep it the same.Here's how to do both:


How to transpose a column Into a row with the same order:

These functions will transpose a column list, CList, into a row.  To keep the data in the original order, create a named range called RList referring to the cells in a row that is to contain the transposed data.  Then use the following array formula: 


=OFFSET(CList,COLUMN()-MIN(COLUMN(RList)),0)

How to transpose a column Into a row with the REVERSE order:

To reverse the order, create the named range "RevRList" then enter this formula:

=OFFSET(CList,MAX(COLUMN(RevRList))-COLUMN(),0)