I recently taught a course and, while this wasn't actually covered in the course content, we did discuss it. I've found that every time I demonstrate this technique, it gets some "oohs" and "aahs", so I figured that I'd share it here.
The question I was posed is "I have a spreadsheet where the data is set up across rows, and I'd like it down the columns. Is there an easy way to do that?"
It is a fairly common request, in my experience. One specific example I remember helping a friend with was a list of product and serial numbers that they pulled from a system. I'll pretend that it looked a little like this:
In this case he wanted to change it to a column of item descriptions and serial numbers. It was a lot more columns of course, and was floored when I sent it back to him within a minute. (It took longer to open the file and respond to the email than to fix it.) Here's how you do it:
- Select the data in A1:E2
- Right click and choose "Copy"
- Click in another cell (I chose A5)
- Right click and choose Paste Special
In the dialog box shown below, ensure you check "Transpose"
- Click OK and you're done
The completed data looks as shown below:
And yes, if you want to go from columns to rows, it works too. Same steps exactly.