This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Quickly Turn a Range on Its Side
Problem; I have a column that contains 20 department names going down a column. I need to build a worksheet with those names going across row 1.
Figure 86 Turn this data sideways.
Strategy: Copy the data. Select a new blank cell. Do a Paste Transpose. Here’s how:
1. Highlight the department names in column A.
2. Ctrl+C to copy the cells to the Clipboard.
3. Move the cell pointer to a blank area of the worksheet. Your paste region can not start in the same cell as your copy region.
4. Open the Paste dropdown. In Excel 2007, choose Transpose. In Excel 2010, click the Transpose icon.
Figure 87 Use this icon to turn the pasted data sideways.
Gotcha: The columns you paste to will not automatically resize to fit the data. To fix this problem, you can select the appropriate range (in this case, C1:Z1) and then choose Home, Format, AutoFit Column Width.
Additional Details: You can use Transpose to convert a horizontal row of numbers into a column. In addition, you can use it to turn a rectangular range on its side.