Converting Rows to Columns (and vice versa) in Excel

Blog

Twice in the last month I’ve needed to take a single column of data (listed in rows) and make them a single row of multiple columns.  It turns out there is an easy way to do this, but I keep forgetting it.  I keep looking for the option on the Ribbon, but it’s not there.  The feature is buried as part of the “Paste Special” functionality.

For example, let’s say I have some data that looks like this:

C#
VB.NET
F#
Iron Python
Iron Ruby

But I really need it to look like this:
C# VB.NET F# Iron Pyton Iron Ruby

So I don’t forget them again (or I can at least find them quickly) here are the steps:

  1. Copy all the data you are looking to make into a column or row. 

  2. Before you copy to your destination location, right click in the start location where you want the data to begin.

  3. Choose “Paste Special”

  4. Check the Transpose checkbox and click OK.

image

This has come in extremely handy for large amounts of data I needed to take from a SQL Query result and turn it into a set of columns in Excel for analysis.