Converting rows to columns in Excel

jent

macrumors 6502a

Original poster

Mar 31, 2010

893

568

• Apr 3, 2012

• #1

I am copying and pasting information about some conferences from a large list of panels. When I find conferences that I am interested in, I copy and paste them into a big Word document, but I figure I can compare them more easily in an Excel spreadsheet.

Each entry is five lines. Here's a sample entry:

Code:

Session ID:     1234
Track Code:     ABC
Session Name:   How to Change the World
Day:            Thursday
Time:           12:30 pm - 2:15 pm

What I'd like to do is convert the items in the first column into the top row of an Excel spreadsheet. This way, I can look at one single column for a list of dozens of session names and look down a single column to see what times they take place.

Is there an easy way to do this in Excel when the data I'm getting in the first place is in the above format? Many thanks!

 

GGJstudios

macrumors Westmere

May 16, 2008

44,545

943

• Apr 3, 2012

• #2

jent said:

What I'd like to do is convert the items in the first column into the top row of an Excel spreadsheet.

Click to expand...

1. Select the entire range.
2. Command-C to copy
3. Click on a cell outside the copied range as your destination.
4. Right-click and select "Paste Special"
5. Check the "Transpose" box and click OK.

 

jent

macrumors 6502a

Original poster

Mar 31, 2010

893

568

• Apr 4, 2012

• #3

Thank you, GGJstudios! It worked like a charm.

 

H

hafr

macrumors 68030

Sep 21, 2011

2,743

9

• Apr 4, 2012

• #4

A small addition to the answer: the keyboard shortcut for paste special is ctrl+cmd+V

 

P

PReinie

macrumors newbie

Jan 21, 2012

8

0

Northbrook IL

• Jun 26, 2015

• #5

After using ctrl+cmd+v then use cmd+e to select "Transpose", then enter/return to make it happen.

I'd like to add transpose to the tool bar but I can't find "transpose" in any add to tool bar list. It's not under paste special transpose or under transpose.

I guess I'll have to make a macro for it.

 

M

mummifiedartist

macrumors newbie

Aug 15, 2015

2

0

• Aug 15, 2015

• #6

This is helpful thank you. However, is there any way to swap rows and columns with data in them, please? I don't have a huge dataset but big enough to look for a shortcut. PS I tend not to use keyboard short cuts. I'm actually trying to produce a histogram but it looks as though the data is too complex as it is organised. I've never used the analyse functions on Excel for Mac only in Microsoft Office.

 

Gregg2

macrumors 604

May 22, 2008

7,186

1,178

Milwaukee, WI

• Aug 16, 2015

• #7

mummifiedartist said:

This is helpful thank you. However, is there any way to swap rows and columns with data in them, please?

Click to expand...

The answer above applies when there is data in the cells. See www.excel-easy.com/examples/transpose.html for a simple example.

 

M

mummifiedartist

macrumors newbie

Aug 15, 2015

2

0

• Aug 17, 2015

• #8

Gregg2 said:

The answer above applies when there is data in the cells. See www.excel-easy.com/examples/transpose.html for a simple example.

Click to expand...

Hi Gregg, Thank you. I did actually try it with data and it worked! That was much to my pleasant surprise. It didn't seem to like text, though. I know it will transpose text, is it the combination of text and numerical data it doesn't like? I did try to do quite a large block of data but when I reduced the size it was fine. I didn't know whether that was due to the reduced size or the lack of combined data types.

 

Last edited by a moderator: Aug 17, 2015