How to Convert Rows into Columns in Microsoft Excel

Microsoft Excel offers hundreds a features for manipulating data.
Microsoft Excel offers hundreds a features for manipulating data. | Source

The Quick Answer:

  1. Highlight Row -> Copy to Clipboard
  2. Highlight Destination Column
  3. Right Click -> Paste Special -> Transpose

The Long Answer:

When using Microsoft Excel, it's not uncommon that you've created, or someone has given you, that doesn't comply with your needs. Perhaps you want to import the data into a relational database management system, like Microsoft Access or SQL Server, or you need to put the data into Chart within Excel. When trying to use the data feels like a square peg into a round holes, knowing how to manipulate the data can save you significant time, and more importantly frustration and effort.

In case you're new, the first thing to understand is that data is stored in Columns (Up and down, Vertical) and Rows (Left and Right, Horizontal), and the data can either be organized in as a list or maybe a chart or a crosstab. A list would be straight-forward, like a table, where there are headers on the top row, one header per column, and then the data fills in the subsequent rows.

A chart/crosstab contains data in columns and rows, but the intersection of the column and row contains the meaningful data. For example, both the X & Y-axis might have city names, and where the two intersect is the distance from the X to the Y city. Another example would be Sales Commissions per salesperson and state for each. Pivot tables can be used in some situations, but these are based on the data that already exists, whereas the crosstab is just raw data.

Ok, but the reason you're here is that you have a row that needs to be a column, or a column that needs to be a row. Either way, Excel can handle this. Assuming you need the former, you can highlight the cells of the data in the row (don't just click on the row selector) and then copy it to the clipboard. (Ctrl+C) Next, highlight a column where you want the end to go, and then right-click on it. The context menu will open, and just under the regular "Paste" options, you'll see that the "Paste Special" option is enabled. When you select it, you'll get a menu of icons that requires an Egyptian hieroglyphics expert to translate. Run your mouse over the different icons until you find the one that says "Transpose" or you can press the letter 'T' to trigger it.

This will cause the data to flow from the row form into a column form, and you'll look like a genius. Feel free to explore the other 'Paste Special' features, and see what they have to offer, as you'll never know you needed some of them until you know that they exist.

More by this Author


Comments 2 comments

Kassi 23 months ago

I was so confused about what to buy, but this makes it undrnstaedable.


Eldora 23 months ago

I'll try to put this to good use imiaemetdly.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working